Skip to Main Content
  • Questions
  • Oracle scheduled job including SQL statement in spool file


Question and Answer

Connor McDonald

Thanks for the question, Derrick.

Asked: December 14, 2020 - 7:37 pm UTC

Last updated: June 11, 2024 - 5:01 am UTC

Version: Oracle Database 12c Standard Edition Release - 64bit Production

Viewed 1000+ times

You Asked

I have a scheduled job on Oracle which does counts before and after delete statements and spools the results to a log file on the Oracle server. The job is type SQL_SCRIPT. When I execute the job the SQL statements are also included in the spool file even though I have specified SET TERM OFF ECHO OFF in the script.

My question is, how can I prevent the actual SQL statements from being included in the spool file when it is executed through the Oracle Job Scheduler?

I have tried various options and searched hundreds of articles but have not found anything on this particular issue.


and Connor said...

I've replicated this on my own 12c and 19c systems, namely, *most* SQL commands (column formatting, page size, prompt etc etc) all work fine, but termout/echo seem to be ignored.

I'm asking around internally to see if this is a bug or known restriction, but I think currently, the workaround would be to use EXTERNAL_SCRIPT type instead.

Thus something like

my_script := 'conn user/pass@db
set termout off
set echo off
spool ...
select object_id, object_name from all_objects;
spool off';

would end up along the lines of

my_script := '
export PATH=...
export ORACLE_HOME=...
cat <<EOF > /tmp/myscript.sql
set termout off
set echo off
spool ...
select object_id, object_name from all_objects;
spool off

sqlplus -s user/pass@db @/tmp/myscript.sql';

Not ideal I know :-(


  (1 rating)

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


What's the status?!

Alex, March 10, 2023 - 4:29 pm UTC

Hello Connor,

are there any (good) news regarding the use of TERMOUT etc. from within a dbms_scheduler SQL_SCRIPT job?

Kind regards
Connor McDonald
June 11, 2024 - 5:01 am UTC

I tested in the latest version of 23ai...and no progress :-(

I will try to followup

More to Explore


Need more information on Administration? Check out the Administrators guide for the Oracle Database