f



blob2xmltype / convert blob to xmltype / dbms_lob.convertToClob

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


0
3/10/2006 5:35:41 PM
comp.database.oracle 721 articles. 0 followers. Post Follow

0 Replies
1930 Views

Similar Articles

[PageSpeed] 42

Reply: