Hello,
I try to manage the output of the sql file (that generate a csv file), in case of error within the sql script.
the following script is OK, everything is fine :
whenever sqlerror exit sql.sqlcode
whenever oserror exit 1
set encoding UTF-8
set echo off
set feedback off
set term off
set timing off
set head on
set pause off
set pagesize 0
set sqlformat delimited ; " "
set LINESIZE 9999
set trimspool on
alter session set nls_numeric_characters = ', ';
alter session set nls_date_format = "DD/MM/YYYY HH24:MI:SS";
Spool .\test.csv
select sysdate from dual;
but if I have an error in SQL by using for example:
select AAAAA from dual;
the csv file will be blank. While I want to be warned of the error
Please note that the same kind of script executed on SQLplus was sending the error message in the CSV file, while with SQLCL not ?
Workaround:
If I change set feedback off --> set feedback on, the error message from SQL appears in the csv file, which is fine for me.
BUT, if now I put back the valid SQL instruction :
whenever sqlerror exit sql.sqlcode
whenever oserror exit 1
set encoding UTF-8
set echo off
set feedback on
set term off
set timing off
set head on
set pause off
set pagesize 0
set sqlformat delimited ; " "
set LINESIZE 9999
set trimspool on
alter session set nls_numeric_characters = ', ';
alter session set nls_date_format = "DD/MM/YYYY HH24:MI:SS";
Spool .\test.csv
select sysdate from dual;
the CSV file contains the result + unwanted lines
"SYSDATE"
17/10/2017 09:26:25
1 ligne sélectionnée.
1 select
2* sysdate from dual
Final question : How could I set my script to have in the csv file
* only data if OK
"SYSDATE"
17/10/2017 09:26:25
* error message if error
Thank you for your help