How to log only error while executing .sql file in sqlplus

0
votes

How can I create a .log file that will only be created when an exception occurs during executing .sql file via sqlplus.

WHENEVER SQLERROR EXIT SQL.SQLCODE

ALTER PRODUCT IDENTIFIED BY &1;
ALTER CUSTOMER IDENTIFIED BY &1;

EXIT;

This script is executed via sqlplus I add parameter in the command line and it is executed when a parameter is incorrect or an ORA error occurs how can I log it into a file where this .sql is located. I do not want to track all execute just create a log file that contains the error ORA

2
oraclesqlplus
By the way, while EXIT SQL.SQLCODE is OK on Windows it is not a good idea on Linux, because its exit codes only go up to 255. - William Robertson

2 Answers

0
votes

To avoid command output, add

SET ECHO OFF

To log messages,

either you may use spool

spool /path/yourlogfile.log

OR

If you're running sqlplus command-line in a Unix environment, you may redirect the outputs. 

sqlplus usr/pwd@db @file.sql >/path/yourlogfile.log

To prevent substitution message, add

SET VERIFY OFF
0
votes

It is possible to create log file in Unix box, but using utl_file. For this code should be written in begin end block. SQlPlus itself does not have capability to create Spool file on conditional basis.

It can be written something like below:-

declare
  filehandle utl_file.file_type;
begin

  execute immediate 'ALTER PRODUCT IDENTIFIED BY &1';
  execute immediate 'ALTER CUSTOMER IDENTIFIED BY &1';

exception

when others 
then

  filehandle := utl_file.fopen('/unixpath/test', 'outputfile.log', 'W');
  utl_file.put_line(filehandle, 'test error '||sqlerrm);
  utl_file.fclose(filehandle);

end;
/