SQL query output to a file is failing with error code 9 when the data contains French and English characters with size > 32k
0
votes
1
answer
335
views
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, 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" = 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
)
Asked by Raj
(1 rep)
Apr 6, 2020, 05:13 PM
Last activity: Jul 26, 2025, 10:03 PM
Last activity: Jul 26, 2025, 10:03 PM