Home » SQL & PL/SQL » SQL & PL/SQL » how to reading CLOB contains 20000 characters.
how to reading CLOB contains 20000 characters. [message #233177] Wed, 25 April 2007 02:14 Go to next message
misragopal
Messages: 125
Registered: June 2005
Location: New Delhi, india
Senior Member

hi,
i am posting this topic after good searchs from various forums. i dont find a way how to read a CLOB column from plsql that contains more than 6000 and less than 20000 characters.

create table GMS_Data(GID NUMBER(3), G_DESC CLOB);

insert INTO gms_data VALUES(100, lpad('first 15000-',15000,'0'));
insert INTO gms_data VALUES(100, lpad('total 20000-',20000,'0'));
insert INTO gms_data VALUES(100, lpad('total 10000-',10000,'0'));
insert INTO gms_data VALUES(100, lpad('total 6000-',6000,'0'));
COMMIT;

I want to copy his clob data into temp_clob_reservoir table.
1 clob column will break into multiple rows in this table.

CREATE TABLE temp_clob_reservoir
(gid NUMBER(4), txt VARCHAR2(4000))

Any clue how to do.??
Re: how to reading CLOB contains 20000 characters. [message #233189 is a reply to message #233177] Wed, 25 April 2007 02:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> declare
  2    lg  integer;
  3    i   integer;
  4  begin
  5    for rec in (select gid, g_desc from gms_data) loop
  6      i  := 1;
  7      lg := length(rec.g_desc);
  8      loop
  9        exit when i > lg;
 10        insert into temp_clob_reservoir values (rec.gid, substr(rec.g_desc,i,20000));
 11        i := i + 20000;
 12      end loop;
 13    end loop;
 14  end;
 15  /

PL/SQL procedure successfully completed.

Regards
Michel
Re: how to reading CLOB contains 20000 characters. [message #233783 is a reply to message #233189] Fri, 27 April 2007 08:10 Go to previous messageGo to next message
misragopal
Messages: 125
Registered: June 2005
Location: New Delhi, india
Senior Member

Thanks Michel. this code is now working for me. One more interesting problem i found in my code. as you saw in my question that i am inserting values in clob table with this statement.

insert INTO gms_data VALUES(100, lpad('total 6000-',6000,'0'));

Thru this statement only 4000 length of record get inserted in table. because LPAD or RPAD is having limit of 4000 and without giving error LPAd will insert only 4000 characters in a column. Due to this i am not able to read data > 4000.

but thanks to you. you gave me real light to explore more.
clov is only inserting 65535 characters.. right?
does this mechanism will work for BLOB data type also.

Regards,,
Gopal Misra
Re: how to reading CLOB contains 20000 characters. [message #233790 is a reply to message #233783] Fri, 27 April 2007 08:26 Go to previous message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ CLOB can contain data up to 2GB (or 4GB, don't rememeber but it is in the doc)
2/ with BLOB you can't use SUBSTR.

I have to correct my script 20000 is actually 4000 as you target field is 4000.

Yes, buffer for lpad and rpad is limited to 4000 bytes (and not characters, different if you are using multi-bytes/variable-length character set).

Regards
Michel

[Updated on: Fri, 27 April 2007 08:27]

Report message to a moderator

Previous Topic: exception handling
Next Topic: listing tables with more than n rows
Goto Forum:
  


Current Time: Sun Dec 11 02:09:08 CST 2016

Total time taken to generate the page: 0.04361 seconds