select col1, ..., xmltype(my_blob_col) from tableWithBlobColumn
select col1, ..., xmltype(my_blob_col) from tableWithBlobColumn
2006-03-11 - By Bj�rn D�rr Jensen
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
ORA-22275 (See ORA-22275.ora-code.com) 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
