Link to home
Start Free TrialLog in
Avatar of Steve Berger
Steve BergerFlag for United States of America

asked on

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

ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Steve Berger

ASKER

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;
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

You only need to close a CLOB when you manually open it.
GOOD ONE