Skip to Main Content

SQLcl

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Sqlcl spooled file output issue ?

User_EYH5LOct 17 2017 — edited Oct 18 2017

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

Comments

Post Details

Added on Oct 17 2017
2 comments
1,628 views