Home » SQL & PL/SQL » SQL & PL/SQL » clob data type (oracle 10g)
clob data type [message #387446] Thu, 19 February 2009 04:41 Go to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
hi,
i am inserting data into a table which is having clob as a data type in oracle sql developer.iam inserting a huge amount of data into that.it is throwing exception like "strig buffer too long"
will oracle sql dveloper not support that much amount of data into it?
Re: clob data type [message #387449 is a reply to message #387446] Thu, 19 February 2009 04:45 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
How, exactly, are you inserting the data?
How long is the value you are inserting?
What is the error you are getting?
Re: clob data type [message #387450 is a reply to message #387449] Thu, 19 February 2009 04:48 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
INSERT INTO EMREncounterDataHistory ( ENCOUNTER_HISTORY_DATA_ID,ENCOUNTER_ID,PATIENT_ID,INSERT INTO EMREncounterDataHistory ( ENCOUNTER_HISTORY_DATA_ID,ENCOUNTER_ID,PATIENT_ID,ENCOUNTER_NARRATION,VERSION,AMMEND_USER,AMMEND_DATE_TIME ) VALUES (1505, 4441, '3338', '<deleted pointless text>', '1.001', '1590_arun', to_date('19-FEB-2009 03:11:00 PM', 'dd-mon-yyyy hh:mi:ss PM') )



where ENCOUNTER_NARRATION is a clob data type column
and exception is string buffer too long

[edited to remove lots of text that added nothing to the problem]

[Updated on: Thu, 19 February 2009 04:56] by Moderator

Report message to a moderator

Re: clob data type [message #387454 is a reply to message #387450] Thu, 19 February 2009 05:05 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
4/10 for actually reading what I asked for.

What is the EXACT TEXT of the error you are getting?
The ORAxxxxx bit at the start is not an optional extra - it's an integral part of the error message.

I'm assuming that you're doing this in SQL rather than in Pl/SQL.

In SQL, a varchar2 has a maximum length of 4000 chrs
A quote delimited string is, by default, treated as a varchar2.

If you want to create a clob longer than that, you need to split your string up into 4000 chr chunks, convert them to clobs, and concatenate them.

Here's a demo:
create table test_107 (col_1 clob);

insert into test_107 values ('<4000 chr string>'
                            );
                            
insert  into test_107 values (to_clob('<4000 chr string>'
                                    ||'<4000 chr string>'
                                    ));
                                    
insert into test_107 values (to_clob('<4000 chr string>')
                           ||to_clob('<4000 chr string>')
                           );
                                    
-- Make a 4000 chr string with this query:
select rpad('A',4000,'A') from dual;
Re: clob data type [message #387456 is a reply to message #387454] Thu, 19 February 2009 05:12 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
ok the string size is exceeding what you mentioned as.in such scenario could i insert it through application server.whether it will work?
Re: clob data type [message #387460 is a reply to message #387456] Thu, 19 February 2009 05:36 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
That would depend what handled the call from the App server. It's most likely to be pl/sql there, which raises the varchar2 size limit to 32k.
Re: clob data type [message #387461 is a reply to message #387460] Thu, 19 February 2009 05:41 Go to previous message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
thanks for your suggestions experts
Previous Topic: Sending email through pl/sql procedure
Next Topic: Storage capacity of NUMBER data-type
Goto Forum:
  


Current Time: Tue Dec 06 00:16:52 CST 2016

Total time taken to generate the page: 0.12917 seconds