1 Reply Latest reply: Nov 2, 2010 1:49 PM by Marco Gralike RSS

    Newbie: insertxml and setxsl

    527623
      Oracle 11.2.0.1

      I am a newbie to both LOB and XML - so here goes.

      I am trying to read an xml file from oracle directory (same location in which a stylesheet also resides). I have the following procedure:

      **************
      create or replace
      procedure xmlinsert(v_file_name IN varchar2) is
           
      v_xml_file     bfile := bfilename('PERFXML' , v_file_name);
      v_lob     clob;

      v_xsl_file bfile := bfilename('PERFXML' , 'perf.xsl');
      v_xsl_clob clob;


           src_offset number := 1;
           dst_offset number := 1;
           cs_id number := NLS_CHARSET_ID('UTF8'); /* 998 */
           lang_ctx number := dbms_lob.default_lang_ctx;
           warning number;

           insCtx DBMS_XMLSave.ctxType;
           rows number;


      begin

      --open XML files for read

           dbms_lob.createtemporary(v_lob,TRUE);
           dbms_lob.open(v_lob,dbms_lob.lob_readwrite);
      dbms_lob.fileopen(v_xml_file, dbms_lob.file_readonly);
           dbms_lob.loadclobfromfile(v_lob, v_xml_file, dbms_lob.getlength(v_xml_file), dst_offset, src_offset, cs_id, lang_ctx,warning);
           dbms_lob.close(v_xml_file);
           dbms_lob.close(v_lob);

      -- open XSL styelsheet

           dbms_lob.createtemporary(v_xsl_clob,TRUE);
           dbms_lob.open(v_xsl_clob,dbms_lob.lob_readwrite);
      dbms_lob.fileopen(v_xsl_file, dbms_lob.file_readonly);
      dbms_lob.loadclobfromfile(v_xsl_clob, v_xsl_file, dbms_lob.getlength(v_xsl_file), dst_offset, src_offset, cs_id, lang_ctx,warning);
           dbms_xmlsave.setxslt(ctxHdl => insCtx, stylesheet=> v_xsl_clob);
      dbms_lob.close(v_xsl_file);
      dbms_lob.close(v_xsl_clob);

      -- insert data to 'files' table
           insCtx := dbms_xmlsave.newContext('FILES');
           dbms_xmlsave.SetIgnoreCase(insCtx,1);

      dbms_xmlsave.setDateFormat(insCtx,'yyyy-MM-dd''T''hh:mm:ss');
      dbms_xmlsave.setRowTag(insCtx,'file');

           rows := dbms_xmlsave.insertXML(insCtx, v_lob);
           dbms_xmlsave.closeContext(insCtx);
      end;

      **********************************

      The problem is that I keep getting an error:

      ORA-22994: source offset is beyond the end of the source LOB
      ORA-06512: at "SYS.DBMS_LOB", line 890
      ORA-06512: at "PERF_TOOLS.XMLINSERT", line 39
      ORA-06512: at line 1
      22994. 00000 - "source offset is beyond the end of the source LOB"
      *Cause:    The source offset for a LOB COPY or LOB LOADFROMFILE
      is beyond the end of the source LOB.
      *Action:   Check the length of the LOB and then adjust the source offset.

      I am not sure what I am doing wrong.

      Thanks in advance.