Home » SQL & PL/SQL » SQL & PL/SQL » How to Insert Data into a CLOB Column of 86000 characters long
How to Insert Data into a CLOB Column of 86000 characters long [message #214451] Tue, 16 January 2007 08:46 Go to next message
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 #214453 is a reply to message #214451] Tue, 16 January 2007 08:54 Go to previous messageGo to next message
dmitry.nikiforov
Messages: 723
Registered: March 2005
Senior Member
You have to use DBMS_LOB package:

http://download-uk.oracle.com/docs/cd/B10501_01/appdev.920/a96612/d_lob.htm#998100

Rgds.
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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.
Re: How to Insert Data into a CLOB Column of 86000 characters long [message #215096 is a reply to message #214594] Fri, 19 January 2007 05:10 Go to previous messageGo to next message
siva_k
Messages: 4
Registered: January 2007
Junior Member
Hi,

Thanks a lot for the elaborated example.

Regards,
Siva.
Re: How to Insert Data into a CLOB Column of 86000 characters long [message #215212 is a reply to message #215096] Fri, 19 January 2007 14:27 Go to previous message
dan_thorman
Messages: 14
Registered: January 2007
Junior Member
i've used something like this:

declare some_name clob := 'TEXT';
begin
execute immediate 'update table set clob_field = :some_name where condition' using some_name;
end;
Previous Topic: months between
Next Topic: Assigning Values within a Function
Goto Forum:
  


Current Time: Sat Dec 10 01:07:40 CST 2016

Total time taken to generate the page: 0.05941 seconds