This discussion is archived
10 Replies Latest reply: Aug 6, 2012 1:53 AM by AlexAnd RSS

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

953171 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    >
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    >
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    >
    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
    >
    ?

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points