| clob data type [message #387446] |
Thu, 19 February 2009 04:41  |
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  |
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 |
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 |
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 #387460 is a reply to message #387456] |
Thu, 19 February 2009 05:36 |
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.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
