sqloracle-databasesqlplusdynamic-sqlexecute-immediate

How to execute a dynamic sql statement in sql*plus


I would like to modify a value by using a dynamic sql statement. The example is choosen to be reproductible. I know that I don't need a dynamic sql statement for that.

variable a number =1;
print a 

1

exec execute immediate 'select 2 into :a from dual'

PL/SQL procedure successfully completed.

 print a

1

1 is returned instead of 2, which means that my statement wasn't executed.

So, how can it be executed?


Solution

  • INTO should be out of execute immediate:

    SQL> var a number = 1;
    SQL> print a
    
             A
    ----------
             1
    
    SQL> exec execute immediate 'select 2 from dual' into :a;
    
    PL/SQL procedure successfully completed.
    
    SQL> print a
    
             A
    ----------
             2
    
    SQL>