Re: ORA22920 from DBMS

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Sun, 18 Apr 1999 18:43:18 GMT
Message-ID: <371b2775.4973181@192.86.155.100>

A copy of this was sent to barryhj_at_my-dejanews.com (if that email address didn't require changing) On Fri, 16 Apr 1999 17:27:51 GMT, you wrote:

>SQL> @c:\o8\lobs\lobcopy
>DOC> Version 1.0, last updated 8/8/97
>DOC> This file demonstrates the DBMS_LOB.COPY routine, as described in
>DOC> Chapter 21 of _Oracle8 PL/SQL Programming_ by Scott Urman. */
>DECLARE
>*
>ERROR at line 1:
>ORA-22920: row containing the LOB value is not locked
>ORA-06512: at "SYS.DBMS_LOB", line 290
>ORA-06512: at line 18
>
>Is this the source of destination LOB that need to be locked? Does the table
>need a row level locking trigger? Or..... Is this an Oracle8 bug.
>
>I looked through the Oracle Web site and searched the net for insight without
>success. Where on the web should I look?
>

I didn't see the example in your post but the answer is that the row containing the dest_lob in the dbms_lob.copy command *must be* locked by you. Here is an example showing it failing and then working. When you select the destination lob out of the database, if you haven't already updated or inserted that row without committing, you need to select it out with FOR UPDATE to lock it.

SQL> create table t ( x clob, y clob );

Table created.

SQL>
SQL> insert into t values ( 'How Now Brown Cow', empty_clob() );

1 row created.

SQL> commit;

Commit complete.

SQL> 
SQL> 
SQL> declare
  2          l_x     clob;
  3          l_y     clob;
  4  begin
  5          select * into l_x, l_y from t;
  6  
  6          dbms_lob.copy( l_y, l_x, dbms_lob.getlength(l_x), 1, 1 );

7 end;
8 /
declare
*
ERROR at line 1:

ORA-22920: row containing the LOB value is not locked
ORA-06512: at "SYS.DBMS_LOB", line 290
ORA-06512: at line 6


SQL>

SQL>
SQL> declare

  2          l_x     clob;
  3          l_y     clob;
  4  begin
  5          select * into l_x, l_y from t FOR UPDATE;
  6  
  6          dbms_lob.copy( l_y, l_x, dbms_lob.getlength(l_x), 1, 1 );

7 end;
8 /

PL/SQL procedure successfully completed.

>-----------== Posted via Deja News, The Discussion Network ==----------
>http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

--
http://govt.us.oracle.com/ -- downloadable utilities

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Sun Apr 18 1999 - 13:43:18 CDT

Re: ORA22920 from DBMS_LOB.COPY