Skip to Main Content
  • Questions
  • get os command output to come out in spool files

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Richard.

Asked: January 07, 2016 - 10:26 pm UTC

Last updated: January 08, 2016 - 2:39 am UTC

Version: 12.1.0.2.0

Viewed 10K+ times! This question is

You Asked

Consider the following sqlplus script called test_script.sql

spool spool_file
select * from dual;
!perl -le 'print time'
!echo abc
spool off

This is a very simple example of what I'm trying to do -- namely I want the results of echo, perl and other os commands to be displayed in a spool file.

if I run this script interactively all the output is shown on the monitor

sql> @test_script

D
-
X

Elapsed: 00:00:00.00
1452204953

abc

sql>



However the results of echo and perl do not appear in spool_file.lst

cat spool_file.lst


D
-
X

Elapsed: 00:00:00.00


Is there any way to get all the output into the spool file?

Thanks,

Richard



and Connor said...

You can do a standard redirect, eg

spool demo.lst
select * from dual;
host my_command >> demo.lst"

but... spool is a *buffered* write facility, ie, we buffer things and flush them out from time to time...so you might have some ordering issues.

Similarly on windows you might have some dramas, eg

SQL> spool c:\temp\demo.lst
SQL> select * from dual;

D
-
X

1 row selected.

SQL> host cmd /c "echo hello2 >> c:\temp\demo.lst"
The process cannot access the file because it is being used by another process.

SQL> spool off


As a consequence, you might be better served moving the entire "spooling" concept to the OS itself, eg (from ksh in this instance)

print "
select * from dual
host my_command" | sqlplus 1>demo.lst 2>&1

Hope this helps.

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