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?
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>
EXECUTE IMMEDIATE