• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1832
  • Last Modified:

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

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
Suriyaraj_Sudalaiappan
Asked:
Suriyaraj_Sudalaiappan
  • 2
  • 2
1 Solution
 
slightwv (䄆 Netminder) Commented:
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
 
Suriyaraj_SudalaiappanAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
You only need to close a CLOB when you manually open it.
0
 
Suriyaraj_SudalaiappanAuthor Commented:
GOOD ONE
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now