10 Replies Latest reply: Aug 6, 2012 3:53 AM by AlexAnd RSS

    clob is not working for bulk data files in PL/SQL XML program

    953171
      Hi Odie,

      we took your help to fix the our issue before

      "https://forums.oracle.com/forums/thread.jspa?threadID=2238458&tstart=105"

      working fine for : program is working for smaller size data.

      Issue : now we have problem with the largr size data .

      getting the below error:

      -------------------------------------------------
      Arguments
      ------------
      P_dir_name='/tmp'
      P_file_name='CCBGO.COLO_CNG.RESPONSES.20120802.00054131826'
      ------------

      Environment will now switch to UTF-8 code-set.
      Parts of this log file may not display correctly
      as a result. This is an expected behavior.

      XML_REPORTS_XENVIRONMENT is :
      /apps/applmgr/product/OFDEV/ofdevora/806/guicommon6/tk60/admin/Tk2Motif_UTF8.rgb

      XENVIRONMENT is set to /apps/applmgr/product/OFDEV/ofdevora/806/guicommon6/tk60/admin/Tk2Motif_UTF8.rgb


      Current NLS_LANG and NLS_NUMERIC_CHARACTERS Environment Variables are :
      American_America.UTF8

      '.,'

      stat_low = 8B
      stat_high = 0
      emsg:was terminated by signal 11

      ----------------------------------------------

      Appreciated for your earlier support.

      Kindly suggest .

      Many Thanks,
      Ramesh.
        • 1. Re: clob is not working for bulk data files in PL/SQL XML program
          AlexAnd
          >
          Hi Odie,
          >
          :)

          it's apps error

          Re: Concurrent program completed with warning
          Re: Concurrent program completed with warning
          • 2. Re: clob is not working for bulk data files in PL/SQL XML program
            953171
            Thanks ALex,

            your are true it is concurrent program error ,
            but it is working for small amount of data and generating the output and it is not working for larger data.
            i have placed the code which i have used kindly suggest where i am going wrong.

            i am calling the .rdf through the concurrent program, i've used the below query in RDF


            select
            BATCHHEADER
            ,BATCHTRAILER
            ,RqUID
            ,Severity
            ,PmtRefId
            ,StatusDesc
            ,ErrorDesc
            ,AsOfDate
            ,AsOfTime
            ,RqUID1
            ,SPRefId
            from table(CL_CXFRFXFH_PKG.rcacknowledgments(:P_dir_name,:P_file_name));

            kindly find the below code for the package CL_CXFRFXFH_PKG.

            ==========================
            CREATE OR REPLACE package body APPS.CL_CXFRFXFH_PKG is

            function rcacknowledgments (p_directory in varchar2, p_filename in varchar2)
            return TRecordTable pipelined
            is

            nb_rec number := 1;
            tmp_xml clob;
            tmp_file clob;
            rec TRecord;

            begin

            dbms_lob.createtemporary(tmp_file, true);
            tmp_file := dbms_xslprocessor.read2clob(p_directory, p_filename);

            rec.BATCHHEADER := regexp_replace(tmp_file, '.*<BATCHHEADER>(.*)</BATCHHEADER>.*', '\1', 1, 1, 'n');
            rec.BATCHTRAILER := regexp_replace(tmp_file, '.*<BATCHTRAILER>(.*)</BATCHTRAILER>.*', '\1', 1, 1, 'n');

            loop

            tmp_xml := regexp_substr(tmp_file, '<\?xml[^?]+\?>\s*<([^>]+)>.*?</\1>', 1, nb_rec, 'n');
            exit when length(tmp_xml) = 0;
            --dbms_output.put_line(tmp_rec);
            nb_rec := nb_rec + 1;

            select RqUID, Severity, PmtRefId, StatusDesc, ErrorDesc, AsOfDate, AsOfTime, RqUID1, SPRefId
            into rec.RqUID
            , rec.Severity
            , rec.PmtRefId
            , rec.StatusDesc
            , rec.ErrorDesc
            , rec.AsOfDate
            , rec.AsOfTime
            , rec.RqUID1
            , rec.SPRefId
            from xmltable(
            '/CMA/BankSvcRq' passing xmltype(tmp_xml)
            columns RqUID varchar2(3000) path 'RqUID'
            , Severity varchar2(3000) path 'XferAddRs/Status/Severity'
            , PmtRefId varchar2(3000) path 'XferAddRs/Status/PmtRefId'
            , StatusDesc varchar2(3000) path 'XferAddRs/Status/StatusDesc'
            , ErrorDesc varchar2(3000) path 'XferAddRs/Status/ErrorDesc'
            , AsOfDate varchar2(3000) path 'XferAddRs/Status/AsOfDate'
            , AsOfTime varchar2(3000) path 'XferAddRs/Status/AsOfTime'
            , RqUID1 varchar2(3000) path 'XferAddRs/RqUID'
            , SPRefId varchar2(3000) path 'XferAddRs/SPRefId'
            )
            ;

            pipe row ( rec );

            end loop;

            dbms_lob.freetemporary(tmp_file);

            return;

            end;

            end;
            /
            ============================================

            Many Thanks,
            Ramesh.
            • 3. Re: clob is not working for bulk data files in PL/SQL XML program
              AlexAnd
              >
              dbms_lob.createtemporary(tmp_file, true);
              tmp_file := dbms_xslprocessor.read2clob(p_directory, p_filename);

              rec.BATCHHEADER := regexp_replace(tmp_file, '.*<BATCHHEADER>(.*)</BATCHHEADER>.*', '\1', 1, 1, 'n');
              rec.BATCHTRAILER := regexp_replace(tmp_file, '.*<BATCHTRAILER>(.*)</BATCHTRAILER>.*', '\1', 1, 1, 'n');

              loop

              tmp_xml := regexp_substr(tmp_file, '<\?xml[^?]+\?>\s*<([^>]+)>.*?</\1>', 1, nb_rec, 'n');
              exit when length(tmp_xml) = 0;
              >
              what is purpose of above code?

              :P_file_name is xml file?

              did you try to use single select statement in rdf?
              like
              select <data>
                  from (XMLTABLE('*'
                                 PASSING (XMLTYPE(bfilename('XMLSTORE','data.xml'),NLS_CHARSET_ID('AL32UTF8')))
                                 COLUMNS <data> xmltype PATH '/*'
                                )
                       )
              • 4. Re: clob is not working for bulk data files in PL/SQL XML program
                odie_63
                Hi Ramesh,

                Any chance you can get a meaningful Oracle error message instead?
                The log file doesn't give anything useful to analyze the problem.
                • 5. Re: clob is not working for bulk data files in PL/SQL XML program
                  953171
                  HI,

                  thanks to all for providing the suggestions...

                  i am getting the XML data file i am converting this to Excel output for that i have used the below code.

                  the program is working fine when the data file less than 33,892kb, if the file exceeds more than 33,892kb the program completed error,
                  i've not sure what exactly going wrong since the program is not giving proper error.

                  Note : I have used CLOB data type for the " tmp_file " .




                  CREATE OR REPLACE package body APPS.CL_CXFRFXFH_PKG is

                  function rcacknowledgments (p_directory in varchar2, p_filename in varchar2)
                  return TRecordTable pipelined
                  is

                  nb_rec number := 1;
                  tmp_xml clob;
                  tmp_file clob;
                  rec TRecord;

                  begin

                  dbms_lob.createtemporary(tmp_file, true);
                  tmp_file := dbms_xslprocessor.read2clob(p_directory, p_filename);

                  rec.BATCHHEADER := regexp_replace(tmp_file, '.*<BATCHHEADER>(.*)</BATCHHEADER>.*', '\1', 1, 1, 'n');
                  rec.BATCHTRAILER := regexp_replace(tmp_file, '.*<BATCHTRAILER>(.*)</BATCHTRAILER>.*', '\1', 1, 1, 'n');

                  loop

                  tmp_xml := regexp_substr(tmp_file, '<\?xml[^?]+\?>\s*<([^>]+)>.*?</\1>', 1, nb_rec, 'n');
                  exit when length(tmp_xml) = 0;
                  --dbms_output.put_line(tmp_rec);
                  nb_rec := nb_rec + 1;

                  select RqUID, Severity, PmtRefId, StatusDesc, ErrorDesc, AsOfDate, AsOfTime, RqUID1, SPRefId
                  into rec.RqUID
                  , rec.Severity
                  , rec.PmtRefId
                  , rec.StatusDesc
                  , rec.ErrorDesc
                  , rec.AsOfDate
                  , rec.AsOfTime
                  , rec.RqUID1
                  , rec.SPRefId
                  from xmltable(
                  '/CMA/BankSvcRq' passing xmltype(tmp_xml)
                  columns RqUID varchar2(3000) path 'RqUID'
                  , Severity varchar2(3000) path 'XferAddRs/Status/Severity'
                  , PmtRefId varchar2(3000) path 'XferAddRs/Status/PmtRefId'
                  , StatusDesc varchar2(3000) path 'XferAddRs/Status/StatusDesc'
                  , ErrorDesc varchar2(3000) path 'XferAddRs/Status/ErrorDesc'
                  , AsOfDate varchar2(3000) path 'XferAddRs/Status/AsOfDate'
                  , AsOfTime varchar2(3000) path 'XferAddRs/Status/AsOfTime'
                  , RqUID1 varchar2(3000) path 'XferAddRs/RqUID'
                  , SPRefId varchar2(3000) path 'XferAddRs/SPRefId'
                  )
                  ;

                  pipe row ( rec );

                  end loop;

                  dbms_lob.freetemporary(tmp_file);

                  return;

                  end;

                  end;
                  /

                  sorry Odie the program is not giving any proper error except above error which i have posted earlier

                  many thanks,
                  Ramesh.
                  • 6. Re: clob is not working for bulk data files in PL/SQL XML program
                    odie_63
                    Couldn't you test the query alone and see what happens?
                    SELECT * FROM TABLE(APPS.CL_CXFRFXFH_PKG.rcacknowledgments('DIRECTORY','FILENAME'));
                    • 7. Re: clob is not working for bulk data files in PL/SQL XML program
                      953171
                      Hi Odie,

                      thanks a lot odie.

                      i have run the below query by passing the data file name which is having the size 33,557kb


                      it is executed fine and given the data in salect statement

                      SELECT * FROM TABLE(APPS.CL_CXFRFXFH_PKG.rcacknowledgments('/tmp','CCBGO.COLO_CNG.RESPONSES.201208029.00054131826'));
                      -- this is is q=working fine and file size of this file is 33,557.


                      SELECT * FROM TABLE(APPS.CL_CXFRFXFH_PKG.rcacknowledgments('/tmp','CCBGO.COLO_CNG.RESPONSES.20120802.00054131826'));
                      -- this is giving error and file size if this file is 41,902kb

                      i am getting below error if the file size crosses 33,557kb


                      [Error] Execution (82: 21): ORA-29284: file read error
                      ORA-06512: at "SYS.UTL_FILE", line 127
                      ORA-06512: at "SYS.UTL_FILE", line 1204
                      ORA-06512: at "XDB.DBMS_XSLPROCESSOR", line 268
                      ORA-06512: at "APPS.CL_CXFRFXFH_PKG", line 15

                      Many Thanks,
                      • 8. Re: clob is not working for bulk data files in PL/SQL XML program
                        AlexAnd
                        Bug 5204876 : DBMS_XMLPARSER FAILS READING FILES HAVING A LINE SIZE OF > 32K
                        Bug 5937802 : DBMS_XSLPROCESSOR.READ2CLOB FAILS IF DOC MORE THAN ABOUT 32K AND NO NEW LINES
                        Bug 5232410 : ON 10.2 XML PARSER FAILS PARSING LARGE XML FILES ORA-29280

                        also you can try to use dbms_lob.loadclobfromfile
                        • 9. Re: clob is not working for bulk data files in PL/SQL XML program
                          953171
                          Hi Alex,

                          thanks for the responce .in that bug they have given only recreate the issue no solution is given.

                          Many Thanks,
                          • 10. Re: clob is not working for bulk data files in PL/SQL XML program
                            AlexAnd
                            >
                            thanks for the responce .in that bug they have given only recreate the issue no solution is given.
                            >

                            did you read
                            >
                            Bug 5204876 : DBMS_XMLPARSER FAILS READING FILES HAVING A LINE SIZE OF > 32K
                            Bug 5937802 : DBMS_XSLPROCESSOR.READ2CLOB FAILS IF DOC MORE THAN ABOUT 32K AND NO NEW LINES
                            Bug 5232410 : ON 10.2 XML PARSER FAILS PARSING LARGE XML FILES ORA-29280
                            >
                            ?
                            i think not all


                            did you try
                            >
                            also you can try to use dbms_lob.loadclobfromfile
                            >
                            ?