• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Ron McLeod
  • Paul Clapham
  • Bear Bibeault
  • Junilu Lacar
Sheriffs:
  • Jeanne Boyarsky
  • Tim Cooke
  • Henry Wong
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • salvin francis
  • Frits Walraven
Bartenders:
  • Scott Selikoff
  • Piet Souris
  • Carey Brown

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

 
Ranch Hand
Posts: 40
  • 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
 
Ranch Hand
Posts: 338
  • 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
  • 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
  • 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.
 
Greenhorn
Posts: 11
  • 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!!
 
author & internet detective
Posts: 40198
816
Eclipse IDE VI Editor Java
  • 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.
 
    Bookmark Topic Watch Topic
  • New Topic