CODEWITHSUNDEEP
sqloracle-databaseplsql
Jeanne Lane
Jeanne Lane

Reputation: 505

How to pass inputs to a dynamic query in Oracle SQL?

I am trying to create a function that dynamically runs a query based on inputs. The first input for the function, input_id, is the argument for the dynamic query. The second input, IN_QUERY_ID, specifies which query to use. 

create or replace  
FUNCTION getResultID(  
INPUT_ID NUMBER,  
IN_QUERY_ID NUMBER  
)  
RETURN VARCHAR2  
AS  
 RESULT_ID VARCHAR2(256);  
 query_str VARCHAR2(256);  
 BEGIN  
  select CONSTRUCTOR INTO query_str from query_str_ref     
   where QUERY_ID=IN_QUERY_ID;  
  EXECUTE IMMEDIATE query_str INTO RESULT_ID  USING INPUT_ID;  
   RETURN Result_ID;  
END getResultID;

I'm getting an error that I'm not properly ending the statement after "RESULT_ID=IN_QUERY_ID;" I'm wondering if I'm missing some other step.

Upvotes: 1

Views: 4668

Answers (2)

Alex Poole
Alex Poole

Reputation: 191580

The good news is that it's not your function that's wrong. According to the dbms_output that @sebas encouraged you to produce, the string you're trying to execute dynamically is:

select FIRST_NAME||LAST_NAME||to_char(BIRTH_DATE,'yyyy/mm/dd') as HOST_ID FROM INPUT_DATA_TABLE WHERE INPUT_ID=NEW:INPUT_ID;

There are two thing wrong with that. The NEW:INPUT_ID is causing the ORA-00933, because the NEW looks spurious; if you remove that it will recognise the :INPUT_ID as a bind variable. (NEW looks like it's come from a trigger but is probably a coincidence). And you should not have a trailing ; on the string, execute doesn't need it and it will break with an invalid character error.

So it should work if the query_str_ref entry is changed to:

select FIRST_NAME||LAST_NAME||to_char(BIRTH_DATE,'yyyy/mm/dd') as HOST_ID FROM INPUT_DATA_TABLE WHERE INPUT_ID=:INPUT_ID

Upvotes: 2

Jeffrey Kemp
Jeffrey Kemp

Reputation: 60312

You haven't declared Result_ID as a variable in the function.

Upvotes: 3

Related Questions