Forum Stats

  • 3,851,995 Users
  • 2,264,055 Discussions
  • 7,904,925 Comments

Discussions

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

user9540031
user9540031 Member Posts: 228 Gold Badge
edited Sep 7, 2022 6:52PM 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.

[EDIT 2022-09-07: Cause: configuration mistake: please see the 2nd comment below. The solution is obviously to fix the configuration, but you may still read on if you please.]

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

  • user9540031
    user9540031 Member Posts: 228 Gold Badge

    Remarks:

    • The PWD command is not found as help topic by the online HELP command, neither in SQL Developer nor in SQLcl
    • In SQL Developer, the PWD command exists, but it does not seem to generate any output.
  • user9540031
    user9540031 Member Posts: 228 Gold Badge

    [...] the spool file was created relative to the %USERPROFILE%\.sqldeveloper directory.

    And I have just realized the reason for this: in the script that I use to start SQLcl, I had explicitly defined the java.io.tmpdir Java system property... to the same directory where I usually run that script from.

    Namely:

    • I had java.io.tmpdir set to: E:\Home\user9540031\Temp
    • And: my current working directory when starting SQLcl was exactly that directory.

    (Remark: no need to use a custom starting script here: it is possible to reproduce with the supplied sql.exe, by setting JAVA_TOOL_OPTIONS accordingly.)

    So the solution is straightforward: create a dedicated directory, e.g. E:\Home\user9540031\.java-temp, for Java temporary files, set java.io.tmpdir to that directory, then leave it alone—i.e. don't start SQLcl with that directory as the current OS working directory, there's no reason to do any kind of work from that place.

    That configuration issue being fixed, the script-generating-script pattern works as expected in SQLcl.

    _________

    As regards the same pattern in SQL Developer... Apparently, when a new SQL worksheet is opened SQL Developer prepends the directory referenced by java.io.tmpdir to the SQLPATH.

    Demo: I added the following lines to my sqldeveloper.conf file:

    #Directory for Java temporary files
    AddVMOption -Djava.io.tmpdir=E:\Home\user9540031\.java-temp
    

    After restarting SQL Developer, and opening a new SQL worksheet, without saving that worksheet to disk—that would have affected the SQLPATH in it—running the show sqlpath command in that worksheet made the point very clear:

    SQLPATH : E:\Home\user9540031\.java-temp;E:\Home\user9540031\SQL_Developer\scripts;[...]
    

    (The other directories come from my configuration—menu Preferences... --> Database::Worksheet node, Select default path to look for scripts.)

    You may even call it a feature: as there's no natural notion of a default working directory in SQL Developer, until the cd command is used to define one, why would SQL Developer behave as though there was one? That said, I don't like spool files ending up in %APPDATA%\SQL Developer as a result, there are too many important files and sub-directories there.

    Solution for SQL Developer: choose a default starting directory, e.g. E:\Home\user9540031\.sqldev-temp, and add an explicit cd E:\Home\user9540031\.sqldev-temp command in SQL Developer's main login.sql file (*) so that this directory is used as the default working directory when a new SQL worksheet is opened—unless the cd command has already been used before in the session—remember the critical point that when a login.sql file is being used, SQL worksheets in the same session share plenty of settings, and SQLPATH is one of these. With that configuration in place, the script-generating-script pattern works as expected in SQL Developer too.

    (*) SQL Developer's login.sql file is configured in Preferences... --> Database node, Filename for connection startup script.

    Regards,