This week's book giveaway is in the Clojure forum.
We're giving away four copies of Clojure in Action and have Amit Rathore and Francis Avila on-line!
See this thread for details.
Win a copy of Clojure in Action this week in the Clojure forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Concatination 3 columns of VARCHAR2(4000) to return a ResultSet with one column

 
prash patil
Ranch Hand
Posts: 40
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Rancher,
I am facing one problem while retrieving 3 columns of type 4000 Bytes in the concatinated form.
i.e.
My Table is

and now i want to selct all these column as single column so that The RESULTSET will contain only ONE

So I wrote one query
And all columns contains max number of char i.e. 4000 so total lenth of the 12000
so my query fails....
with ERROR :


ANY POINTER APPRICIATED
 
Paul Campbell
Ranch Hand
Posts: 338
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Oracle has a 4000 character limit on Varchar2/Strings... you have to convert your columns to clobs... try this instead:

 
prash patil
Ranch Hand
Posts: 40
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks!! Paul,

I already tried that one..
but the concern is that I added these three columns in table to replace the CLOB columns.
As there was performance issue with CLOB column..

So if I use the to_Clob in select will it not create the same performance issue.???
OR rather how oracle treat TO_CLOB and selecting CLOB colunms in terms of performance?

Any pointers on this?

Thanks,
PP
 
Paul Campbell
Ranch Hand
Posts: 338
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
prash patil wrote:Thanks!! Paul,

I already tried that one..
but the concern is that I added these three columns in table to replace the CLOB columns.
As there was performance issue with CLOB column..

So if I use the to_Clob in select will it not create the same performance issue.???
OR rather how oracle treat TO_CLOB and selecting CLOB colunms in terms of performance?

Any pointers on this?

Thanks,
PP


I would fix my CLOB issue and let the database do the heavy lifting...

How were you defining the CLOB column? If you fail to include the LOB Storage clause, you force the system to do a default storage each time a BLOB or CLOB exceeds 4000 bytes. When you this occurs the result is often datafile fragmentation and performance problems. You can also have LOB performance issues if your buffer cache is incorrectly set.
 
Avyaya Avi
Greenhorn
Posts: 11
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
As you have posted this question in the JDBC section, I suppose that you are retrieving this values to be displayed in some JSP or in any Java file as a form of output.

So, wht I have in mind is, while retriving these values from the DB thru the resultset, you can concatenate them while placing them in the bean like:

someBean.setSisRec(rs.getString(COL_NAME1)+" " + rs.getString(COL_NAME2)+" " +rs.getString(COL_NAME3));

This would set the concatenated form of the values from the DB into the bean on which you can perform any more functionality related implementation.

Hope this helps!!
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 33694
316
Eclipse IDE Java VI Editor
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Avyaya Avi wrote:As you have posted this question in the JDBC section, I suppose that you are retrieving this values to be displayed in some JSP or in any Java file as a form of output.

Not necessarily. This forum is for SQL questions too.
 
I agree. Here's the link: http://aspose.com/file-tools
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic