Syntax SQL Variable for spool path

57 Views Asked by At

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;
/
1

There are 1 best solutions below

0
Peter Frey On BEST ANSWER

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