Sample Header Ad - 728x90

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