Executing Dynamic Sql query in Oracle
13,963
The Dynamic String has to be enclosed within 'Single Quotes'
OPEN OUT_CUR FOR
'SELECT * FROM MYTABLE WHERE ID '|| DYN_QUERY;
EXECUTE IMMEDIATE
allows Multi row result, if you use BULK COLLECT
Example:
DECLARE
TYPE myarray IS TABLE OF VARCHAR2(100);
v_array myarray;
BEGIN
EXECUTE IMMEDIATE 'select ''x'' from dual union all select ''y'' from dual'
BULK COLLECT INTO v_array;
--Or you could use the alternative quoting mechanism to avoid doubling quotation marks.
--EXECUTE IMMEDIATE q'[select 'x' from dual union all select 'y' from dual]'
-- BULK COLLECT INTO v_array;
FOR i IN 1..v_array.count
LOOP
DBMS_OUTPUT.PUT_LINE(v_array(i));
END LOOP;
END;
EXECUTE IMMEDIATE
using bind variables;
String := 'SELECT * FROM EMP WHERE name = :name AND age = :age AND :name <> ''Mahesh''';
EXECUTE IMMEDIATE String USING 'Mahi',21,'Mahi';
<OR>
EXECUTE IMMEDIATE String USING proc_variable1,proc_variable2,proc_variable1;
Author by
smilu
Updated on June 07, 2022Comments
-
smilu almost 2 years
I have a
SELECT
query in which i will have a a DynamicWHERE
condition. The thing is when I try to concatenate theWHERE
conditionPARAMETER
with the SQL Query its not allowing me to save thePROCEDURE
.eg:
CREATE PROCEDURE usp_MySearchQuery ( QTYPE IN INT, OUT_CUR OUT SYS_REFCURSOR ) IS DYN_QUERY VARCHAR2; BEGIN IF QTYPE=1 THEN DYN_QUERY :=' BETWEEN 1 AND 2'; ELSE DYN_QUERY :='=10'; END IF; OPEN OUT_CUR FOR SELECT * FROM MYTABLE WHERE TYPE=QTYPE AND ID || DYN_QUERY; END;
This is how my procedure looks like. I tried
EXECUTE IMMEDIETE
but in its documentation itself, its written it wont work for multiple row query.In
MSSQL
we haveEXEC
(not sure) command which canexecute
the text sent to the command. In the same way do we have any commands which can run the dynamic query inOracle
UPDATE: Answer
I tried like this.
OPEN OUT_CUR FOR ' SELECT * FROM MYTABLE WHERE TYPE=:QTYPE AND ID ' || DYN_QUERY USING QTYPE;
and it worked
-
smilu about 10 yearsI have a date conversion also inside it. ie. TO_CHAR(SYSDATE,'DD-MM-YYYY'). Now when i put the single quotes how it will work. I wrote Two single quotes... will it work? ' SELECT ..... TO_CHAR(SYSDATE,''DD-MM-YYYY'')' will it work?
-
smilu about 10 yearsYour answer was correct. But i have one more problem which i thought will be solved when executing the query. In my stored procedure i'm having the Parameter in the where conditon too. So, when im running the query with single quotes its saying that the procedure parameter(QTYPE) inside the select query is Invalid. I thought of putting Single quotes and Joining it as a string but, Im worried about SQLInjection.
-
Maheswaran Ravisankar about 10 yearsyou can also you bind variable !!Like
EXECUTE IMMEDIATE 'select ''x'' from dual where :x = 1' USING v_x
Also applies forOPEN CURSOR
-
Maheswaran Ravisankar about 10 years@smilu You can also try out this link which explains about
DBMS_ASSERT
package -
smilu about 10 yearsI put : and its showing another error all variables are not bound :(
-
Maheswaran Ravisankar about 10 yearsyou use same variable name two times? you have to specify in
USING
too same number of bind variable inputs.. -
smilu about 10 yearsany examples i can refer?
-
Maheswaran Ravisankar about 10 yearsAdded few to my answer!