You may get the following puzzling stack of error messages when you attempt to use the XMLTYPE constructor on an empty CLOB.
This error can be easily reproduced in the following way:
select xmltype(empty_clob()) as x
from dual;
ORA-00942: table or view does not existMost likely, in a real life scenario, you initialized a CLOB column with the special value EMPTY_CLOB(), that is standard practice when you need to insert a CLOB value into a table as the result of a conversion from a BLOB type, as shown in this PL/SQL snippet:
ORA-06512: at "SYS.XMLTYPE", line 254
ORA-06512: at line 1
...Note that you can compare the lob locator with the value EMPTY_CLOB().
insert into file_store
( name, document, created_by)
values (l_name, empty_clob(), p_user)
returning id, document into l_id, l_clob;
dbms_lob.convertToClob(l_clob, l_blob, DBMS_LOB.LOBMAXSIZE, l_dest_off, l_src_off, l_csid, l_shift, l_warning);
...
...in case you need to distinguish between a null CLOB locator and an empty CLOB locator.
if l_clob = empty_clob() then
-- lob locator is empty
...
elsif l_clob is null then
-- lob locator is atomically null
...
else
-- lob locator contains a non empty CLOB
...
end if;
...
Once you are sure that the document is not empty, you can try to apply the XMLType constructor and see if you get any parsing errors.
See message translations for ORA-00942 and search additional resources.
2 comments:
IF THIS WORKS I AM GOING TO BUILD AN ALTAR IN MY BACKYARD WITH YOUR STATUE AND I'LL TATOO YOUR NAME IN MY ASS. Let me check.
You are a life saver. Thanks a lot
Post a Comment