How can I write the result of executing an SQL script to the log?

Question

How can I write the result of executing an SQL script to the log?

I use it to run on a remote machine .bat, which calls SQL * Plus and executes the script. In the log, only the result of the execution is written, in the form of a message that so many lines are involved, or if an error occurred, then the error code.

Text.bat:

SET NLS_LANG=AMERICAN_AMERICA.CL8MSWIN1251

call sqlplus sys/pass@tnsalias as sysdba @restartQueuesSystem.sql >restartQueuesSystem.log

pause

exit

Where:

restartQueuesSystem.sql this is a script file
restartQueuesSystem.log file containing the execution log that SQL*Plus itself writes

How can I set up a log entry in SQL * Plus or upgrade the script so that the execution result itself is written in the log?

For example:
In restartQueuesSystem.sql it is written select * from table;.
Then in restartQueuesSystem.log I want to see not only the execution notification,
but also the result of executing the request select * from table;.

8

sql oracle11g oracle логирование sqlplus

Author: MaxU, 2020-01-13

Source

1 answers

score 14 · Accepted Answer

Use SQL*Plus User's Guide and Reference.
Many of the commands described in Chapter 12 SQL * Plus Command Reference are used to control the output when executing SQL statements in a script.

Working example to start with

Example.bat:

SET NLS_LANG=AMERICAN_AMERICA.CL8MSWIN1251

sqlplus -s -l user/pass@dbserver:1521/service @example.sql > example.log

pause

Example.sql:

whenever sqlerror exit failure rollback
set feedback on timing on

prompt
prompt ## creating table ...
create table items as
    select rownum id, 'item '||rownum item
    from xmlTable ('1 to 3')

run

prompt
prompt ## updating table ...
update items set item=item||'**' 
where id=2

run

prompt
prompt ## querying table ...
select *
from items

run

prompt
prompt ## droping table ...
drop table items

run
exit

When running from the CMD terminal:

> example.bat

The following log file will be created, containing the commands and the results of their execution:

## creating table ...
  1  create table items as
  2  select rownum id, 'item '||rownum item
  3*     from xmlTable ('1 to 3')

Table created.

Elapsed: 00:00:00.33

## updating table ...
  1  update items set item=item||'**'
  2* where id=2

1 row updated.

Elapsed: 00:00:00.02

## querying table ...
  1  select *
  2* from items

    ID ITEM
---------- ---------------------------------------------
     1 item 1
     2 item 2**
     3 item 3

3 rows selected.

Elapsed: 00:00:00.01

## droping table ...
  1* drop table items

Table dropped.

Elapsed: 00:00:00.27