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

Question

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.

please, DBMS_OUTPUT.PUT_LINE(INPUT_ID); and DBMS_OUTPUT.PUT_LINE(query_str); — Sebas
– Sebas, Commented Jun 28, 2012 at 23:40
Sebas, I put those output lines after " where QUERY_ID=IN_QUERY_ID" and now it is saying that I'm not properly ending the statement after the output lines. It seems to always find the error before "execute immediate". — Jeanne Lane
– Jeanne Lane, Commented Jun 29, 2012 at 15:24
please, comment the execute immediate so you can get the buffer output — Sebas
– Sebas, Commented Jun 29, 2012 at 16:18
Where are you running this? It seems to be fine in SQL*Plus and SQL Developer; it looks like whatever client you're using doesn't understand the execute command, which is strange. — Alex Poole
– Alex Poole, Commented Jun 29, 2012 at 17:00
OK I thought you were getting the error compiling the function, clearly not. Which means it's erroring on the dynamic SQL you're executing. — Alex Poole
– Alex Poole, Commented Jun 29, 2012 at 19:22

Jeffrey Kemp · Accepted Answer · 2012-06-29 00:45:33Z

3

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

answered Jun 29, 2012 at 0:45

Jeffrey Kemp

60.5k15 gold badges111 silver badges161 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jeanne Lane Over a year ago

Sorry...I changed the name of the variables in my program for this post for clarity . I did declare result_id. Do you know what else could be the problem?

Alex Poole · Accepted Answer · 2012-06-29 19:37:57Z

2

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

edited Jun 29, 2012 at 19:37

answered Jun 29, 2012 at 19:27

Alex Poole

192k11 gold badges198 silver badges349 bronze badges

1 Comment

Jeanne Lane Over a year ago

Yes! This helped! Plus changing :INPUT_ID to :strINPUT_ID.

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related