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
-
All are English characters(Eg: SQL query result record contains around 64 K characters)
-
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;