How to give the result of a query in a subtitution variable

this SQL plus code is working

define a=22; 
host powershell.exe echo &a;

22

But if the value from a comes from a query, it doesn't work anymore.

define a=2; 
select 22 into a;
host powershell.exe echo &a;

2 instead of 22

I've tried that with a buffer but to no avails

variable buffer varchar2;
select 22 into :buffer from dual;
define a=b;
host powershell.exe echo &a;

:b

Solution

You can use the column .. new_value ... syntax. If you still want a bind variable too, as in your previous question, then define and populate that variable however you are now, e.g.:

SQL> variable buffer varchar2(2);
SQL> exec :buffer := '22';

PL/SQL procedure successfully completed.

SQL> print buffer

BUFFER
--------------------------------
22

Then define the column with a new value you can refer to later as a substitution variable:

SQL> column a new_value a
SQL> select :buffer as a from dual;

A
--------------------------------
22

SQL> host powershell.exe echo &a
22

SQL>

You can set termout off and back on around that extra query if you're running this as a script and don't want to see the output.

And you can query anything, it doesn't have to be a bind variable:

select 22 as a from dual;

SQL> select sysdate as a from dual;

A
---------
02-JUN-22

SQL> host powershell.exe echo &a
02-JUN-22

SQL>