aspose file tools*
The moose likes JDBC and the fly likes Concatination 3 columns of VARCHAR2(4000) to return a ResultSet with one column Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Soft Skills this week in the Jobs Discussion forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Concatination 3 columns of VARCHAR2(4000) to return a ResultSet with one column " Watch "Concatination 3 columns of VARCHAR2(4000) to return a ResultSet with one column " New topic
Author

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

prash patil
Ranch Hand

Joined: May 26, 2008
Posts: 40
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

Joined: Oct 06, 2007
Posts: 338
Oracle has a 4000 character limit on Varchar2/Strings... you have to convert your columns to clobs... try this instead:

prash patil
Ranch Hand

Joined: May 26, 2008
Posts: 40
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

Joined: Oct 06, 2007
Posts: 338
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

Joined: Feb 04, 2009
Posts: 11
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

Joined: May 26, 2003
Posts: 31052
    
162

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.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Concatination 3 columns of VARCHAR2(4000) to return a ResultSet with one column