Home » SQL & PL/SQL » SQL & PL/SQL » insert data > 4000 bytes into clob datatype columns (10g)
insert data > 4000 bytes into clob datatype columns [message #388517] Wed, 25 February 2009 02:22 Go to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

Hi,

We need to insert a query into a clob column with query size > 4000 bytes. Oracle SQL parser can only handle string literals up to 4000 characters in length when inserting or updating.

Is there an alternate way to insert the same?

Thank you

Re: insert data > 4000 bytes into clob datatype columns [message #388518 is a reply to message #388517] Wed, 25 February 2009 02:30 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You can insert up to 32k from pl/sql.
If you need more that that, you need to convert each chunk of text to a clob, with the TO_CLOB function, and concatenate these clobs:
INSERT INTO test_table (clob_col) 
VALUES (to_clob('long string 1')||to_clob('long string 2'));
Re: insert data > 4000 bytes into clob datatype columns [message #388519 is a reply to message #388517] Wed, 25 February 2009 02:33 Go to previous message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
@user71408,

Please Search the Forum First. This has been answered many times.
Anyways this link might help, especially look into the demo @JRowbottom provided.

[Edit: Damn!! I am slow]

Regards,
Jo

[Updated on: Wed, 25 February 2009 02:35]

Report message to a moderator

Previous Topic: Sql query for multiple parameters
Next Topic: return xml in sys_refcursor
Goto Forum:
  


Current Time: Wed Dec 07 10:27:32 CST 2016

Total time taken to generate the page: 0.13675 seconds