Home » SQL & PL/SQL » SQL & PL/SQL » Ora-6502 on inserting a clob via pl/sql
Ora-6502 on inserting a clob via pl/sql [message #268747] Wed, 19 September 2007 10:22 Go to next message
orausern
Messages: 817
Registered: December 2005
Senior Member
Hi
I am trying insert a large xml text file with lots of characters (15000 chars) using dbms_lob. the table in which it is to be inserted is:
SQL> desc nt
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 N                                                  CLOB



the pl/sql code is like:
Delete from nt
;
commit;

declare 
	v clob;
v_len number;
v1 varchar2(4000);
Begin
insert into nt values (null);
commit;
v := '<?xml version="1.0" encoding="utf-8" ?>
<Application name="aaaa">
--	aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa...put any 10000 characters here...

</Application>
';
--dbms_output.put_line(to_char('length is :'||dbms_lob.getlength(v)));
v_len:=dbms_lob.getlength(v);
for i in 1..v_len loop
v1:=null;
v1:=dbms_lob.substr(v,(i-1)*3000+1,3000);
--update nt set n=n||v1;
dbms_output.put_line('count iterations..'||to_char(i));
insert into nt(n) Values(v);
end loop;
commit;
End;
/
when i try to insert it via pl-sql I get this error:

SQL> @d:\1

1 row deleted.


Commit complete.

count iterations..1
count iterations..2
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 2304



Thanks for the help!
Nirav
Re: Ora-6502 on inserting a clob via pl/sql [message #268749 is a reply to message #268747] Wed, 19 September 2007 10:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Where is line 2304?
What is the use of v1?
Does not the number of loop something like v_len/3000?
What is your Oracle version (4 decimals)?

Regards
Michel

Re: Ora-6502 on inserting a clob via pl/sql [message #268915 is a reply to message #268747] Thu, 20 September 2007 01:58 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I don't think that you've read the documentation for the DBMS_LOB package.

DBMS_LOB.SUBSTR rather irritatingly has its Position and Lenght parameters the other way round to the normal Substr:
DBMS_LOB.SUBSTR (
   lob_loc     IN    CLOB   CHARACTER SET ANY_CS,
   amount      IN    INTEGER := 32767,
   offset      IN    INTEGER := 1)
  RETURN VARCHAR2 CHARACTER SET lob_loc%CHARSET;


As Michael says - Why are you splitting the CLOB down anyway?
Previous Topic: Help w/CLOB error_v2
Next Topic: ora-01722 when using to_number (merged)
Goto Forum:
  


Current Time: Sat Dec 10 01:20:33 CST 2016

Total time taken to generate the page: 0.10518 seconds