:

ORA-01489 result of string concatenation is too long

The Problem

The result of string concatenation cannot be more than 4000 characters long. If the string concatenation is too long, you will receive the following error message:

ORA-01489: result of string concatenation is too long

The Solution

Example I

If you are using dynamic SQL and the resultant string is greater than 4000 characters in length, you can break it down into chunks; writing the first, now smaller, string to a file and then append the result of the second part of the original query to it:

type txtfile.part > txtfile_out.txt — txtfile.part has hard coded lines in which are always needed at the top of the file

sqlplus -s -L user/pass@sid @sqlfile1.sql >> txtfile_out.txt
sqlplus -s -L user/pass@sid @sqlfile2.sql >> txtfile_out.txt

Example II

If your string was too long, you could break it down into parts by, for example, the letter that the table name starts with:

— This could be sqlfile1.sql

SELECT owner||’.’||table_name||’,’¬†
from dba_tables
where owner = ‘USER1’
and regexp_like(table_name, ‘^[A-I]’)
and table_name not like ‘%AZ’;

— This could be sqlfile2.sql

SELECT WM_CONCAT(owner||’.’||table_name)
from dba_tables
where owner = ‘USER1’
and regexp_like(table_name, ‘^[J-Z]’)
and table_name not like ‘%\_Y’;

What’s the point of this? Well, by using WM_CONCAT in the second and most importantly the last query which writes to the txtfile_out.txt file, you ensure that there will be no comma at the end of the table_name list so you could use it for the list of tables in a datapump export.

Like it, share it...

Category: SQL


Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *