- 196.8K All Categories
- 2.2K Data
- 238 Big Data Appliance
- 1.9K Data Science
- 450.2K Databases
- 221.7K General Database Discussions
- 31 Multilingual Engine
- 550 MySQL Community Space
- 478 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3K ORDS, SODA & JSON in the Database
- 544 SQLcl
- 4K SQL Developer Data Modeler
- 187K SQL & PL/SQL
- 21.3K SQL Developer
- 295.8K Development
- 17 Developer Projects
- 138 Programming Languages
- 292.5K Development Tools
- 107 DevOps
- 3.1K QA/Testing
- 646K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 154 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.1K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 18 Java Essentials
- 160 Java 8 Questions
- 86K Java Programming
- 80 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 204 Java User Groups
- 436 LiveLabs
- 38 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 171 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 232 Portuguese
Directory for creating spool files specified with no explicit path (or with a relative path)
[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:
- Open a spool file:
SPOOL temp.sql(note the
- Run a SQL query which returns the text of other SQL commands, writing the results into the spool file
- Close the spool file:
- Call the resulting script:
@temp: this executes the SQL commands generated at step 2.
- [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.
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.
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: 220.127.116.11 build: 18.104.22.168.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.
- 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
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!