Dynamic Select Query

Question

I want to run dynamic SELECT sql in oracle.

In Sql server it is simple

 declare @a int,@b int,@c int;
 set @a=1;
 set @b=2;
 set @c=3;
 drop table empl
 create table empl (id int,name nvarchar(100))
 insert into empl values (1,'name1'),(2,'name2'),(3,'name3'),(4,'name4'),(5,'name4')
 select * from empl
 where id=@a or id= @b or id=@c

Result:

1 name1
2 name2
3 name3

But when I try do this in oracle with EXECUTE IMMEDIATE i have issues that it is doesn't support select statement without into.

How I can Achieve this statement in Oracle ?

• Darwin von Corax: I freely confess my ignorance of TSQL, but what is the SELECT in your example meant to accomplish? It looks as though it's simply selecting the variables into themselves, if it does anything at all.
• Vitaly Ascheulov: I want to use variables in select statement. And run it.

Answer 1

In your example using SQL Server syntax, all you are doing is projecting three values, 10, 20 and 30. I don't see anything dynamic in there.

SQL> SELECT 10 a, 20 b, 30 c FROM dual;
 A B C
---------- ---------- ----------
 10 20 30

In PL/SQL, you just need to declare the variables and assign the value.

SQL> set serveroutput on
SQL> DECLARE
 2 a NUMBER;
 3 b NUMBER;
 4 c NUMBER;
 5 var_a NUMBER;
 6 var_b NUMBER;
 7 var_c NUMBER;
 8 BEGIN
 9 a :=10;
 10 b := 20;
 11 c := 30;
 12 SELECT A,b,c INTO var_a, var_b, var_c FROM DUAL;
 13 DBMS_OUTPUT.PUT_LINE('Values are '||var_a||' '||var_b||' '||var_c);
 14 END;
 15 /
Values are 10 20 30
PL/SQL procedure successfully completed.

---

UPDATE As per OP's updated question:

You don't need an explicit cursor, just use a cursor for loop.

For example, using the standard EMP table in SCOTT schema:

SQL> set serveroutput on
SQL> DECLARE
 2 a NUMBER;
 3 b NUMBER;
 4 c NUMBER;
 5 BEGIN
 6 a := 10;
 7 b := 20;
 8 c := 30;
 9 FOR i IN (SELECT empno FROM emp WHERE deptno IN (a, b, c))
 10 LOOP
 11 DBMS_OUTPUT.PUT_LINE('Employee number is '||i.empno);
 12 END LOOP;
 13 END;
 14 /
Employee number is 7369
Employee number is 7499
Employee number is 7521
Employee number is 7566
Employee number is 7654
Employee number is 7698
Employee number is 7782
Employee number is 7788
Employee number is 7839
Employee number is 7844
Employee number is 7876
Employee number is 7900
Employee number is 7902
Employee number is 7934
PL/SQL procedure successfully completed.

• Vitaly Ascheulov: My real query is more complex. And I want to declare variables in the begin of query. And do not change values in whole query
• Darwin von Corax: Then we'll need a better idea of what you want to accomplish. Your sample code doesn't really do anything.
• Lalit Kumar B: @VitalyAscheulov Added an example of PL/SQL. But, all that I did is assignthe values to the variables, and then SELECT INTO other variables. if there is something different that you want, then please elaborate.
• Vitaly Ascheulov: It is ok when if it is 1 value. I corrected my tsql code. Is there solution will be cursor now ?
• Lalit Kumar B: @VitalyAscheulov Yes, it is simple using substitution variables. Try SELECT &a a, &b b, &c c FROM dual; and put enter the values when prompted.