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

From: Bjørn Dörr Jensen <B.D.Jensen_at_gmx.net>
Date: Sat, 11 Mar 2006 13:39:01 +0100
Message-ID: <001e01c64508$c6353af0$6701a8c0@camelopardalis>

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

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

Received on Sat Mar 11 2006 - 06:39:01 CST