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;
.
1 answers
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