Forum Stats

  • 3,752,658 Users
  • 2,250,533 Discussions
  • 7,867,907 Comments

Discussions

Sqlcl spooled file output issue ?

User_EYH5L
User_EYH5L Member Posts: 21 Red Ribbon
edited Oct 18, 2017 5:24AM in SQLcl

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

Answers

  • Gary Graham-Oracle
    Gary Graham-Oracle Member Posts: 3,256 Bronze Crown
    edited Oct 17, 2017 5:08PM

    Normally when SQLcl vs SQL*Plus behavior differs, we recommend...

    set classic on

    but in your case, whether spooling or not, that has no effect.

    Bug logged.  Thank you for reporting it!  The error message should be displayed even if  FEEDBACK OFF.

  • User_EYH5L
    User_EYH5L Member Posts: 21 Red Ribbon
    edited Oct 18, 2017 5:24AM

    Great, thank you !

    Please note that sometimes error is already sent to the CSV file. (depending of the kinf of the error I suppose (syntaxt error, technical error, etc...)).