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.
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/oracle...785/e19400.htm
http://wtcis.wtamu.edu/oracle/appdev...ob.htm#1017117
===============================================
I'm not able to see the error in the code - can you?
Greetings
Bjoern