select col1, ..., xmltype(my_blob_col) from tableWithBlobColumn

From: Bjørn Dörr Jensen <B.D.Jensen@xxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Date: Sat, 11 Mar 2006 13:39:01 +0100

Hi! What I want is something like: select col1, ..., xmltype(my_blob_col) from tableWithBlobColumn. Yet I have an oracle 8.1.7.4 db storing xml as blob. (yes, yes it should have been clob, but it wasn't my descision) Soon we upgrade to 10g, so I want to convert them to xmltype (-:

I found that the function xmltype can take an clob, but I have an blob that
should be converted.
Here what I tried:

=======================================================================
DECLARE
 nr number;
 xmlclob clob ;
 xmlblob blob;
 src_offset INTEGER := 1;  -- (IN)Offset in characters in the source LOB
for the start of the read.
 dest_offset INTEGER := 1; -- (IN)Offset in bytes in the destination LOB
for the start of the write. Specify a value of 1 to start at the beginning
of the LOB.
 warning INTEGER;          -- (OUT) Warning message. This parameter
indicates when something abnormal happened during the conversion. You are
responsible for checking the warning message.
 lang_context INTEGER := dbms_lob.default_lang_ctx; -- (IN) Language
context, such as shift status, for the current conversion.
BEGIN

SELECT id, blobcol
  INTO nr, xmlblob
FROM e_data
WHERE id=124 ; -- also tried with "...WHERE id=124 for update

SELECT EMPTY_CLOB()
 INTO xmlclob
FROM DUAL;


dbms_lob.convertToClob(xmlclob, xmlblob,
                      DBMS_LOB.LOBMAXSIZE,
                      dest_offset, src_offset,
                      DBMS_LOB.default_csid,
                      lang_context,
                      warning);

 dbms_output.put_line('Warning: ' || warning || ' id'||nr);
end;
/
============================================
But I got this:
Error report:
ORA-22275: invalid LOB locator specified
ORA-06512: at "SYS.DBMS_LOB", line 683
ORA-06512: at line 20

ORA-22275 invalid LOB locator specified
Cause: One of the following:
1.        The LOB locator was never initialized.
2.        The locator is for a BFILE and the routine expects a
BLOB/CLOB/NCLOB locator.
3.        The locator is for a BLOB/CLOB/NCLOB and the routine expects a
BFILE locator.
4.        An attempt was made to update the LOB in a trigger body -- LOBs in
trigger bodies are read only.
Action: For (1), initialize the LOB locator by selecting into the locator
variable or by setting the LOB locator to empty. For (2) and (3), pass the
correct type of locator into the routine. For (4), remove the trigger body
code that updates the LOB value.

http://www.csee.umbc.edu/help/oracle8/server.815/a67785/e19400.htm
http://wtcis.wtamu.edu/oracle/appdev.101/b10802/d_lob.htm#1017117
===============================================
Where is the error?
As I can see, we can exclude case 2,3, and 4.
So it must be case 1 - but how could that be?

If could get the above to work, I could add some lines off code an be happy:
my_xmltype_variable := xmltype(xmlclob); -- my_xmltype declared as xmltype

Thanks for help (-:

Greetings
Bjoern

--
//www.freelists.org/webpage/oracle-l

select col1, ..., xmltype(my_blob_col) from tableWithBlobColumn

Other related posts: