Re: Q: dbms_lob.write ?

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Mon, 28 Jun 1999 19:30:29 GMT
Message-ID: <3782cd27.25278047@newshost.us.oracle.com>

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;

 15 end;
 16 /
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

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;

 15 end;
 16 /
abcdefghijklmnopqrstuvwxyz
26

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