Forum Stats

  • 3,824,908 Users
  • 2,260,438 Discussions
  • 7,896,343 Comments

Discussions

Directory for creating spool files specified with no explicit path (or with a relative path)

user9540031
user9540031 Member Posts: 188 Silver Badge
edited Jun 12, 2022 11:49AM in SQLcl

Hi,

[Tested on: SQLcl 22.1 / Windows 7 & 10]

There's a common, decade-old pattern that has been used again and again in SQL*Plus scripts, which is named script-generating-script, and works as follows:

  1. Open a spool file: SPOOL temp.sql (note the .sql extension)
  2. Run a SQL query which returns the text of other SQL commands, writing the results into the spool file
  3. Close the spool file: SPOOL OFF
  4. Call the resulting script: @temp : this executes the SQL commands generated at step 2.
  5. [Optionally] Remove the temporary script, e.g.: HOST del temp.sql (note: this requires calling an OS-specific command)

This trick makes it possible to do in pure SQL—plus a couple of SQL*Plus commands—things that would otherwise require lengthy and wordy PL/SQL with dynamic SQL.

Of course, the same can be done in SQLcl... with a twist: as this post will show, SQLcl differs subtly from SQL*Plus as regards the location of spool files when the full path of the spool file is not specified in the SPOOL command, or when a relative path is being used.

1/ SQL*Plus

If a relative path (or no path) is used in the SPOOL command, SQL*Plus always evaluates the path relative to its current working directory, which it inherited from its parent OS process (the process in which the sqlplus command was called).

There is no CD command in SQL*Plus which would enable to switch the current working directory from within SQL*Plus.

Additionally, if a relative path is used, the @ / START commands in SQL*Plus also evaluate the path of the called file relative to the current working directory.

Therefore, the script-generating-script pattern (as described above) always works, even if no path is explicitly specified, because in that case everything implicitly takes place in the current working directory of the SQL*Plus process.

2/ SQLcl

Things are subtly different in SQLcl, as shown below:

E:\Home\user9540031\Temp> sqlcl /nolog

SQLcl: Release 22.1 Production on Sun Jun 12 11:08:29 2022
Copyright (c) 1982, 2022, Oracle.  All rights reserved.

SQL> show version
Oracle SQLDeveloper Command-Line (SQLcl) version: 22.1.1.0 build: 22.1.1.131.0820

SQL> pwd
E:\Home\user9540031\Temp\

SQL> host echo %SQLPATH%
E:\Home\user9540031\oracle\sqlcl

SQL> show sqlpath
SQLPATH : E:\Home\user9540031\Temp\;.;E:\Home\user9540031\oracle\sqlcl

SQL> spool tmpspool.sql
SQL> prompt prompt Hello World
prompt Hello World
SQL> spool off

SQL> @tmpspool

Error starting at line : 1 in command -
@tmpspool
Error report -
SP2-0310: Unable to open file: "tmpspool.sql"

Above, the current working directory is E:\Home\user9540031\Temp. SQLcl prepends that directory to the SQLPATH, hence the search for called SQL files will begin in that directory. The PWD command returns that directory as SQLcl's notion of its current working directory.

(Contrary to SQL*Plus, SQLcl has a CD command...)

Meanwhile, the search for the previously-created spool file fails! Obviously, this breaks the script-generating-script pattern if using the current working directory implicitly.

And now the big question is: where did my spool file go?

Fortunately, there are great monitoring/troubleshooting tools around, and with help from Process Monitor, I could quickly find out that the spool file had not been created where I expected it:

SQL> @%USERPROFILE%\.sqldeveloper\tmpspool.sql
Hello World

That's right: the spool file was created relative to the %USERPROFILE%\.sqldeveloper directory. And because that directory is not in the SQLPATH, subsequently the @ / START command could not locate the file.

Workarounds:

  • Either: (a) Specify absolute paths, both in the SPOOL command and in subsequent @ / START commands.
  • Or: (b) Use the CD command to explicitly change SQLcl's notion of its current working directory:
SQL> cd C:\Windows\TEMP

SQL> pwd
C:\Windows\Temp

SQL> show sqlpath
SQLPATH : C:\Windows\Temp;E:\Home\user9540031\Temp\;.;E:\Home\user9540031\oracle\sqlcl

SQL> spool tmpspool2.sql
SQL> prompt prompt Hello again!
prompt Hello again!
SQL> spool off

SQL> @tmpspool2
Hello again!

Remark: in fact, the CD command in SQLcl does not change the current working directory of the OS process in which SQLcl—actually, the Java virtual machine—is running. You may verify that by checking the properties of that process using tools such as Process Explorer. Instead, SQLcl has its own logical notion of the current working directory, which it is to use when creating spool files or looking for SQL files when the @ / START commands are being used: the target of the CD command is prepended to the SQLPATH.

Bottom line: explicitly CD to some directory, and the script-generating-script pattern will work as expected, even if implicitly using the current working directory.

Meanwhile, if you call CD without an argument, in order to go back where you started, the pattern will fail again, right as before:

SQL> cd

SQL> pwd
E:\Home\user9540031\Temp\

SQL> show sqlpath
SQLPATH : E:\Home\user9540031\Temp\;.;E:\Home\user9540031\oracle\sqlcl

SQL> spool tmpspool3.sql
SQL> prompt prompt That'll be all for now.
prompt That'll be all for now.
SQL> spool off

SQL> @tmpspool3.sql

Error starting at line : 1 in command -
@tmpspool3.sql
Error report -
SP2-0310: Unable to open file: "tmpspool3.sql"

SQL> @%USERPROFILE%\.sqldeveloper\tmpspool3
That'll be all for now.

3/ SQL Developer

[Tested on: SQL Developer 21.4.3 / Windows 7]

SQL Developer's behaviour is apparently similar to that of SQLcl, though it uses a different directory for creating spool files initially: SQL Developer uses the %APPDATA%\SQL Developer directory, whereas SQLcl uses %USERPROFILE%\.sqldeveloper.

Conclusion: if you read this post up to this point, you know that SQLcl / SQL Developer handle their notion of the current working directory differently from SQL*Plus. Determining whether they do so in a 100%-consistent way, or whether the behaviour described above is an entirely desirable feature is left as an exercise to the reader. Meanwhile, it is possible that former SQL*Plus scripts using the scrpt-generating-script pattern fail (out of the box) if they use the current working directory implicitly, and therefore these scripts may require minor changes, namely: (a) use explicit paths, or (b) use an explicit CD command.

Or: workaround (c): just add an initial CD path_to_some_dir command to your login.sql script—assuming that login.sql script is in your SQLPATH, of course.

Hope this helps!

Regards,

Comments