0

I apologize for possible duplicate, but I just cannot seem to get this working. I have an .sql file where I am trying to pass a parameter like "C:\Path\To" to adjust the location of spooling output, since I would like to run it from a Windows scheduler script. The print statement shows the correct path, let alone with a header like this: 

ARGPATH
----------------------------------------------------------------------------    ----
C:\Path\To\output.txt

However, I cannot seem to get the spooling to work with this type of dynamic file location parameter. There are no errors when running the script beside the lack of output. I am using SQL Developer's CLI SQLcl ver 4.2, 12.1.0.2.0. Unfortunately I am stuck with this particular tool and have very little control over the development Environment in general. Instructions on how to just spool into the local folder (where the sql script resides) instead of some default location are most welcome as well. Thanks in advance!

set heading off
var argpath varchar2(100)
exec :argpath := Trim('&1'||'output.txt')
spool argpath;

print argpath;
select count (*) .../select statement/
spool off;
exit

Maybe I should add that when I declare the path explicitly, i.e. 

spool C:\Path\To\output.txt

everything works just as expected.

katolsster
  • 3
  • 4

1 Answers1

0

You're overthinking it. Use substitution on its own and dont use binds the way you were trying to.

spool '&argpath\output.txt'
select 1 from dual;
spool off
Barry McGillin
  • 465
  • 5
  • 11