Insert CLOB into Oracle database from SQL Developer

Question

I want to insert the same long string into all cells in certain column, which is CLOB type.

It said I should use "bind variables" to do it. So i Googled this:

variable xmlstuff CLOB;
exec :xmlstuff := '<?xml version="1.0"?> ... really long xml...';

UPDATE TABLE_NAME SET COLUMN_NAME = '&&xmlstuff';

Now it still says

The string literal is longer than 4000 characters.

What is the proper use of bind variable in this case?

&&xmlstuff is not a bind variable, by the way.

mustaccio
– mustaccio

2014-04-01 14:03:44 +00:00
Commented Apr 1, 2014 at 14:03 — mustaccio
– mustaccio, Commented Apr 1, 2014 at 14:03
Why are you working with XML as a CLOB and not as XMLType?

evenro
– evenro

2014-04-01 14:06:18 +00:00
Commented Apr 1, 2014 at 14:06 — evenro
– evenro, Commented Apr 1, 2014 at 14:06
@evenro: Because of previous programmers.

StaNov
– StaNov

2014-04-01 14:10:03 +00:00
Commented Apr 1, 2014 at 14:10 — StaNov
– StaNov, Commented Apr 1, 2014 at 14:10

evenro · Accepted Answer · 2014-04-02 00:12:39Z

1

If you are programming using C# or Java - just use the OracleCLOB object, and it will do all the necessary steps.

If you want to use a CLOB in SQL or PL/SQL, you need to allocate it, and release it after using.
search for DBMS_LOB information.

Regarding the 4000 bytes limit - this is a varchar2 limit within SQL.
to bypass this - you can use PL/SQL - which limits you to a varchar of 32KB, which is not as near to the 4GB that you can hold in a CLOB, but that is the limit for "automatic" creation of a CLOB.
if your string is longer than 32K - you'll have to use a DBMS_LOB to load the data into the clog object, by using append on the clob object.

this is the fastest link I found about how to do it: http://geekswithblogs.net/robertphyatt/archive/2010/03/24/write-read-and-update-oracle-clobs-with-plsql.aspx

I wanted to answer fast, so please let me know if you cannot solve your issue after getting this information - and I'll try to explain it better.

edited Apr 2, 2014 at 0:12

answered Apr 1, 2014 at 14:03

evenro

2,66422 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

StaNov Over a year ago

Whoa, that looks hard. I thought the answer is gonna be something like "Just add this line into the SQL script and it will work."

evenro Over a year ago

Sorry :( For short strings it works - but for things that are bigger than the limits- you need to work harder...

StaNov Over a year ago

Thanks anyway. This may help somebody who thinks the same as i did.

evenro Over a year ago

Just for clarification - it is 4000 bytes limit (just fixed it above) - for languages that each character is 2 bytes, it'll be 2000 chars... (important to be accurate..)

Collectives™ on Stack Overflow

Insert CLOB into Oracle database from SQL Developer

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related