How to execute query in a variable in Oracle?

Question

In T-SQL can execute a query in a variable using following query.

  DECLARE @QUERY VARCHAR(4000)
  SET @QUERY='SELECT SAL_GRD_CODE,SAL_GRD_NAME
  FROM HS_PR_SALARY_GRADE WHERE SAL_GRD_CODE IN ('000001','000002')'

  EXEC(@QUERY)

Is there a way to achieve this in Oracle?

Solution

SET @QUERY='SELECT SAL_GRD_CODE,SAL_GRD_NAME FROM HS_PR_SALARY_GRADE WHERE SAL_GRD_CODE IN ('000001','000002')'

I don't see any values to be dynamic in your query. So, why do you want to use PL/SQL? You could do it in plain SQL with a SELECT statement.

But, if you are really doing it in PL/SQL, then you need to (ab)use EXECUTE IMMEDIATE.

Also, you would want to retrieve the output of the SELECT statement in PL/SQL, for which Oracle expects an INTO clause.

For example,

SQL> var v_empno number;
SQL> exec :v_empno := 7369;

PL/SQL procedure successfully completed.

SQL> SET serveroutput ON
SQL>
SQL> DECLARE
  2    v_Sal   NUMBER;
  3    str     VARCHAR2(200);
  4  BEGIN
  5    str    :='SELECT sal FROM emp WHERE empno =' ||:v_empno;
  6    EXECUTE IMMEDIATE str INTO v_Sal;
  7    dbms_output.put_line('Employee salary is '||v_sal);
  8  END;
  9  /
Employee salary is 800

PL/SQL procedure successfully completed.

SQL>

Using bind variables

You can declare a bind variable in SQL*Plus, and select into it:

SQL> var v_empno number;
SQL> var v_sal number;
SQL> exec :v_empno := 7369;

PL/SQL procedure successfully completed.

SQL> DECLARE
  2    str     VARCHAR2(200);
  3  BEGIN
  4    str    :='SELECT sal FROM emp WHERE empno =' ||:v_empno;
  5    EXECUTE IMMEDIATE str INTO :v_sal;
  6  END;
  7  /

PL/SQL procedure successfully completed.

SQL>
SQL> print v_sal;

     V_SAL
----------
       800

SQL>

Comments

[Ameya Deshpande]

use EXECUTE IMMEDIATE

[Nishantha]

This is not the real senario. Just an example code to explain the issue.

[Lalit Kumar B]

@Nishantha Ok, anyway, you have a working test case so you could do it in the way demonstrated. Good luck!

[Lalit Kumar B]

@Nishantha I would prefer using bind variables, so added another example. Look at the updated answer. Please mark it answered, it would help others too.