Improve company productivity with a Business Account.Sign Up

x
?
Solved

DBMS_LOB.ISOPEN throws the error ora-22275 invalid lob locator

Posted on 2011-03-11
4
Medium Priority
?
1,848 Views
Last Modified: 2012-05-11
DBMS_LOB.ISOPEN throws the error below error.

ora-22275 invalid lob locator

The below assume that i am getting the v_lob_length as empty. But i am getting error when it comes to DBMS_LOB.ISOPEN.

Please correct me if i am wrong.

here is my code:


DBMS_LOB.createtemporary(v_xml_clob, TRUE);
DBMS_LOB.open(v_xml_clob, DBMS_LOB.lob_readwrite);

SELECT  DBMS_XMLGEN.getxml ('SELECT object_name, object_type from dba_objects where rownum <= 5')
			)
INTO    v_xml_clob
FROM    DUAL;

v_lob_length    := NVL(DBMS_LOB.getlength(v_xml_clob),0);

v_index       := 1;

fnd_file.put_line(fnd_file.log,'v_lob_length : '||v_lob_length);

WHILE v_index <= v_lob_length
LOOP
    v_read_cnt   := 32767;
    DBMS_LOB.read (
		v_xml_clob, 
		v_read_cnt, 
		v_index, 
		v_chunk
		);

    fnd_file.put(fnd_file.output,v_chunk);
    v_index := v_index + v_read_cnt;
END LOOP; 

IF DBMS_LOB.ISOPEN(v_xml_clob) = 1
THEN
    DBMS_LOB.close(v_xml_clob);
    DBMS_LOB.freetemporary(v_xml_clob);
END IF;

Open in new window

0
Comment
  • 2
  • 2
4 Comments
 
LVL 79

Accepted Solution

by:
slightwv (䄆 Netminder) earned 2000 total points
ID: 35109140
The problem is selecting into a CLOB reinitializes the lob locator.  When the select returns no rows the lob is actually NULL and not initialized.

There is no need to create a temporary lob when you are selecting into one.
declare
	v_xml_clob clob;
	v_lob_length number;
	v_index number;
    v_read_cnt number;
	v_chunk varchar2(32767);
begin


SELECT  DBMS_XMLGEN.getxml ('SELECT object_name, object_type from dba_objects where rownum <= 5')
INTO    v_xml_clob
FROM    DUAL;


v_lob_length    := NVL(DBMS_LOB.getlength(v_xml_clob),0);

v_index       := 1;

--fnd_file.put_line(fnd_file.log,'v_lob_length : '||v_lob_length);

WHILE v_index <= v_lob_length
LOOP
    v_read_cnt   := 32767;
    DBMS_LOB.read (
		v_xml_clob, 
		v_read_cnt, 
		v_index, 
		v_chunk
		);

    --fnd_file.put(fnd_file.output,v_chunk);
    v_index := v_index + v_read_cnt;
END LOOP; 

end;
/

Open in new window

0
 

Author Comment

by:Suriyaraj_Sudalaiappan
ID: 35109225
Thanks for your reply. I have another doubt. In your code, no need to use the below condition?? if yes, why?

IF DBMS_LOB.ISOPEN(v_xml_clob) = 1
THEN
    DBMS_LOB.close(v_xml_clob);
END IF;
0
 
LVL 79

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35109486
You only need to close a CLOB when you manually open it.
0
 

Author Closing Comment

by:Suriyaraj_Sudalaiappan
ID: 35122678
GOOD ONE
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

605 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question