Home » SQL & PL/SQL » SQL & PL/SQL » dbms_lob.append raising error. (oracle 11g)
dbms_lob.append raising error. [message #601702] Sun, 24 November 2013 11:48 Go to next message
Jothish
Messages: 12
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..
Re: dbms_lob.append raising error. [message #601704 is a reply to message #601702] Sun, 24 November 2013 11:54 Go to previous messageGo to next message
BlackSwan
Messages: 22718
Registered: January 2009
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: dbms_lob.append raising error. [message #603032 is a reply to message #601704] Wed, 11 December 2013 02:19 Go to previous messageGo to next message
Jothish
Messages: 12
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;




Re: dbms_lob.append raising error. [message #603039 is a reply to message #603032] Wed, 11 December 2013 03:02 Go to previous messageGo to next message
Michel Cadot
Messages: 58938
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.

Re: dbms_lob.append raising error. [message #603042 is a reply to message #601704] Wed, 11 December 2013 03:04 Go to previous messageGo to next message
tigsav
Messages: 38
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.
Re: dbms_lob.append raising error. [message #603056 is a reply to message #603042] Wed, 11 December 2013 03:58 Go to previous messageGo to next message
Michel Cadot
Messages: 58938
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.

Re: dbms_lob.append raising error. [message #603060 is a reply to message #603042] Wed, 11 December 2013 04:03 Go to previous messageGo to next message
Jothish
Messages: 12
Registered: February 2009
Location: Chennai
Junior Member
Sorry.. Value is like this
id clob_text
--------------
1
1
1 </empid>
Re: dbms_lob.append raising error. [message #603072 is a reply to message #603056] Wed, 11 December 2013 05:15 Go to previous messageGo to next message
Jothish
Messages: 12
Registered: February 2009
Location: Chennai
Junior Member
As already mentioned error occurs only at the 3rd record.
Re: dbms_lob.append raising error. [message #603076 is a reply to message #603072] Wed, 11 December 2013 05:28 Go to previous messageGo to next message
Michel Cadot
Messages: 58938
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Copy and paste.

Re: dbms_lob.append raising error. [message #603087 is a reply to message #603076] Wed, 11 December 2013 06:19 Go to previous messageGo to next message
Jothish
Messages: 12
Registered: February 2009
Location: Chennai
Junior Member
ORA-22920: row containing the LOB value is not locked
Re: dbms_lob.append raising error. [message #603095 is a reply to message #603087] Wed, 11 December 2013 07:27 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2007
Registered: January 2010
Senior Member
So what's the issue? Error message is self-explanatory. Just use SELECT ... FOR UPDATE.

SY.
Re: dbms_lob.append raising error. [message #603097 is a reply to message #603087] Wed, 11 December 2013 07:29 Go to previous messageGo to next message
Michel Cadot
Messages: 58938
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Wed, 11 December 2013 12:28

Copy and paste.


Re: dbms_lob.append raising error. [message #603102 is a reply to message #603097] Wed, 11 December 2013 07:43 Go to previous messageGo to next message
Jothish
Messages: 12
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 Go to previous messageGo to next message
BlackSwan
Messages: 22718
Registered: January 2009
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.
Re: dbms_lob.append raising error. [message #603109 is a reply to message #603102] Wed, 11 December 2013 08:23 Go to previous message
Solomon Yakobson
Messages: 2007
Registered: January 2010
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.
Previous Topic: external table
Next Topic: Can I use global variable in dynamic sql and outside of sql statment
Goto Forum:
  


Current Time: Sat Aug 30 17:51:56 CDT 2014

Total time taken to generate the page: 0.09440 seconds