What I want is something like: select col1, ..., xmltype(my_blob_col) from
Yet I have an oracle 184.108.40.206 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:
xmlclob clob ;
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.
SELECT id, blobcol
INTO nr, xmlblob
WHERE id=124 ; -- also tried with "...WHERE id=124 for update
dbms_output.put_line('Warning: ' || warning || ' id'||nr);
But I got this:
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
3. The locator is for a BLOB/CLOB/NCLOB and the routine expects a
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.
I'm not able to see the error in the code - can you?