Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Display CLOB in web page

Posted on 2011-02-12
12
1,420 Views
Last Modified: 2012-05-11
Hi Experts,

We have a web page where in we display the records from table (one of cloumn is clob) using Oracle query

One of the particular record length is 73244 and its throwing exception
ORA-22835: Buffer too small for CLOB to CHAR or BLOB to RAW conversion (actual: 73244, maximum: 4000)

We do not wish to truncate to 4000 characters using DBMS_LOB.SUBSTR function.

Is there any way to display the entire contents in SQL ? ( I cannot use PL/SQL as is web page query)

Thanks





0
Comment
Question by:ajexpert
12 Comments
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34880545
What web language and what data provider are you using?
0
 
LVL 14

Author Comment

by:ajexpert
ID: 34880861
When I execute the query in toad I get the error as I posted. Can u please help me how to convert clob column

there is no problem with data provider or web page. My goal is to modify query in suchh a way that I shouldn't get any error while executing from toad
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34880996
Toad should handle CLOBs without a problem.  What is the query?  Are you trying to concatenate a clob with another string?
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 14

Author Comment

by:ajexpert
ID: 34881137
No I am not trying to concatenate with other string

yes toad can handle clobs but I m not able to see data, so we use TO_CHAR funtion. Since the data is more than 4000 characters it is throwing error as I stated above
0
 

Expert Comment

by:Suriyaraj_Sudalaiappan
ID: 34881763
Plz try to convert by use of TO_CLOB function
0
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 400 total points
ID: 34883419
You can only use to_clob when inserting a long into a new table.

As you already know, you cannot use to_char on a clob this way is it is larger than 4000 character.

You have me confused.  To can or cannot select the native clob in toad?  I assume that you can and the problem is with native clobs with the web page.

If this is correct, I need to know the web language and data provider.

0
 
LVL 5

Assisted Solution

by:Sanjeev Labh
Sanjeev Labh earned 100 total points
ID: 34884038
Hi I am also a little perplexed here since as far as I know toad can easily handle and display any clob coloumn being selected in a query.
e.g.

select my_clob from my_tab

toad returns the column data in the result row you would find an ellipse (...) symbol. Once you click it the whole text window opens with the clob data. So, it is really quite simple in toad. I am not sure if you are doing the same or I have got your question wrongly.
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34884533
sanjeevlabh,

The issue was with using to_char on the clob from toad and the web code.  That is what generates the error.

We need to know what the web app was developed with so we can get that code to handle native clobs without trying to convert it to a string.

0
 
LVL 3

Expert Comment

by:dbauermann
ID: 34886925
ajexpert,

If you are using owa packages, I suggest the following code:

(...)
v_image   blob;
v_buffer   RAW(32767);
v_offset   BINARY_INTEGER := 1;
v_buffer_size   NUMBER := 32767;
(...)
OWA_UTIL.MIME_HEADER('image/gif');
 -- read the LOB content in a loop and send it across to the browser
LOOP
   DBMS_LOB.READ(v_image, v_buffer_size, v_offset, v_buffer);
   -- convert the raw content read into varchar2 and send it to the browser
   htp.prn(UTL_RAW.CAST_TO_VARCHAR2(v_buffer));
   v_offset:= (v_offset + v_tamanho_buffer);
END LOOP;
(...)

Open in new window

0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34887592
>>If you are using owa packages, I suggest the following code:

That code is for a BLOB. not a CLOB.

0
 
LVL 14

Author Comment

by:ajexpert
ID: 34889164
@slightwv:

We are using java - provider jdbc for oracle

I spoke to my team member letting him know that he needs to write a routine to display CLOB from db

Thanks
0
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 400 total points
ID: 34889219
0

Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
exp/imp 25 86
Can't Access My Database 57 77
Oracle Query - Return results based on minimum value 8 35
pivot rows to columns 1 35
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

838 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question