Welcome Guest ( Log In | Register )


 
 
 
 
 
 

 
 
Oracle 

Performance Tuning Reference poster
 
Oracle training in Linux 

commands
 
Oracle training Weblogic Book
 
Easy Oracle Jumpstart
 
Oracle training & performance tuning books
 
Burleson Consulting Remote DB Administration
 
 
 
Reply to this topicStart new topic
> ORA-01489: result of string concatenation is too long
rashmisb
post Jul 25 2012, 03:45 AM
Post #1


Newbie
*

Group: Members
Posts: 3
Joined: 25-July 12
Member No.: 47,449



Hi , you might have received a lot of questions on this error, but I am still not able to resolve this.

I have a sql query which has around 115 columns and out of which 25 columns are of varchar2(2000) and when I run the query I get the
ORA-01489: result of string concatenation is too long error.

I tried to use to_clob function for the columns having varchar2(2000) and if I run the sql from toad , it works fine, but when I tried to run the same query from sqlplus and spool to a file, the result doesn't come in a single line. I have tried to import the spool file to my local and open it , but still it doesn't come in a single line, the data is trucated
This is how my data looks in the spool file.


1-L31OGM|Red|1|Due|Qualified|02/08/2012||02/08/2012| you are missing a message t

These are the below set options used in the query . I even tried set long 100000000 and also set longchucksize option also, I have tried with WRAP OFF and WRAP ON also ,but still it doesn't help.

SET HEADING OFF
SET WRAP OFF
SET LINESIZE 32000
SET FEEDBACK OFF
SET PAGESIZE 0
SET LONG 32000
SET TRIMSPOOL ON
SET ECHO OFF
SET TERMOUT OFF

Can you please help me with a solution to get the data in a single line and using utl_file package is not an option in our project due to security reason.

Thanks in Advance.
Go to the top of the page
 
+Quote Post
burleson
post Jul 25 2012, 06:19 AM
Post #2


Advanced Member
***

Group: Members
Posts: 11,401
Joined: 26-January 04
Member No.: 13



ORA-01489: result of string concatenation is too long


Cause: String concatenation result is more than the maximum size.

Action: Make sure that the result is less than the maximum size.

************************************************
>> 25 columns are of varchar2(2000)

That's just TOO WIDE!

I would run the query 25 times, and make it manageable . . .


*****************************************************
>> Can you please help me with a solution to get the data in a single line

Why is it so importnt to get 50,000+ characters on a single line?

Like I said, I would do separate queries.

You can always store the results in an array and concatenate it together later . . . .

*******************************************************

>> SET HEADING OFF
SET WRAP OFF
SET LINESIZE 32000

Also, don't do it in SQL*Plus. Instead, place it inside a program like Pro*c . . .


--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post
rashmisb
post Jul 25 2012, 07:08 AM
Post #3


Newbie
*

Group: Members
Posts: 3
Joined: 25-July 12
Member No.: 47,449



QUOTE (burleson @ Jul 25 2012, 04:49 PM) *
ORA-01489: result of string concatenation is too long


Cause: String concatenation result is more than the maximum size.

Action: Make sure that the result is less than the maximum size.

************************************************
>> 25 columns are of varchar2(2000)

That's just TOO WIDE!

I would run the query 25 times, and make it manageable . . .


*****************************************************
>> Can you please help me with a solution to get the data in a single line

Why is it so importnt to get 50,000+ characters on a single line?

Like I said, I would do separate queries.

You can always store the results in an array and concatenate it together later . . . .

*******************************************************

>> SET HEADING OFF
SET WRAP OFF
SET LINESIZE 32000

Also, don't do it in SQL*Plus. Instead, place it inside a program like Pro*c . . .

Go to the top of the page
 
+Quote Post
rashmisb
post Jul 25 2012, 10:03 AM
Post #4


Newbie
*

Group: Members
Posts: 3
Joined: 25-July 12
Member No.: 47,449



Thanks a lot Burleson , but can you please give me some examples of running multiple queries and concatenating at the end which I can refer .

The sql which I am referring is being called from a shell script and hence the script would run in sqlplus.
Go to the top of the page
 
+Quote Post
burleson
post Jul 25 2012, 10:51 AM
Post #5


Advanced Member
***

Group: Members
Posts: 11,401
Joined: 26-January 04
Member No.: 13



Hi Rash,

>> The sql which I am referring is being called from a shell script and hence the script would run in sqlplus

First, it's a crappy design to have 25 varchar(2000) columns in a table row! It must fragment all over the tablespace!

One easy way would be to use temporary tables to extract each of the 25 columns, each into a PL/SQL collection.

Once they are in 25 arrays, the PL/SQL can manipulate them at-will . . .

Here is how to populate a PL/SQL collection, with working code examples:

http://www.dba-oracle.com/oracle_news/2004_2_5_rittman.htm

*****************************************

If you are in s UNIX/Linux envuironment, you could invoke sqlplus once to extract each and concatenate it to the spool file:


CODE
sqlplus . . .
spool hugefile1.txt
select col1 from mytab
spool off
exit


sqlplus . . .
spool hugefile2.txt
select col2 from mytab;
spool off
exit

-- then concatenate the flat files together . . .


For a working example, see the code download in the Oracle shell scripting book:

http://www.rampant-books.com/book_0701_shell_scripting.htm


--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post

Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 

Lo-Fi Version Time is now: 23rd August 2014 - 06:23 AM