Skip to Main Content
  • Questions

  • ERROR: ORA-01489: result of string concatenation is too long

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Shane .

Asked: December 21, 2000 - 8:43 am UTC

Last updated: June 16, 2004 - 1:25 pm UTC

Version: 3.3.2.0.2, Oracle DB 8.1.5

Viewed 10K+ times! This question is

You Asked

We have written Stored Procedures that extract data from our database and inserts the data into about seven tables.

I then use a SQL*plus script that joins the tables and concatenates the columns together into a fixed length file.
I spool this file out to the network for another system to work with. It can only handle a single fixed length file.

The record length was at 3856 chars and it worked. Now with new additional requirements the record length is now 4658 chars and I get the above error. I know that VARCHAR2 can only be a max of 4000 chars but do not know why this should affect the string if I am spooling out the record. I can not split the file either.

Would you know of another way to get the data to a spooled fixed length file? Could I change some server setting to deal with larger output? Could I move it to a larger data type and then spool it out? Any ideas would be appreciated.

Thanking you in advance
Shane Lakes

Shortened Example of script:

Set arraysize 1
Set linesize 4000
Set pagesize 50000
Set embedded on
Set termout off
Set heading off
Set feedback off
Set verfiy off

Spool C:\VAL.txt
Select
rpad (NVL(TO_CHAR(A.SEQNUM), ' '),12, ' ') ||
rpad (NVL(TO_CHAR(A.CASE_MBR_KEY_OUT), ' '),8, ' ') ||
rpad (NVL(TO_CHAR(A.CASE_KEY_OUT), ' '),8, ' ') ||
rpad (NVL(A.CONT_NO_OUT, ' '),10, ' ') ||
rpad (NVL(A.BRANCH_CD_OUT, ' '),6, ' ') ||
rpad (NVL(A.COUNS_CD_OUT, ' '),6, ' ') ||
.
. Lines left out
.
.rpad (NVL(G.EXP_DESC9, ' '),50, ' ') ||
rpad (NVL(G.EXP_SCL9, ' '),4, ' ') ||
rpad (NVL(TO_CHAR(G.EXP_CHRG_AMT9), ' '),16, ' ') ||
rpad (NVL(TO_CHAR(G.EXP_CHRG_XPCT9), ' '),6, ' ') ||
rpad (NVL(G.EXP_DESC10, ' '),50, ' ') ||
rpad (NVL(G.EXP_SCL10, ' '),4, ' ') ||
rpad (NVL(TO_CHAR(G.EXP_CHRG_AMT10), ' '),16, ' ') ||
rpad (NVL(TO_CHAR(G.EXP_CHRG_XPCT10), ' '),6, ' ') ||
rpad (NVL(TO_CHAR(G.TIMESTAMP, 'YYYYMMDD HH24:MI:SS'), ' '),26, ' ')
from
VAL_SCHEME_DTLS A,
VAL_MBR_DTLS B,
VAL_MBR_BEN_DTLS C,
VAL_MBR_UNIT_DTLS D,
VAL_PREM_DTLS E,
VAL_MBR_LOADINGS F,
VAL_MBR_EXPENSES G
where
A.SEQNUM = B.SEQNUM AND
B.SEQNUM = C.SEQNUM AND
C.SEQNUM = D.SEQNUM AND
D.SEQNUM = E.SEQNUM AND
E.SEQNUM = F.SEQNUM AND
F.SEQNUM = G.SEQNUM
/
Spool off



and Tom said...

the max length of a varchar2 is 4000 characters in 8.x (2000 in 7.x). A string concatenation is a varchar2 -- hence, the max you can concat is 4000 bytes.

One solution for you in 8i would be to use a temporary table and a clob. Here is an example:

drop table clob_table;

create global temporary table clob_table
( id int primary key, x clob ) on commit delete rows
/

declare
l_cnt number default 1;
l_clob clob;

procedure append( p_string in varchar2 )
as
begin
dbms_lob.writeappend(l_clob,length(p_string),p_string);
end;
begin
for x in ( select * from all_objects where rownum < 10 )
loop
insert into clob_table values ( l_cnt, empty_clob() )
returning x into l_clob;

-- Here i am just generating data, you would have
-- your rpads and nvl's of the columns here...
for i in 1 .. trunc(5000/length(x.object_name))+1
loop
append( x.object_name );
end loop;
l_cnt := l_cnt+1;
end loop;
end;
/

set long 10000
select x from clob_table order by id;
commit;
-- data disappears after the commit.


Another solution would be to use UTL_FILE if the file can be on the server. Use the entry point in UTL_FILE that lets fopen specify the maximum record width and set it to 32k or so. Otherwise the line length with utl_file will default to about 1022 bytes.



Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

result of string concatenation is too long

MOHAN, April 27, 2004 - 11:23 am UTC

Hi Tom,

I am currently facing the same issue. As it has been indicated make it as 32k. Where to do it and which one has to be done..

My Oracle database version is 9i. Is there any solution in 9i since i am currently facing this issue..

the following is my code..

PROCEDURE build_customer_file (p_error_text OUT VARCHAR2)
IS
TYPE cdi_cust_rectyp IS REF CURSOR;

c_cdi_cust_recs_cur cdi_cust_rectyp;

TYPE cdi_cust_temp_typ IS TABLE OF VARCHAR2 (32766) INDEX BY BINARY_INTEGER;

c_cdi_cust_temp_rec cdi_cust_temp_typ;

ROWS NATURAL := 5;
l_location VARCHAR2(200) := 'IN BUILD_CUSTOMER_FILE';
l_method VARCHAR2(255) := 'BUILD_CUSTOMER_FILE';
l_file_location VARCHAR2(500) := '/var/spool/erp/DMDW/logfiles';
l_file_name VARCHAR2(100) := 'CU'|| TO_CHAR (SYSDATE, 'MMDDYY');
l_file_open_mode VARCHAR2(1) := 'W';
l_cust_buffer_rec VARCHAR2(1000);
l_file_id UTL_FILE.file_type;

BEGIN

l_location := 'IN OPEN_CUSTOMER_FILE';

mdw_cdi_interface.open_file (l_file_location,
l_file_name,
l_file_open_mode,
l_file_id
);

l_location := 'IN WRITE_CUSTOMER_FILE';


OPEN c_cdi_cust_recs_cur
FOR ('SELECT LPAD (NVL (TO_CHAR(account_id), '' ''), 15, '' '')
||LPAD (NVL (TO_CHAR (household_id), '' ''), 15, '' '')
||RPAD (NVL (customer_number, '' ''), 30, '' '')
||RPAD (NVL (customer_name, '' ''), 130, '' '')
||RPAD (NVL (street_line_one, '' ''), 240, '' '')
||RPAD (NVL (street_line_two, '' ''), 240, '' '')
||RPAD (NVL (city, '' ''), 60, '' '')
||RPAD (NVL (state, '' ''), 2, '' '')
||RPAD (SUBSTR (REPLACE (REPLACE (NVL (postal_code, '' ''), '' '',''''), ''-'',''''),1,9),15, '' '')
||RPAD (NVL (primary_phone, '' ''), 35, '' '')
||RPAD (NVL (do_not_rent_flag, '' ''), 1, '' '')
||RPAD (NVL (mail_preference_code, '' ''), 5, '' '')
||RPAD (NVL (category_code, '' ''), 30, '' '')
||RPAD (NVL (TO_CHAR (update_date,''YYYYMMDD''), '' ''), 8, '' '')
||RPAD (NVL (customer_status, '' ''), 1, '' '')
||RPAD (NVL (TO_CHAR (acquisition_date,''YYYYMMDD''), '' ''), 8, '' '')
||RPAD (NVL (external_source_key_id,'' ''), 10, '' '')
||RPAD (NVL (TO_CHAR (first_order_date,''YYYYMMDD''), '' ''), 8, '' '')
||RPAD (NVL (TO_CHAR (last_order_date, ''YYYYMMDD''), '' ''), 8, '' '')
||LPAD (NVL (TO_CHAR (total_order_amt), '' ''), 9, '' '')
||LPAD (NVL (TO_CHAR (total_refund_amt), '' ''), 9, '' '')
||LPAD (NVL (TO_CHAR (total_order_qty), '' ''), 8, '' '')
||RPAD (NVL (TO_CHAR (last_marketing_date,''YYYYMMDD''),'' ''), 8, '' '')
||RPAD (NVL (agc_club_member_flag, '' ''), 1, '' '')
||RPAD (NVL (country_code, '' ''), 60, '' '')
||RPAD (NVL (province, '' ''), 60, '' '')
||RPAD (NVL (in_care_of_name, '' ''), 15, '' '')
||RPAD (NVL (organization_code, '' ''), 10, '' '') cust_record
FROM pc_mdw_cdi_customer_temp'
);

LOOP
FETCH c_cdi_cust_recs_cur
BULK COLLECT INTO c_cdi_cust_temp_rec LIMIT ROWS;

FOR i IN c_cdi_cust_temp_rec.FIRST .. c_cdi_cust_temp_rec.LAST

LOOP
BEGIN
mdw_cdi_interface.write_file (l_file_id, c_cdi_cust_temp_rec (i));
EXCEPTION
WHEN OTHERS
THEN
null;
END;
END LOOP;

EXIT WHEN c_cdi_cust_recs_cur%NOTFOUND;

END LOOP;

CLOSE c_cdi_cust_recs_cur;

l_location := 'IN CLOSE_CUST_FILE';
mdw_cdi_interface.close_file (l_file_id);

EXCEPTION
WHEN OTHERS
THEN
p_error_text := l_method || l_location || SQLCODE || SQLERRM;
END build_customer_file;





Tom Kyte
April 28, 2004 - 1:13 pm UTC

if the result of your concat in sql is > 32k, just don't do it there.

instead of

for x in ( select c1 || c2 || ... || cn data from ... )
loop
write( x.data )

do this

for x in ( select c1, c2,...., cn from .... )
loop
write ( x.c1 || x.c2 || .... || x.cn );


plsql can do 32k.
sql can do 4000 bytes only.

please explain

A reader, June 15, 2004 - 4:08 pm UTC

can you explain let us say I put 10 procedures in this clobs
1 create or replace package p1...
2 create or replace package body p1...
3 create or replace package p3...
4 create or replace package p4...
5 create or replace package p5...
..
in this clob_table
how can i execute it dynamically using this process...
please explain

Tom Kyte
June 16, 2004 - 11:12 am UTC

using what process? we are talking about string concatenation here, not dynamically executing clobs that contain create or replace statements.

VARCHAR2 size

sandeep, June 16, 2004 - 11:53 am UTC

hi sir
u told that we cant do more than 32K in PLSQL
what if we have to open SLQ String which is more that 32 K in "open for .... loop"
what should we do


Tom Kyte
June 16, 2004 - 1:25 pm UTC

you won't be using open for.

you would have to use dbms_sql and the parse interface that allows for an array to be passed in.

but i would ask "why the heck am I building a query that exceeds 32k, what did i do to get myself into such a situation"

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

CLOB/BLOB/etc

Complete documentation on Securefiles and Large Objects here