variablessqlplusspool

Syntax SQL Variable for spool path

I fail to use a variable as a path for the spool. Currently, I have tried many variations but none actually worked:

variable some_var VARCHAR2(100)

exec :some_var := some_pack.get_name()

PRINT some_var 
-- this prints the value of some_var as expected

spool some_var 
-- this will spool to "some_var.LST" which was not intended

-- this did not work either
-- spool :some_var 
-- spool &some_var 

begin
    -- I put some lines here for the spool
end;
/
Solution

  • So finally I found that this would work:

    column sd new_value sd
select some_pack.get_name() sd from dual ;
SPOOL &&sd

    The "very similar looking" version did NOT work:

    variable some_var VARCHAR2(100)
exec :some_var := some_pack.get_name()
SPOOL &&some_var

    I leave the question open to anyone who is able to explain the different behaviors.

    Best, Peter