Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Ora-01489: result of string concatenation is too long

Raj RammohanMay 28 2009 — edited Apr 26 2011
Hello Gurus,

i have a typical problem

i am constructing a query in FORM and writing SQLPLUS script into a .SQL file. file will contain final data like below..

set linesize 90
set pagesize 0
set echo off
set verify off
set termout off
set feedback off
set trimspool on
set escape '^'
spool D:\10GAPPServerappln\xxx\TEMPREP\ADA39057.sql;
set linesize 229
select ' IQIS# ,Cust Complaint Short Txt ,CD Short Txt ' from dual;
set linesize 129
select a||','||b||','||c||d from table;
/
spool off;
exit;

After this By using HOST command i will execute the above .sql script and will write the output to text file.

But problem is when i have clob column in any one of concatenated columns in query (a or b or c) then i am getting the error "Ora-01489: result of string concatenation is too long".

pls suggest me how to overcome this problem..
This post has been answered by Solomon Yakobson on May 28 2009
Jump to Answer

Comments

Solomon Yakobson
VARCHAR2 in SQL is limited to 4000 bytes. Your concatenation result exceeds it. Change:
select a||','||b||','||c||d from table;
to
select TO_CLOB(a)||','||b||','||c||d from table;
Also, get rid of line
/
Since
select a||','||b||','||c||d from table;
already has semi-colon at the end, SQL*Plus will execute it. Next line
/
wil execute it again.

SY.
1 person found this helpful
Solomon Yakobson
Also add
set long 1000
set longchunk 1000
set linesize 1000
to your script (I assume concatenation results will not exceed 1000 bytes).

SY.
unknown-698157
Actually that isn't the problem.
The problem is you are using an extreemly inefficient technique to set up an extreemly unscalable application.
Calling HOST to call sqlplus again? WHY ON EARTH?
Just
@<filename>
will suffice!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

Obviously the || operator is concatenating strings, your CLOB is implicitly converted to a VARCHAR2, which has a 4000 bytes limit.
You need to learn dbms_sql, and do away with this sqlplus drama.
Or you must be urgently looking for a new job.

--------------------------
Sybrand Bakker
Senior Oracle DBA

Experts: Those who did read documentation.
Solomon Yakobson
sybrand_b wrote:

Obviously the || operator is concatenating strings, your CLOB is implicitly converted to a VARCHAR2, which has a 4000 bytes limit.
???

From non-experts who did read documentation:

CLOB || VARCHAR2 = CLOB:
SQL> CREATE OR REPLACE
  2  VIEW V1
  3  AS SELECT TO_CLOB('A') || 'A' clob_concat_varchar2 FROM dual
  4  /

View created.

SQL> DESC V1
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 CLOB_CONCAT_VARCHAR2                               CLOB

SQL>
SY.
Solomon Yakobson
Answer
The actual issue here is operator sequence.
select a||','||b||','||c||d from table;
If c is a CLOB and a and b are VARCHAR2 you will get Ora-01489 if a||','||b||',' exceeds 4000 bytes. I assume you could get same Ora-01489 if "a" is a clob since optimizer might decide to concat ','||b||','||c||d first and result might exceed 4000. So for a bullet-proff solution you would need to wrap all non-clob columns with TO_CLOB, not just "a" as I suggested.

SY.
Marked as Answer by Raj Rammohan · Sep 27 2020
Raj Rammohan
Hello,

Thanks for your suggession. it worked
852835
thanx a lot Solomon Yakobson,

you are my savior today, you are wise like the king.
1 - 7
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on May 24 2011
Added on May 28 2009
7 comments
29,066 views