SQL query output to a file is failing with error code 9 when the data contains French and English characters with size > 32k

oracleoracle-12c

Database: Oracle 12 C

I have a MESSAGE table with BODY VARCHAR2(32000), METADATA VARCHAR2(32000).

Initially I had a problem when both columns have 32k+32k chars of data. I had fixed it by applying the TO_CLOB function and I am able to write the output to the file without truncating the result. (This is working only when

  1. All are English characters(Eg: SQL query result record contains around 64 K characters)

  2. Combination of French & English characters when the SQL query result record < 32K characters)

But when the BODY part is having French & English characters with expected SQL query result record > 32K characters, In this case, my query execution is failing with error code : 9.

Please help me on how to fix this issue without truncating the data.
Below is my Unix script code

export PATH

export LD_LIBRARY_PATH

export ORACLE_ACCESS

#export NLS_LANG=.AL32UTF8

export NLS_LANG=AMERICAN_AMERICA.AL32UTF8

output=$(sqlplus -S "$ORACLE_ACCESS" << EOF
    WHENEVER OSERROR EXIT 9;

    whenever sqlerror exit sql.sqlcode;

    set NEWPAGE none

    set pagesize 0

    set trimspool on

    set trimout on

    SET LINESIZE 32767

    SET LONG 500000000

    SET LONGCHUNKSIZE 100000000

    set echo off

    set heading off

    set feedback off

    set term off

    set recsep off

    set serveroutput on size unlimited

    SPOOL $FILE_NM APPEND

    select '02~^'||TO_CLOB(EVENT_ID)||'~^'||BODY||'~^'||META_DATA||'~^'||'|^#' from MESSAGE where ((CREATE_TS >= TO_DATE('01-Jan-2025','DD-MON-YYYY') and CREATE_TS < TO_DATE('01-Jan-2025','DD-MON-YYYY')+1) ORDER BY ROWID;

    SPOOL OFF

    exit;

EOF

)

Best Answer

I removed this line and its working fine now. It is writing all kind of characters to the file.(English,French,Chinese)

WHENEVER OSERROR EXIT 9;