Executing Dynamic Sql query in Oracle

Question

I have a SELECT query in which i will have a a Dynamic WHERE condition. The thing is when I try to concatenate the WHERE condition PARAMETER with the SQL Query its not allowing me to save the PROCEDURE.

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 have EXEC(not sure) command which can execute the text sent to the command. In the same way do we have any commands which can run the dynamic query in Oracle

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

score 5 · Accepted Answer · edited May 30 '19 at 02:31

5

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;

edited May 30 '19 at 02:31

Jon Heller

34,999
6
74
132

answered Mar 06 '14 at 06:34

Maheswaran Ravisankar

17,652
6
47
69

I 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 Mar 06 '14 at 06:48
Your 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. – smilu Mar 06 '14 at 06:58
you can also you bind variable !!Like `EXECUTE IMMEDIATE 'select ''x'' from dual where :x = 1' USING v_x` Also applies for `OPEN CURSOR` – Maheswaran Ravisankar Mar 06 '14 at 07:00
@smilu You can also try out [this link](http://stackoverflow.com/questions/21405616/oracle-sql-injection-block-with-dbms-assert/21406499#21406499) which explains about `DBMS_ASSERT` package – Maheswaran Ravisankar Mar 06 '14 at 07:05
I put : and its showing another error all variables are not bound :( – smilu Mar 06 '14 at 07:07
you use same variable name two times? you have to specify in `USING` too same number of _bind_ variable inputs.. – Maheswaran Ravisankar Mar 06 '14 at 07:08
any examples i can refer? – smilu Mar 06 '14 at 07:12
Added few to my answer! – Maheswaran Ravisankar Mar 06 '14 at 07:16

Executing Dynamic Sql query in Oracle

1 Answers1

Linked