OraFAQ Forum: SQL & PL/SQL » dbms_lob.append raising error.

Home » SQL & PL/SQL » SQL & PL/SQL » dbms_lob.append raising error. (oracle 11g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

dbms_lob.append raising error. [message #601702]

Sun, 24 November 2013 11:48

Jothish
Messages: 22
Registered: February 2009
Location: Chennai

Junior Member

I am using dbms_lob.append and appending the lob content to the clob variable in the function , it is throwing "ORA-22920 row containing the LOB value is not locked". note that i am not going to update the clob column , just i am getting the clob value and appending that to local variable in that functtion and it return the clob content to the front end. if i use FOR UPDATE it will solve, but i dnt want to use because i am not updating any content in the clob content in the table .... please suggest me if any alternative solution to get rid of from this error..

Report message to a moderator

Re: dbms_lob.append raising error. [message #601704 is a reply to message #601702]

Sun, 24 November 2013 11:54

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

Report message to a moderator

Re: dbms_lob.append raising error. [message #603032 is a reply to message #601704]

Wed, 11 December 2013 02:19

Jothish
Messages: 22
Registered: February 2009
Location: Chennai

Junior Member

When i try to execute the function below raises "ORA-22920 row containing the LOB value is not locked"
note: clob_table has 3 rows but first two rows has null values, but third row contains clob content raises when it try to call the procedure clob_add
(only at the 3 record).

I am not going to update any clob content to the clob_table suggest selecting and will be returned to the function

clob_table has the value:

id clob_text
--------------
1
2
3 </empid>

function clob_data return clob is
clob_dest clob;
begin

for i in 1..(select clob_text from clob_table where id=1) /*retrive 3 rows*/
loop
CLOB_ADD(clob_dest,i.clob_text);
end loop;

return clob_dest;
exception when others then
dbms_output.put_line('Error: '||sqlcode||'--'||sqlerrm);
end;

PROCEDURE CLOB_ADD(
destCLOB IN OUT CLOB,
addCLOB IN CLOB)
IS
BEGIN
IF addCLOB is not null THEN
IF destCLOB is null THEN
destCLOB := addCLOB;
ELSE
dbms_lob.append( destCLOB, addCLOB );
END IF;
END IF;
END;

Report message to a moderator

Re: dbms_lob.append raising error. [message #603039 is a reply to message #603032]

Wed, 11 December 2013 03:02

Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

exception when others then
dbms_output.put_line('Error: '||sqlcode||'--'||sqlerrm);

The first thing to do is to remove this and re-execute and copy and paste the session with the error.

Report message to a moderator

Re: dbms_lob.append raising error. [message #603042 is a reply to message #601704]

Wed, 11 December 2013 03:04

tigsav
Messages: 49
Registered: April 2012

Member

Hi,

Did you want to something like this ?

CREATE OR REPLACE FUNCTION Clob_data RETURN CLOB IS
  clob_dest CLOB;
BEGIN
  FOR i IN (SELECT data FROM clob_table WHERE id = 1) /*retrive 3 rows*/
   LOOP
    Clob_add(clob_dest, i.data);
  END LOOP;

  RETURN clob_dest;
EXCEPTION
  WHEN OTHERS THEN
    dbms_output.Put_line('Error: ' || SQLCODE || '--' || SQLERRM);
END;

select Clob_data() from dual;


This gave me proper output .

And by the way id =1 wont give you 3 rows i guess.

Report message to a moderator

Re: dbms_lob.append raising error. [message #603056 is a reply to message #603042]

Wed, 11 December 2013 03:58

Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

I want something WITHOUT "WHEN OTHERS THEN".
And I want the execution that raises the error.

Report message to a moderator

Re: dbms_lob.append raising error. [message #603060 is a reply to message #603042]

Wed, 11 December 2013 04:03

Jothish
Messages: 22
Registered: February 2009
Location: Chennai

Junior Member

Sorry.. Value is like this
id clob_text
--------------
1
1
1 </empid>

Report message to a moderator

Re: dbms_lob.append raising error. [message #603072 is a reply to message #603056]

Wed, 11 December 2013 05:15

Jothish
Messages: 22
Registered: February 2009
Location: Chennai

Junior Member

As already mentioned error occurs only at the 3rd record.

Report message to a moderator

Re: dbms_lob.append raising error. [message #603076 is a reply to message #603072]

Wed, 11 December 2013 05:28

Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Copy and paste.

Report message to a moderator

Re: dbms_lob.append raising error. [message #603087 is a reply to message #603076]

Wed, 11 December 2013 06:19

Jothish
Messages: 22
Registered: February 2009
Location: Chennai

Junior Member

ORA-22920: row containing the LOB value is not locked

Report message to a moderator

Re: dbms_lob.append raising error. [message #603095 is a reply to message #603087]

Wed, 11 December 2013 07:27

Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA

Senior Member

So what's the issue? Error message is self-explanatory. Just use SELECT ... FOR UPDATE.

SY.

Report message to a moderator

Re: dbms_lob.append raising error. [message #603097 is a reply to message #603087]

Wed, 11 December 2013 07:29

Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Michel Cadot wrote on Wed, 11 December 2013 12:28

Copy and paste.

Report message to a moderator

Re: dbms_lob.append raising error. [message #603102 is a reply to message #603097]

Wed, 11 December 2013 07:43

Jothish
Messages: 22
Registered: February 2009
Location: Chennai

Junior Member

Y i need to use FOR Update clause. Since i am not going to update the clob content or not going to manipulate with that content in the table clob_table. Apart from that if any alternate solution is there??

[Updated on: Wed, 11 December 2013 07:45]

Report message to a moderator

Re: dbms_lob.append raising error. [message #603107 is a reply to message #603102]

Wed, 11 December 2013 08:10

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

22920, 00000, "row containing the LOB value is not locked"
//  *Cause:  The row containing the LOB value must be locked before 
//           updating the LOB value.
//  *Action: Lock the row containing the LOB value before updating the LOB
//           value.

Report message to a moderator

Re: dbms_lob.append raising error. [message #603109 is a reply to message #603102]

Wed, 11 December 2013 08:23

Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA

Senior Member

Jothish wrote on Wed, 11 December 2013 08:43

Y i need to use FOR Update clause. Since i am not going to update the clob content or not going to manipulate with that content in the table clob_table.

And how do we know that? You posted function clob_data which is obviously not a working function. What is this:

for i in 1..(select clob_text from clob_table where id=1) /*retrive 3 rows*/

And Michel asked you at least twice to post (cut & paste) SQL*Plus (or whatever tool you are using) snippet showing your code execution along with all errors.

SY.

Report message to a moderator

Previous Topic:	external table
Next Topic:	Can I use global variable in dynamic sql and outside of sql statment

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Apr 23 02:12:34 CDT 2024