How to execute a dynamic sql statement in sql*plus

Question

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?

Littlefoot · Accepted Answer · 2022-06-01 10:28:26Z

3

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>

answered Jun 1, 2022 at 10:28

Littlefoot

144k15 gold badges41 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to execute a dynamic sql statement in sql*plus

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related