Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Q: dbms_lob.write ?
A copy of this was sent to tedchyn_at_yahoo.com
(if that email address didn't require changing)
On Mon, 28 Jun 1999 19:05:25 GMT, you wrote:
>sir, why am I getting following errors for dbms_lob.write ?
>
>Thanks, Ted Chyn(tedchyn_at_yahoo.com)
[snip]
>abcdefghijklmnopqrstuvwxyz
>26
>declare
>*
>ERROR at line 1:
>ORA-06502: PL/SQL: numeric or value error
>ORA-06512: at "SYS.DBMS_LOB", line 526
>ORA-06512: at line 13
>
>
>Sent via Deja.com http://www.deja.com/
>Share what you know. Learn what you don't.
Because you have a NULL in the document column, it needs to be initialized in the database to an EMPTY_CLOB() before you can write to it, consider:
SQL> create table lob_store
2 ( lob_id int,
3 document clob )
4 /
Table created.
SQL>
SQL> insert into lob_store values ( 1, null );
1 row created.
SQL>
SQL> declare
2 lobloc clob;
3 buffer varchar2(32000);
4 amount number := 20;
5 offset number :=1;
6 begin
7 buffer :='abcdefghijklmnopqrstuvwxyz'; 8 amount := length(buffer); 9 dbms_output.put_line(buffer); 10 dbms_output.put_line(to_char(amount)); 11 select document into lobloc from lob_store 12 where lob_id =1 for update; 13 dbms_lob.write(lobloc,amount,1,buffer); 14 commit;
ORA-06502: PL/SQL: numeric or value error ORA-06512: at "SYS.DBMS_LOB", line 526 ORA-06512: at line 13
SQL> update lob_store set document = EMPTY_CLOB() where lob_id = 1;
1 row updated.
SQL>
SQL> declare
2 lobloc clob;
3 buffer varchar2(32000);
4 amount number := 20;
5 offset number :=1;
6 begin
7 buffer :='abcdefghijklmnopqrstuvwxyz'; 8 amount := length(buffer); 9 dbms_output.put_line(buffer); 10 dbms_output.put_line(to_char(amount)); 11 select document into lobloc from lob_store 12 where lob_id =1 for update; 13 dbms_lob.write(lobloc,amount,1,buffer); 14 commit;
PL/SQL procedure successfully completed.
--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'...
Current article is "Part I of V, Autonomous Transactions" updated June 21'st
Thomas Kyte tkyte_at_us.oracle.com Oracle Service Industries Reston, VA USA
Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Mon Jun 28 1999 - 14:30:29 CDT