How to Insert Data into a CLOB Column of 86000 characters long [message #214451] |
Tue, 16 January 2007 08:46  |
siva_k
Messages: 4 Registered: January 2007
|
Junior Member |
|
|
Hi,
I am trying to insert data into a Clob Column. The data contains text with ' (single quotation marks) something like
" Hi, Good Morning John's ". The String is too huge say 86000 characters. I tried to insert from sql prompt but couldn't as sqlplus has a restriction of 4000 bytes.
Can anyone please help me in inserting this string.
Thanks in advance.
Regards,
Siva.
|
|
|
|
Re: How to Insert Data into a CLOB Column of 86000 characters long [message #214460 is a reply to message #214451] |
Tue, 16 January 2007 09:03   |
siva_k
Messages: 4 Registered: January 2007
|
Junior Member |
|
|
Is there any way that I can directly insert data using Insert Statement?
How can I view this inserted Data?
I tried using a Varchar2 variable in a procedure. I have even reduced the string to 30000 characters but still was not able to insert. I am receiving an error "Identifier too long".
The link provided didn't provide any options for entering my data with " ' " (single quotes) in it. Can anyone provide more specific DBMS_LOB functions related to this scenario.
Thanks in advance.
Regards,
Siva.
|
|
|
Re: How to Insert Data into a CLOB Column of 86000 characters long [message #214594 is a reply to message #214460] |
Wed, 17 January 2007 03:45   |
dmitry.nikiforov
Messages: 723 Registered: March 2005
|
Senior Member |
|
|
>>The link provided didn't provide any options for entering my
>>data with " ' " (single quotes) in it. Can anyone provide
>>more specific DBMS_LOB functions related to this scenario
What's the problem with "'" ?
WRITEAPPEND doesn't work for you ?
SQL> create table t (data# clob);
Table created.
SQL> declare
2 buffer1 varchar2(32767) := 'Doesn''t ';
3 buffer2 varchar2(32767) := 'this work for you ?';
4 l_data# clob;
5 begin
6 insert into t values(empty_clob()) returning data# into l_data#;
7 dbms_lob.writeappend(l_data#,length(buffer1),buffer1);
8 dbms_lob.writeappend(l_data#,length(buffer2),buffer2);
9 commit;
10 end;
11 /
PL/SQL procedure successfully completed.
SQL> select * from t;
DATA#
--------------------------------------------------------------------------------
Doesn't this work for you ?
Rgds.
|
|
|
|
|