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

    Newbie: insertxml and setxsl


      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;


      --open XML files for read

      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);

      -- open XSL styelsheet

      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);

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


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


      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.