0

I have a sql script in

dir:\some\path\script.sql

This script is executed via SqlDeveloper and spools some data into

dir:\some\path\data\tablename.csv

Now, I have to store the script in a .sql file because if I execute it from SqlDeveloper directly, spool writes the query itself to the file which I do not want.

But I also do not want to hard-code paths because everyone in my team has their GIT repo whereever they want, so the scenario I'd like to achieve is that my sql script uses relative paths from it's location to write the csv files.

Currently the script.sql looks something like this:

SET COLSEP ";"
spool dir:\some\path\data\table1.csv
select /*csv*/ * from table1;
spool off;

and I want to look it something like this

SET COLSEP ";"
spool current_dir\data\table1.csv
select /*csv*/ * from table1;
spool off;

So that when using the script, the path has to be typed only once, when calling the script.

Jdv
  • 962
  • 10
  • 34
  • Are you looking for a way to define dynamic spool location? If yes then-http://stackoverflow.com/questions/1246360/oracle-how-to-use-spool-with-dynamic-spool-location – atokpas Mar 02 '17 at 11:21
  • @JSapkota not really, as far as I understand in the question you linked part of the desired path is stored in a table. What I need is to get the location of the sql script from within the script. – Jdv Mar 02 '17 at 11:51

1 Answers1

0

More of a workaround than a solution to the problem, but for now I just created a scriptRunner.sql file in the same directory as the script.sql which simply runs the script (@script.sql).

When opening this file, it seems like SqlDevelopers working directory is set to the directory where both .sql files are in and this property is extended to the called script, therefore I can simply use

spool data\table1.csv

Jdv
  • 962
  • 10
  • 34