Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Arie.

Asked: October 19, 2017 - 5:30 am UTC

Last updated: October 20, 2017 - 1:00 pm UTC

Version: 11.2.0.3

Viewed 1000+ times

You Asked

Hi All,
I've been reading a lot on the internet and am searching for a replacement for sqlplusw.exe. The problem is that I've written a lot of scripts and with sqlplusw.exe you could set a path by reading in an sqlfile. The solutions I've seen sofar including sqldeveloper don't offer that solution.

Example: I've stored my scripts in a location: D:\SQLScripts\Program1\test1.sql and D:\SQLScripts\Program2\test2.sql

When using SQLPlusW I could go to File and open the first test1.sql file which also causes SQLPlus to set the correct path. So in that script I have a spool command like this: output/output.txt. The same goes for test2.sql.

I haven't come across any SQL CLI where you can read in the appropriate script which also sets the correct path to the location of the script. That way you can relatively spool output.

Now the Question:
Is there a good alternative for SQLPlusW that has all and I mean mainly the File\Open feature to set the main directory so that you can spool and call other scripts relatively without using the full path for the script. If you move the scripts eventually it's not nice to have to change all spool locations to a new full path again.

Thanks for any help.

Regards,


and Chris said...

Which version of SQL Developer are you using and how exactly are you running your scripts?

Using the latest version (17.3.1.279), when I run scripts calling them with @, the spooled output goes to %APPDATA%\Sql Developer\

But if I open the SQL file then run it in script mode (F5) the results go wherever I opened the file:

C:\Users\csaxon\Documents\Scripts>dir output
 Volume in drive C is System
 Volume Serial Number is 4416-06A6

 Directory of C:\Users\csaxon\Documents\Scripts\output

19/10/2017  15:09    <DIR>          .
19/10/2017  15:09    <DIR>          ..
               0 File(s)              0 bytes
               2 Dir(s)  97,226,600,448 bytes free

C:\Users\csaxon\Documents\Scripts>type spool-test.sql
spool output\out.log
select 'SPOOLED' t from dual;
spool off

-- open spool-test.sql in SQL Developer, hit F5 then

C:\Users\csaxon\Documents\Scripts>dir output
 Volume in drive C is System
 Volume Serial Number is 4416-06A6

 Directory of C:\Users\csaxon\Documents\Scripts\output

19/10/2017  15:10    <DIR>          .
19/10/2017  15:10    <DIR>          ..
19/10/2017  15:10                28 out.log
               1 File(s)             28 bytes
               2 Dir(s)  97,226,989,568 bytes free

C:\Users\csaxon\Documents\Scripts>type output\out.log
T
SPOOLED


This is what you're looking for, right?

Or, if you're using SQLcl, you can use cd in this to change the directory, just like you would at the command prompt. So you can set the directory as needed:

C:\Users\csaxon\Documents\Scripts>type spool-test-cl.sql
cd output
spool clout.log
select 'SPOOLED' t from dual;
spool off

C:\Users\csaxon\Documents\Scripts>sql.exe chris/xxxxx@db

SQLcl: Release 17.3.0 Production on Thu Oct 19 15:07:08 2017

Copyright (c) 1982, 2017, Oracle.  All rights reserved.

Last Successful login time: Thu Oct 19 2017 15:07:11 +01:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options


SQL> @spool-test-cl
T
SPOOLED


SQL> exit

Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

C:\Users\csaxon\Documents\Scripts>dir output
 Volume in drive C is System
 Volume Serial Number is 4416-06A6

 Directory of C:\Users\csaxon\Documents\Scripts\output

19/10/2017  15:12    <DIR>          .
19/10/2017  15:12    <DIR>          ..
19/10/2017  15:12                28 clout.log
19/10/2017  15:10                28 out.log
               2 File(s)             56 bytes
               2 Dir(s)  97,223,872,512 bytes free

C:\Users\csaxon\Documents\Scripts>type output\clout.log
T
SPOOLED

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Seems my problems with SQLDeveloper have vanished!

A reader, October 20, 2017 - 9:02 am UTC

Hi Thanks for you quick answer and I don't remember which version I used, but it seems that my problems with SQLDeverloper have been resolved. I can run my scripts again like I did on SQLPlusW. :)

Regards,
Arie
Chris Saxon
October 20, 2017 - 1:00 pm UTC

:)
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

VLDB

If you are new to partitioning, check out Connor McDonald's introduction series here.

VLDB

Documentation set on VLDB and Partitioning.