Search code examples
pythonoraclecsvoracle-sqldevelopersqlplus

Oracle - 'ORA-01489 - Result of string concatenation is too long' Error When Trying to spool data via SQLPLUS

I have used the below spool command to get data via CMD using sqlplus

set pagesize 0
set term off
set feed off
set colsep ,


spool 'C:\Provident_Workspace\output.csv'

SELECT EO.SLELABEL||','||dbms_lob.substr(EL.LOGTEXT,4000,1)||',' ||utl_raw.cast_to_varchar2(dbms_lob.substr(SR.SODATA,4000,1))
FROM SORECORD SR, SOAPPKEY SK, EPTORDER EO, EPTLOG EL 
WHERE SK.APPKEYNAME = 'MSISDN' 
AND SK.appkeyvalue = '07996703863' 
AND SK.SOID = SR.SOID 
AND SR.SOTYPE = 'NAC' 
AND trunc(SR.Receipttimestamp) = date '2020-01-07' AND SR.SOID = EO.SOID 
AND EO.EPTNUMBER = EL.EPTNUMBER AND EL.SOID LIKE TO_CHAR((Select SUBSTR(TO_CHAR(SR.SOID), 1, LENGTH(SR.SOID)-1) FROM SORECORD SR, SOAPPKEY SK WHERE SK.APPKEYNAME = 'MSISDN' AND SK.appkeyvalue = '07996703863' AND SK.SOID = SR.SOID AND SR.SOTYPE = 'NAC' AND trunc(SR.Receipttimestamp) = date '2020-01-07')) || '%' AND EL.SOID > TO_NUMBER((Select SUBSTR(TO_CHAR(SR.SOID),1, LENGTH(SR.SOID)-1) FROM SORECORD SR, SOAPPKEY SK WHERE SK.APPKEYNAME = 'MSISDN' AND SK.appkeyvalue = '07996703863' AND SK.SOID = SR.SOID AND SR.SOTYPE = 'NAC' AND trunc(SR.Receipttimestamp) = date '2020-01-07') || '0');

spool off

when running this command on SQLPLUS via cmd I am getting ORA-01489 Error. Now I know why this is happening, EL.LOGTEXT is CLOB Data Type and SR.SODATA is BLOB Data Type. Both contain large amount of multiline data inside them and probably their concatenation is getting larger than 4000 bytes.

Is there any way I can make this query work without any loss of data anywhere?

I need these 3 columns in tabular format in a CSV file so that I can easily iterate over them using inbuilt csv module of Python.

Solution

  • As @thatjeffsmith says: Don't use SQL*Plus. Instead use the Python cx_Oracle interface. But if you insist on a CSV datafile, try SQL*Plus's native CSV feature, see Fast Generation of CSV and JSON from Oracle Database.

    From the blog post:

    $ sqlplus -s -m 'csv on' cj@'"localhost/pdb1"' @t.sql
  "DEPARTMENT_ID","DEPARTMENT_NAME"
  10,"Administration"
  20,"Marketing"
  30,"Purchasing"
  40,"Human Resources"
  50,"Shipping"
  60,"IT"
  70,"Public Relations"
  80,"Sales"
  90,"Executive"
  100,"Finance"

    The -s "silent" option may hide the password prompt on some platforms making SQL*Plus appear to hang. Enter the password and all will be well.

    The quoting around the connection string may or may not be needed on your OS or with your version of SQL*Plus.