This discussion is archived
9 Replies Latest reply: Dec 13, 2012 2:02 AM by AlbertoFaenza RSS

UTL_FILE reqeust taking too much time for completion

977941 Newbie
Currently Being Moderated
Hi,

We are running utl_file reqeust.
the select statement fetching 6000 records, for this it taking 22 mins for completion.

the code is
-----------------


CREATE OR REPLACE PACKAGE BODY "XXC"."XXC_MOD_IN_068_AP_TO_FIS_PKG"
AS
/*******************************************************************************
* Module Type : PL/SQL
* Module Name : XXC_MOD_IN_068_AP_FIS_PKG
* Description : This package is used for AP to Fiscal Interface.
* Run Env. : SQL*Plus
* Procedure Name Description
* XXC_MOD_068_AP_PR XXC_MOD_068_AP_PR Procedure is used to insert transactions
* into CSV OutPut File from Oracle Account Payables.
* Calling Module: None
* Modules Called: XXC_COMMON_INT_PK.INSERT_AUDIT
* Module Number : MOD_IN_068

* Known Bugs and Restrictions: none
* History
* =======
*
* Version Name Date Description of Change
* ------- ------------- ----------- -------------------------------------------
* 0.1 Sanjeev Khurana 25-JULY-2011 Initial Creation.
* 0.2 Rohit 09-DEC-2011 Updated header details for the file
* 0.3 Amit Kulwal 28-AUG-2012 Updated the cursor query for incident 671520
* 0.4 Swaraj Goud 20-Nov-2012 Updated as per the CR 671520
***************************************************************************************
*/

/************************
| PACKAGE BODY
************************/
--------------------------------------------------------------------------------
-- Actual Code Start Here
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
-- Procedure : XXC_MOD_068_AP_PR
-- Description : XXC_MOD_068_AP_PR Procedure is used to insert transactions
-- into CSV OUTPUT File from Oracle Account Payables.
-- Parameters:
-- Parm Name I/O Description
-- ----------------- ---- ----------------------------------------------------
-- p_errbuf OUT Error message.
-- p_retcode OUT Error code. Returns 0 if no errors otherwise returns 1.
-- p_start_date IN Start Date
-- p_end_date IN End Date
--------------------------------------------------------------------------------
PROCEDURE xxc_mod_068_ap_pr (
p_errbuf OUT VARCHAR2,
p_retcode OUT NUMBER,
p_start_date IN VARCHAR2,
p_end_date IN VARCHAR2
)
IS
-- Define variables and assign default values
l_sucess_count NUMBER := 0;
l_error_count NUMBER := 0;
-- Standard declaration
l_source VARCHAR2 (10);
l_target VARCHAR2 (10);
lc_module_description VARCHAR2 (50)
:= 'MOD_IN_068 - AP to Fiscal';
l_status CONSTANT VARCHAR2 (50) := 'NEW';
p_status NUMBER;
l_batch_id NUMBER;
l_batch_id_next NUMBER
:= apps_common_out_batch_id_s1.NEXTVAL;
l_proc_name VARCHAR2 (100) := 'XXC_MOD_IN_068';
l_request_id NUMBER
:= fnd_global.conc_request_id;
l_audit_master_id NUMBER := NULL;
l_mod_code VARCHAR2 (100);
l_log_type NUMBER := 1; --INFORMATION
l_det_status_success NUMBER := 0; --SUCCESS
l_det_status_inprocess NUMBER := 3; --INPROCESS
l_det_status_rejected NUMBER := 4; --REJECTED
l_det_status_err NUMBER := 3; --Error
l_det_status_complete NUMBER := 9; --COMPLETE
-- Standard who Columns
l_created_by NUMBER := fnd_global.user_id;
l_creation_date DATE := SYSDATE;
l_last_update_date DATE := SYSDATE;
l_last_update_login NUMBER := fnd_global.user_id;
v_file UTL_FILE.file_type;
l_location VARCHAR2 (150);
l_archive_location VARCHAR2 (150);
l_date VARCHAR2 (50);
l_filename VARCHAR2 (50);
l_open_mode VARCHAR2 (1) := 'W';
--- l_max_linesize NUMBER := 32767;
l_max_linesize VARCHAR2 (150); -- Updated 09-Nov-2012
     


--Cursor is used to fetch valid records for the interface
CURSOR get_ap_data_inv
IS
SELECT asp.segment1 supplier_ref,
-- asp.vendor_name supplier_name,
replace(asp.vendor_name, ',', ' ') supplier_name,
--aia.invoice_num invoice_number,
replace(aia.invoice_num, ',','') invoice_number,
aia.invoice_date,
aia.invoice_amount amount,
aia.doc_sequence_value unique_id,
aia.creation_date date_invoice_entered,
apsa.due_date date_invoice_paid,
aia.SOURCE user_id,
aia.payment_status_flag,
aia.invoice_type_lookup_code doc_type,
--aia.description,
replace(aia.description, ',' , ' ') description,
apsa.gross_amount
FROM ap_invoices_all aia,
ap_suppliers asp,
apps.ap_payment_schedules_all apsa
-- apps.iby_payments_all iba,
-- apps.iby_docs_payable_all dp
WHERE aia.invoice_id = apsa.invoice_id
AND aia.vendor_id = asp.vendor_id
AND aia.org_id = apsa.org_id
-- AND apsa.payment_status_flag != 'Y' -- commented for CR
-- AND dp.payment_id = iba.payment_id(+)
-- AND aia.invoice_id = dp.calling_app_doc_unique_ref2(+)
-- AND apsa.due_date <= (SYSDATE + 1)
AND TRUNC (aia.creation_date)
BETWEEN NVL (fnd_date.canonical_to_date (p_start_date),
TRUNC (aia.creation_date))
AND NVL (fnd_date.canonical_to_date (p_end_date),
TRUNC (aia.creation_date));
     TYPE xxc_tbl IS TABLE OF get_ap_data_inv%ROWTYPE;
xxc_tbl1 xxc_tbl;

BEGIN
l_batch_id := apps_common_out_batch_id_s1.CURRVAL;
xxc_common_int_pk.insert_audit (p_batch_id => l_batch_id,
p_request_id => l_request_id,
p_source_system => l_source,
p_proc_name => l_proc_name,
p_log_type => l_log_type,
p_det_status => l_det_status_inprocess,
p_msg_code => NULL,
p_entity => NULL,
p_msg_desc => 'Process Starts',
p_mast_request_id => l_request_id,
p_record_id => NULL,
p_source => l_source,
p_target => l_target,
p_email => NULL,
p_mod_code => l_mod_code,
p_audit_master_id => l_audit_master_id
);

-- Get Module Code
BEGIN
SELECT TRIM (fval.flex_value),
TRIM (SUBSTR (fval.description,
1,
INSTR (fval.description, ' -')
))
INTO l_source,
l_mod_code
FROM fnd_flex_values_vl fval, fnd_flex_value_sets vset
WHERE vset.flex_value_set_id = fval.flex_value_set_id
AND vset.flex_value_set_name IN ('XXC_COMM_INT_CONFIG')
AND fval.enabled_flag = 'Y'
AND fval.description = lc_module_description;
EXCEPTION
WHEN OTHERS
THEN
xxc_common_int_pk.insert_audit (p_batch_id => l_batch_id,
p_request_id => l_request_id,
p_source_system => l_source,
p_proc_name => l_proc_name,
p_log_type => l_log_type,
p_det_status => l_det_status_err,
p_msg_code => NULL,
p_entity => NULL,
p_msg_desc => 'Error Mode Code',
p_mast_request_id => l_request_id,
p_record_id => NULL,
p_source => l_source,
p_target => l_target,
p_email => NULL,
p_mod_code => l_mod_code,
p_audit_master_id => l_audit_master_id
);
raise_application_error (-20045, SQLERRM);
END;

--File Location Path for OutPut File
BEGIN
SELECT fnd_profile.VALUE ('XXC_MOD_IN_068_AP_OUTBOUND'),
fnd_profile.VALUE ('XXC_MOD_IN_068_AP_ARCHIVE')
INTO l_location,
l_archive_location
FROM DUAL;
EXCEPTION
WHEN OTHERS
THEN
xxc_common_int_pk.insert_audit
(p_batch_id => l_batch_id,
p_request_id => l_request_id,
p_source_system => l_source,
p_proc_name => l_proc_name,
p_log_type => l_log_type,
p_det_status => l_det_status_rejected,
p_msg_code => NULL,
p_entity => NULL,
p_msg_desc => 'Profile Value not found',
p_mast_request_id => l_request_id,
p_record_id => NULL,
p_source => l_source,
p_target => l_target,
p_email => NULL,
p_mod_code => l_mod_code,
p_audit_master_id => l_audit_master_id
);
END;

BEGIN
SELECT TO_CHAR (SYSDATE, 'YYYYMMDDhh24miss')
INTO l_date
FROM DUAL;
EXCEPTION
WHEN OTHERS
THEN
xxc_common_int_pk.insert_audit (p_batch_id => l_batch_id,
p_request_id => l_request_id,
p_source_system => l_source,
p_proc_name => l_proc_name,
p_log_type => l_log_type,
p_det_status => l_det_status_rejected,
p_msg_code => NULL,
p_entity => NULL,
p_msg_desc => 'status not found',
p_mast_request_id => l_request_id,
p_record_id => NULL,
p_source => l_source,
p_target => l_target,
p_email => NULL,
p_mod_code => l_mod_code,
p_audit_master_id => l_audit_master_id
);
END;

l_filename := 'AP_Fiscal_' || l_date || '.csv';
v_file :=
UTL_FILE.fopen (LOCATION => l_location,
filename => l_filename,
open_mode => l_open_mode,
max_linesize => l_max_linesize
);
-- Changed as per Sarah's email on 9th Decemeber
/* UTL_FILE.put_line (v_file,
'SUPPLIER_REF'
|| ','
|| 'SUPPLIER_NAME'
|| ','
|| 'INVOICE_NUMBER'
|| ','
|| 'INVOICE_DATE'
|| ','
|| 'AMOUNT'
|| ','
|| 'UNIQUE_ID'
|| ','
|| 'DATE_INVOICE_ENTERED'
|| ','
|| 'DATE_INVOICE_PAID'
|| ','
|| 'USER_ID'
|| ','
|| 'PAYMENT_STATUS_FLAG'
|| ','
|| 'DOC_TYPE'
|| ','
|| 'DESCRIPTION'
|| ','
|| 'PAYMENT_AMOUNT'
);*/
UTL_FILE.put_line (v_file,
'SUPPLIERREF'
|| ','
|| 'SUPPLIERNAME'
|| ','
|| 'INVOICENUMBER'
|| ','
|| 'DATE'
|| ','
|| 'AMOUNT'
|| ','
|| 'UNIQUEID'
|| ','
|| 'DATEINVOICEENTERED'
|| ','
|| 'DATEINVOICEPAID'
|| ','
|| 'USERID'
|| ','
|| 'PAYMENTSTATUS'
|| ','
|| 'DOCTYPE'
|| ','
|| 'DESCRIPTION'
|| ','
|| 'PAYMENTAMOUNT');
UTL_FILE.put_line (v_file,
'XX'
|| ','
|| 'XX'
|| ','
|| 'XX'
|| ','
|| 'XX'
|| ','
|| 'XX'
|| ','
|| 'XX'
|| ','
|| 'XX'
|| ','
|| 'XX'
|| ','
|| 'XX'
|| ','
|| 'XX'
|| ','
|| 'XX'
|| ','
|| 'XX'
|| ','
|| 'XX');
                         
                         open get_ap_data_inv;
                         loop
                         fetch get_ap_data_inv bulk collect into xxc_tbl1 limit 6000;
fnd_file.put_line(fnd_file.log, 'Cursor Count is : '||xxc_tbl1.count);
                         for i in xxc_tbl1.first .. xxc_tbl1.count

--FOR cur_rec IN get_ap_data_inv
LOOP
BEGIN

--Common package used for proper sequence for Record_id and Bath_id
l_sucess_count := l_sucess_count + 1;
--Insert into CSV file
fnd_file.put_line (fnd_file.LOG, 'Before Utl file');
UTL_FILE.put_line (v_file,
xxc_tbl1(i).supplier_ref
|| ','
|| xxc_tbl1(i).supplier_name
|| ','
|| xxc_tbl1(i).invoice_number
|| ','
|| xxc_tbl1(i).invoice_date
|| ','
|| xxc_tbl1(i).amount
|| ','
|| xxc_tbl1(i).unique_id
|| ','
|| xxc_tbl1(i).date_invoice_entered
|| ','
|| xxc_tbl1(i).date_invoice_paid
|| ','
|| xxc_tbl1(i).user_id
|| ','
|| xxc_tbl1(i).payment_status_flag
|| ','
|| xxc_tbl1(i).doc_type
|| ','
|| xxc_tbl1(i).description
|| ','
|| xxc_tbl1(i).gross_amount);
fnd_file.put_line (fnd_file.LOG,
'Supplier Reference : ' || xxc_tbl1(i).supplier_ref);
xxc_common_int_pk.insert_audit
(p_batch_id => l_batch_id,
p_request_id => l_request_id,
p_source_system => l_source,
p_proc_name => l_proc_name,
p_log_type => l_log_type,
p_det_status => l_det_status_complete,
p_msg_code => NULL,
p_entity => NULL,
p_msg_desc => 'Inserting records from AP to Fiscal Successfully',
p_mast_request_id => l_request_id,
p_record_id => NULL,
p_source => l_source,
p_target => l_target,
p_email => NULL,
p_mod_code => l_mod_code,
p_audit_master_id => l_audit_master_id
);
EXCEPTION
WHEN OTHERS
THEN
l_error_count := l_error_count + 1;
fnd_file.put_line (fnd_file.LOG,
'Error While Inserting from AP to Fiscal '
|| SQLERRM);
-- Create audit log for AP Inv records insert Exception
--Insert into the Audit table XXC_COMM_AUDIT_DETAIL_LOG and XXC_COMM_AUDIT_MASTER_LOG
xxc_common_int_pk.insert_audit
(p_batch_id => l_batch_id,
p_request_id => l_request_id,
p_source_system => l_source,
p_proc_name => l_proc_name,
p_log_type => l_log_type,
p_det_status => l_det_status_rejected,
p_msg_code => NULL,
p_entity => NULL,
p_msg_desc => 'Error While Inserting from AP to Fiscal',
p_mast_request_id => l_request_id,
p_record_id => NULL,
p_source => l_source,
p_target => l_target,
p_email => NULL,
p_mod_code => l_mod_code,
p_audit_master_id => l_audit_master_id
);

END;
     
END LOOP;
exit when get_ap_data_inv%NOTFOUND;
end loop;
     close get_ap_data_inv;

UTL_FILE.fclose (v_file);
UTL_FILE.fcopy (l_location,
l_filename,
l_archive_location,
l_filename
);
-- Create audit log for Successfully processed records
-- Procedure call to insert in Audit tables
xxc_common_int_pk.insert_audit
(p_batch_id => l_batch_id,
p_request_id => l_request_id,
p_source_system => l_source,
p_proc_name => l_proc_name,
p_log_type => l_log_type,
p_det_status => l_det_status_complete,
p_msg_code => NULL,
p_entity => NULL,
p_msg_desc => 'Compeleted Sucessfully AP to Fiscal',
p_mast_request_id => l_request_id,
p_record_id => NULL,
p_source => l_source,
p_target => l_target,
p_email => NULL,
p_mod_code => l_mod_code,
p_audit_master_id => l_audit_master_id
);

--Insert into the Audit table XXC_COMM_AUDIT_DETAIL_LOG and XXC_COMM_AUDIT_MASTER_LOG for populating email drop table
BEGIN
SELECT transaction_status
INTO p_status
FROM xxc_comm_audit_master_log
WHERE audit_master_id = l_audit_master_id;
EXCEPTION
WHEN OTHERS
THEN
xxc_common_int_pk.insert_audit (p_batch_id => l_batch_id,
p_request_id => l_request_id,
p_source_system => l_source,
p_proc_name => l_proc_name,
p_log_type => l_log_type,
p_det_status => l_det_status_err,
p_msg_code => NULL,
p_entity => NULL,
p_msg_desc => 'Status Error',
p_mast_request_id => l_request_id,
p_record_id => NULL,
p_source => l_source,
p_target => l_target,
p_email => NULL,
p_mod_code => l_mod_code,
p_audit_master_id => l_audit_master_id
);
END;

IF p_status <> 0
THEN
xxc_comm_audit_log_pk.populate_email_drop_table (l_audit_master_id,
l_batch_id);
END IF;
EXCEPTION
WHEN UTL_FILE.invalid_path
THEN
UTL_FILE.fclose (v_file);
raise_application_error (-20000, 'File location is invalid.');
WHEN UTL_FILE.invalid_mode
THEN
UTL_FILE.fclose (v_file);
raise_application_error (-20001,
'The open_mode parameter in FOPEN is invalid.');
WHEN UTL_FILE.invalid_filehandle
THEN
UTL_FILE.fclose (v_file);
raise_application_error (-20002, 'File handle is invalid.');
WHEN UTL_FILE.invalid_operation
THEN
UTL_FILE.fclose (v_file);
raise_application_error
(-20003,
'File could not be opened or operated on as requested.');
WHEN UTL_FILE.write_error
THEN
UTL_FILE.fclose (v_file);
raise_application_error
(-20005,
'Operating system error occurred during the write operation.');
WHEN UTL_FILE.file_open
THEN
UTL_FILE.fclose (v_file);
raise_application_error
(-20008,
'The requested operation failed because the file is open.');
WHEN UTL_FILE.invalid_maxlinesize
THEN
UTL_FILE.fclose (v_file);
raise_application_error
(-20009,
'The MAX_LINESIZE value for FOPEN() is invalid; it should '
|| 'be within the range 1 to 32767.');
COMMIT;
ROLLBACK TO data_extract;
WHEN OTHERS
THEN
raise_application_error (-20045, SQLERRM);
UTL_FILE.fclose (v_file);
END xxc_mod_068_ap_pr;
END xxc_mod_in_068_ap_to_fis_pkg;

/
Show Errors

Iam implemented BULK collect concept in programe,can anyone please suggest how can I imporve performance....

Thanks,
Rakesh
  • 1. Re: UTL_FILE reqeust taking too much time for completion
    Justin Cave Oracle ACE
    Currently Being Moderated
    1) Please use the \
     tag before and after code you post to preserve the white space.  That makes the code much easier to read.
    
    2) Why do you believe that the problem is with the UTL_FILE calls?  Have you traced/ profiled/ debugged the code?  How do you know that the problem isn't that the query you're running takes 22 minutes to return 6000 rows because of a query plan that is less than optimal?
    
    Justin                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
  • 2. Re: UTL_FILE reqeust taking too much time for completion
    977941 Newbie
    Currently Being Moderated
    Hi,

    Thanks for reply...

    the select statment fetching the data in Toad as 3 sec.

    But when submitting the reqquest it will taking 22 min.

    Can you please suggest where I can wrong....please

    Thanks,
    Rakesh
  • 3. Re: UTL_FILE reqeust taking too much time for completion
    AlbertoFaenza Expert
    Currently Being Moderated
    974938 wrote:
    Hi,

    Thanks for reply...

    the select statment fetching the data in Toad as 3 sec.

    But when submitting the reqquest it will taking 22 min.

    Can you please suggest where I can wrong....please

    Thanks,
    Rakesh
    Edit your original message and put your code between two lines starting with {noformat}
    {noformat}
    
    i.e.:
    {noformat}
    {noformat}
    SELECT ...
    {noformat}
    {noformat}
    
    
    Also please read <a href="https://forums.oracle.com/forums/thread.jspa?threadID=2174552#9360002">How do I ask a question on the forums?</a>
    
    You need to post some sample data to be able reproduce your problem.
    
    Regards.
    Al                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
  • 4. Re: UTL_FILE reqeust taking too much time for completion
    977941 Newbie
    Currently Being Moderated
      CREATE OR REPLACE PACKAGE BODY "XXC"."XXC_MOD_IN_068_AP_TO_FIS_PKG" 
    AS
      PROCEDURE xxc_mod_068_ap_pr (
        p_errbuf                         OUT      VARCHAR2,
        p_retcode                        OUT      NUMBER,
        p_start_date                     IN       VARCHAR2,
        p_end_date                       IN       VARCHAR2
      )
      IS
        -- Define variables and assign default values
        l_sucess_count                          NUMBER := 0;
        l_error_count                           NUMBER := 0;
        -- Standard declaration
        l_source                                VARCHAR2 (10);
        l_target                                VARCHAR2 (10);
        lc_module_description                   VARCHAR2 (50)
                                                     := 'MOD_IN_068 - AP to Fiscal';
        l_status                       CONSTANT VARCHAR2 (50) := 'NEW';
        p_status                                NUMBER;
        l_batch_id                              NUMBER;
        l_batch_id_next                         NUMBER
                                             := apps_common_out_batch_id_s1.NEXTVAL;
        l_proc_name                             VARCHAR2 (100) := 'XXC_MOD_IN_068';
        l_request_id                            NUMBER
                                                      := fnd_global.conc_request_id;
        l_audit_master_id                       NUMBER := NULL;
        l_mod_code                              VARCHAR2 (100);
        l_log_type                              NUMBER := 1;   --INFORMATION
        l_det_status_success                    NUMBER := 0;   --SUCCESS
        l_det_status_inprocess                  NUMBER := 3;   --INPROCESS
        l_det_status_rejected                   NUMBER := 4;   --REJECTED
        l_det_status_err                        NUMBER := 3;   --Error
        l_det_status_complete                   NUMBER := 9;   --COMPLETE
        -- Standard who Columns
        l_created_by                            NUMBER := fnd_global.user_id;
        l_creation_date                         DATE := SYSDATE;
        l_last_update_date                      DATE := SYSDATE;
        l_last_update_login                     NUMBER := fnd_global.user_id;
        v_file                                  UTL_FILE.file_type;
        l_location                              VARCHAR2 (150);
        l_archive_location                      VARCHAR2 (150);
        l_date                                  VARCHAR2 (50);
        l_filename                              VARCHAR2 (50);
        l_open_mode                             VARCHAR2 (1) := 'W';
    --- l_max_linesize                          NUMBER := 32767;
        l_max_linesize                          VARCHAR2 (150); -- Updated 09-Nov-2012
         
    
    
        --Cursor is used to fetch valid records for the interface
        CURSOR get_ap_data_inv
        IS
          SELECT       asp.segment1 supplier_ref,
                 -- asp.vendor_name supplier_name,
                 replace(asp.vendor_name, ',', ' ') supplier_name,
                 --aia.invoice_num invoice_number,
                 replace(aia.invoice_num, ',','') invoice_number,
                 aia.invoice_date,
                 aia.invoice_amount amount,
                 aia.doc_sequence_value unique_id,
                 aia.creation_date date_invoice_entered,
                 apsa.due_date date_invoice_paid,
                 aia.SOURCE user_id,
                 aia.payment_status_flag,
                 aia.invoice_type_lookup_code doc_type,
                 --aia.description,
                 replace(aia.description, ',' , ' ') description,
                 apsa.gross_amount
          FROM   ap_invoices_all aia,
                 ap_suppliers asp,
                 apps.ap_payment_schedules_all apsa
                -- apps.iby_payments_all iba,
            --     apps.iby_docs_payable_all dp
          WHERE  aia.invoice_id = apsa.invoice_id
          AND    aia.vendor_id  = asp.vendor_id
          AND    aia.org_id     = apsa.org_id
      --  AND    apsa.payment_status_flag != 'Y'   -- commented for CR
      --  AND    dp.payment_id = iba.payment_id(+)
      --  AND    aia.invoice_id = dp.calling_app_doc_unique_ref2(+)
      --  AND    apsa.due_date <= (SYSDATE + 1)
          AND    TRUNC (aia.creation_date)
                   BETWEEN NVL (fnd_date.canonical_to_date (p_start_date),
                                TRUNC (aia.creation_date))
                       AND NVL (fnd_date.canonical_to_date (p_end_date),
                                TRUNC (aia.creation_date));
                                     TYPE xxc_tbl IS TABLE OF get_ap_data_inv%ROWTYPE;
                                  xxc_tbl1 xxc_tbl;
                                 
      BEGIN
        l_batch_id                      := apps_common_out_batch_id_s1.CURRVAL;
        xxc_common_int_pk.insert_audit (p_batch_id                        => l_batch_id,
                                        p_request_id                      => l_request_id,
                                        p_source_system                   => l_source,
                                        p_proc_name                       => l_proc_name,
                                        p_log_type                        => l_log_type,
                                        p_det_status                      => l_det_status_inprocess,
                                        p_msg_code                        => NULL,
                                        p_entity                          => NULL,
                                        p_msg_desc                        => 'Process Starts',
                                        p_mast_request_id                 => l_request_id,
                                        p_record_id                       => NULL,
                                        p_source                          => l_source,
                                        p_target                          => l_target,
                                        p_email                           => NULL,
                                        p_mod_code                        => l_mod_code,
                                        p_audit_master_id                 => l_audit_master_id
                                       );
    
    -- Get Module Code
        BEGIN
          SELECT TRIM (fval.flex_value),
                 TRIM (SUBSTR (fval.description,
                               1,
                               INSTR (fval.description, ' -')
                              ))
          INTO   l_source,
                 l_mod_code
          FROM   fnd_flex_values_vl fval, fnd_flex_value_sets vset
          WHERE  vset.flex_value_set_id = fval.flex_value_set_id
          AND    vset.flex_value_set_name IN ('XXC_COMM_INT_CONFIG')
          AND    fval.enabled_flag = 'Y'
          AND    fval.description = lc_module_description;
        EXCEPTION
          WHEN OTHERS
          THEN
            xxc_common_int_pk.insert_audit (p_batch_id                        => l_batch_id,
                                            p_request_id                      => l_request_id,
                                            p_source_system                   => l_source,
                                            p_proc_name                       => l_proc_name,
                                            p_log_type                        => l_log_type,
                                            p_det_status                      => l_det_status_err,
                                            p_msg_code                        => NULL,
                                            p_entity                          => NULL,
                                            p_msg_desc                        => 'Error Mode Code',
                                            p_mast_request_id                 => l_request_id,
                                            p_record_id                       => NULL,
                                            p_source                          => l_source,
                                            p_target                          => l_target,
                                            p_email                           => NULL,
                                            p_mod_code                        => l_mod_code,
                                            p_audit_master_id                 => l_audit_master_id
                                           );
            raise_application_error (-20045, SQLERRM);
        END;
    
    --File Location Path for OutPut File
        BEGIN
          SELECT fnd_profile.VALUE ('XXC_MOD_IN_068_AP_OUTBOUND'),
                 fnd_profile.VALUE ('XXC_MOD_IN_068_AP_ARCHIVE')
          INTO   l_location,
                 l_archive_location
          FROM   DUAL;
        EXCEPTION
          WHEN OTHERS
          THEN
            xxc_common_int_pk.insert_audit
                                          (p_batch_id                        => l_batch_id,
                                           p_request_id                      => l_request_id,
                                           p_source_system                   => l_source,
                                           p_proc_name                       => l_proc_name,
                                           p_log_type                        => l_log_type,
                                           p_det_status                      => l_det_status_rejected,
                                           p_msg_code                        => NULL,
                                           p_entity                          => NULL,
                                           p_msg_desc                        => 'Profile Value not found',
                                           p_mast_request_id                 => l_request_id,
                                           p_record_id                       => NULL,
                                           p_source                          => l_source,
                                           p_target                          => l_target,
                                           p_email                           => NULL,
                                           p_mod_code                        => l_mod_code,
                                           p_audit_master_id                 => l_audit_master_id
                                          );
        END;
    
        BEGIN
          SELECT TO_CHAR (SYSDATE, 'YYYYMMDDhh24miss')
          INTO   l_date
          FROM   DUAL;
        EXCEPTION
          WHEN OTHERS
          THEN
            xxc_common_int_pk.insert_audit (p_batch_id                        => l_batch_id,
                                            p_request_id                      => l_request_id,
                                            p_source_system                   => l_source,
                                            p_proc_name                       => l_proc_name,
                                            p_log_type                        => l_log_type,
                                            p_det_status                      => l_det_status_rejected,
                                            p_msg_code                        => NULL,
                                            p_entity                          => NULL,
                                            p_msg_desc                        => 'status not found',
                                            p_mast_request_id                 => l_request_id,
                                            p_record_id                       => NULL,
                                            p_source                          => l_source,
                                            p_target                          => l_target,
                                            p_email                           => NULL,
                                            p_mod_code                        => l_mod_code,
                                            p_audit_master_id                 => l_audit_master_id
                                           );
        END;
    
        l_filename                      := 'AP_Fiscal_' || l_date || '.csv';
        v_file                          :=
          UTL_FILE.fopen (LOCATION                          => l_location,
                          filename                          => l_filename,
                          open_mode                         => l_open_mode,
                          max_linesize                      => l_max_linesize
                         );
                           -- Changed as per Sarah's email on 9th Decemeber
        /* UTL_FILE.put_line (v_file,
                               'SUPPLIER_REF'
                            || ','
                            || 'SUPPLIER_NAME'
                            || ','
                            || 'INVOICE_NUMBER'
                            || ','
                            || 'INVOICE_DATE'
                            || ','
                            || 'AMOUNT'
                            || ','
                            || 'UNIQUE_ID'
                            || ','
                            || 'DATE_INVOICE_ENTERED'
                            || ','
                            || 'DATE_INVOICE_PAID'
                            || ','
                            || 'USER_ID'
                            || ','
                            || 'PAYMENT_STATUS_FLAG'
                            || ','
                            || 'DOC_TYPE'
                            || ','
                            || 'DESCRIPTION'
                            || ','
                            || 'PAYMENT_AMOUNT'
                           );*/
        UTL_FILE.put_line (v_file,
                              'SUPPLIERREF'
                           || ','
                           || 'SUPPLIERNAME'
                           || ','
                           || 'INVOICENUMBER'
                           || ','
                           || 'DATE'
                           || ','
                           || 'AMOUNT'
                           || ','
                           || 'UNIQUEID'
                           || ','
                           || 'DATEINVOICEENTERED'
                           || ','
                           || 'DATEINVOICEPAID'
                           || ','
                           || 'USERID'
                           || ','
                           || 'PAYMENTSTATUS'
                           || ','
                           || 'DOCTYPE'
                           || ','
                           || 'DESCRIPTION'
                           || ','
                           || 'PAYMENTAMOUNT');
        UTL_FILE.put_line (v_file,
                              'XX'
                           || ','
                           || 'XX'
                           || ','
                           || 'XX'
                           || ','
                           || 'XX'
                           || ','
                           || 'XX'
                           || ','
                           || 'XX'
                           || ','
                           || 'XX'
                           || ','
                           || 'XX'
                           || ','
                           || 'XX'
                           || ','
                           || 'XX'
                           || ','
                           || 'XX'
                           || ','
                           || 'XX'
                           || ','
                           || 'XX');
                                
                                open get_ap_data_inv;
                                loop
                                fetch get_ap_data_inv bulk collect into xxc_tbl1 limit 6000;
                 fnd_file.put_line(fnd_file.log, 'Cursor Count is : '||xxc_tbl1.count);
                                for i in xxc_tbl1.first .. xxc_tbl1.count
    
        --FOR cur_rec IN get_ap_data_inv
        LOOP
          BEGIN
          
            --Common package used for proper sequence for Record_id and Bath_id
            l_sucess_count                  := l_sucess_count + 1;
            --Insert into CSV file
            fnd_file.put_line (fnd_file.LOG, 'Before Utl file');
            UTL_FILE.put_line (v_file,
                                  xxc_tbl1(i).supplier_ref
                               || ','
                               || xxc_tbl1(i).supplier_name
                               || ','
                               || xxc_tbl1(i).invoice_number
                               || ','
                               || xxc_tbl1(i).invoice_date
                               || ','
                               || xxc_tbl1(i).amount
                               || ','
                               || xxc_tbl1(i).unique_id
                               || ','
                               ||  xxc_tbl1(i).date_invoice_entered
                               || ','
                               ||  xxc_tbl1(i).date_invoice_paid
                               || ','
                               ||  xxc_tbl1(i).user_id
                               || ','
                               ||  xxc_tbl1(i).payment_status_flag
                               || ','
                               ||  xxc_tbl1(i).doc_type
                               || ','
                               ||  xxc_tbl1(i).description
                               || ','
                               ||  xxc_tbl1(i).gross_amount);
            fnd_file.put_line (fnd_file.LOG,
                               'Supplier Reference : ' ||  xxc_tbl1(i).supplier_ref);
            xxc_common_int_pk.insert_audit
                  (p_batch_id                        => l_batch_id,
                   p_request_id                      => l_request_id,
                   p_source_system                   => l_source,
                   p_proc_name                       => l_proc_name,
                   p_log_type                        => l_log_type,
                   p_det_status                      => l_det_status_complete,
                   p_msg_code                        => NULL,
                   p_entity                          => NULL,
                   p_msg_desc                        => 'Inserting records from AP to Fiscal Successfully',
                   p_mast_request_id                 => l_request_id,
                   p_record_id                       => NULL,
                   p_source                          => l_source,
                   p_target                          => l_target,
                   p_email                           => NULL,
                   p_mod_code                        => l_mod_code,
                   p_audit_master_id                 => l_audit_master_id
                  );
          EXCEPTION
            WHEN OTHERS
            THEN
              l_error_count                   := l_error_count + 1;
              fnd_file.put_line (fnd_file.LOG,
                                    'Error While Inserting from AP to Fiscal '
                                 || SQLERRM);
                 -- Create audit log for AP Inv records insert Exception
              --Insert into the Audit table XXC_COMM_AUDIT_DETAIL_LOG and XXC_COMM_AUDIT_MASTER_LOG
              xxc_common_int_pk.insert_audit
                           (p_batch_id                        => l_batch_id,
                            p_request_id                      => l_request_id,
                            p_source_system                   => l_source,
                            p_proc_name                       => l_proc_name,
                            p_log_type                        => l_log_type,
                            p_det_status                      => l_det_status_rejected,
                            p_msg_code                        => NULL,
                            p_entity                          => NULL,
                            p_msg_desc                        => 'Error While Inserting from AP to Fiscal',
                            p_mast_request_id                 => l_request_id,
                            p_record_id                       => NULL,
                            p_source                          => l_source,
                            p_target                          => l_target,
                            p_email                           => NULL,
                            p_mod_code                        => l_mod_code,
                            p_audit_master_id                 => l_audit_master_id
                           );
                        
          END;
           
        END LOOP;
         exit when get_ap_data_inv%NOTFOUND;
        end loop;
         close get_ap_data_inv;
    
        UTL_FILE.fclose (v_file);
        UTL_FILE.fcopy (l_location,
                        l_filename,
                        l_archive_location,
                        l_filename
                       );
          -- Create audit log for Successfully processed records
        -- Procedure call to insert in Audit tables
        xxc_common_int_pk.insert_audit
                               (p_batch_id                        => l_batch_id,
                                p_request_id                      => l_request_id,
                                p_source_system                   => l_source,
                                p_proc_name                       => l_proc_name,
                                p_log_type                        => l_log_type,
                                p_det_status                      => l_det_status_complete,
                                p_msg_code                        => NULL,
                                p_entity                          => NULL,
                                p_msg_desc                        => 'Compeleted Sucessfully AP to Fiscal',
                                p_mast_request_id                 => l_request_id,
                                p_record_id                       => NULL,
                                p_source                          => l_source,
                                p_target                          => l_target,
                                p_email                           => NULL,
                                p_mod_code                        => l_mod_code,
                                p_audit_master_id                 => l_audit_master_id
                               );
    
        --Insert into the Audit table XXC_COMM_AUDIT_DETAIL_LOG and XXC_COMM_AUDIT_MASTER_LOG for populating email drop table
        BEGIN
          SELECT transaction_status
          INTO   p_status
          FROM   xxc_comm_audit_master_log
          WHERE  audit_master_id = l_audit_master_id;
        EXCEPTION
          WHEN OTHERS
          THEN
            xxc_common_int_pk.insert_audit (p_batch_id                        => l_batch_id,
                                            p_request_id                      => l_request_id,
                                            p_source_system                   => l_source,
                                            p_proc_name                       => l_proc_name,
                                            p_log_type                        => l_log_type,
                                            p_det_status                      => l_det_status_err,
                                            p_msg_code                        => NULL,
                                            p_entity                          => NULL,
                                            p_msg_desc                        => 'Status Error',
                                            p_mast_request_id                 => l_request_id,
                                            p_record_id                       => NULL,
                                            p_source                          => l_source,
                                            p_target                          => l_target,
                                            p_email                           => NULL,
                                            p_mod_code                        => l_mod_code,
                                            p_audit_master_id                 => l_audit_master_id
                                           );
        END;
    
        IF p_status <> 0
        THEN
          xxc_comm_audit_log_pk.populate_email_drop_table (l_audit_master_id,
                                                           l_batch_id);
        END IF;
      EXCEPTION
        WHEN UTL_FILE.invalid_path
        THEN
          UTL_FILE.fclose (v_file);
          raise_application_error (-20000, 'File location is invalid.');
        WHEN UTL_FILE.invalid_mode
        THEN
          UTL_FILE.fclose (v_file);
          raise_application_error (-20001,
                                   'The open_mode parameter in FOPEN is invalid.');
        WHEN UTL_FILE.invalid_filehandle
        THEN
          UTL_FILE.fclose (v_file);
          raise_application_error (-20002, 'File handle is invalid.');
        WHEN UTL_FILE.invalid_operation
        THEN
          UTL_FILE.fclose (v_file);
          raise_application_error
                           (-20003,
                            'File could not be opened or operated on as requested.');
        WHEN UTL_FILE.write_error
        THEN
          UTL_FILE.fclose (v_file);
          raise_application_error
                     (-20005,
                      'Operating system error occurred during the write operation.');
        WHEN UTL_FILE.file_open
        THEN
          UTL_FILE.fclose (v_file);
          raise_application_error
                        (-20008,
                         'The requested operation failed because the file is open.');
        WHEN UTL_FILE.invalid_maxlinesize
        THEN
          UTL_FILE.fclose (v_file);
          raise_application_error
                    (-20009,
                        'The MAX_LINESIZE value for FOPEN() is invalid; it should '
                     || 'be within the range 1 to 32767.');
          COMMIT;
          ROLLBACK TO data_extract;
        WHEN OTHERS
        THEN
          raise_application_error (-20045, SQLERRM);
          UTL_FILE.fclose (v_file);
      END xxc_mod_068_ap_pr;
    END xxc_mod_in_068_ap_to_fis_pkg;
    Please verify...
  • 5. Re: UTL_FILE reqeust taking too much time for completion
    BluShadow Guru Moderator
    Currently Being Moderated
    974938 wrote:
    Hi,

    Thanks for reply...

    the select statment fetching the data in Toad as 3 sec.
    Are you sure that was all 6000 rows?
    TOAD only fetches the first 500 rows and displays those, and then fetches the next 500 when you scroll down the data grid etc. and it does that so you can get data back quicker than if it were to fetch all the data back.
    How long does it take in TOAD if you scroll down to the end of all 6000 rows of data?
    But when submitting the reqquest it will taking 22 min.

    Can you please suggest where I can wrong....please
    The UTL_FILE package is a low level file access which can read and write files on the operating system. It's about as fast as fast can be, so your idea that it is UTL_FILE that is causing the issue is undoubtably wrong. The delay is almost certainly elsewhere in your code, and most likely in the query that is fetching the data.

    Try running your query along through SQL*Plus and see how long it takes for it to fetch back and show all the 6000 rows.
  • 6. Re: UTL_FILE reqeust taking too much time for completion
    977941 Newbie
    Currently Being Moderated
    Hardly sqlstatement takking 10 sec to fetch data for 6000 rows and the explain plan as below:


    SQL> SELECT PLAN_TABLE_OUTPUT
    FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, 'ex_plan2','BASIC'));
    2
    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------
    --------------------------------------------------------
    | Id | Operation | Name |
    --------------------------------------------------------
    | 0 | SELECT STATEMENT | |
    | 1 | HASH JOIN | |
    | 2 | HASH JOIN | |
    | 3 | TABLE ACCESS FULL| AP_INVOICES_ALL |
    | 4 | TABLE ACCESS FULL| AP_SUPPLIERS |
    | 5 | TABLE ACCESS FULL | AP_PAYMENT_SCHEDULES_ALL |
    --------------------------------------------------------
    11 rows selected.

    please suggest..
  • 7. Re: UTL_FILE reqeust taking too much time for completion
    AlbertoFaenza Expert
    Currently Being Moderated
    Hi,

    your code is too long and we don't have your input data.

    To be sure that is UTL_FILE the cause you should be able to trace your code and see exactly that UTL_FILE is causing the problem.

    In your code I don't see only UTL_FILE calls but other statements as well.

    Consider that the following code:
    SQL> set timing ON
    SQL> DECLARE
      2     v_fl      UTL_FILE.FILE_TYPE;
      3  BEGIN
      4     v_fl := UTL_FILE.FOPEN('DATA_TMP','test.txt','w');
      5     FOR c1 IN (SELECT * FROM all_objects)
      6     LOOP
      7        UTL_FILE.PUT_LINE(v_fl,
      8                          c1.object_name
      9                          ||c1.subobject_name
     10                          ||c1.object_type
     11                          ||c1.timestamp
     12                          ||c1.status
     13                          ||c1.temporary
     14                          ||c1.generated
     15                          ||c1.secondary
     16                          ||c1.edition_name);
     17     END LOOP;
     18
     19     UTL_FILE.FCLOSE(v_fl);
     20  END;
     21  /
    
    PL/SQL procedure successfully completed.
    
    Elapsed: 00:00:07.81
    SQL> set timing off
    SQL>
    on my simple laptop writes 71000 rows in about 7 secs.

    Regards.
    Al
  • 8. Re: UTL_FILE reqeust taking too much time for completion
    977941 Newbie
    Currently Being Moderated
    SQL> SELECT PLAN_TABLE_OUTPUT
    FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, 'ex_plan2','BASIC'));
    2
    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------
    --------------------------------------------------------
    | Id | Operation | Name |
    --------------------------------------------------------
    | 0 | SELECT STATEMENT | |
    | 1 | HASH JOIN | |
    | 2 | HASH JOIN | |
    | 3 | TABLE ACCESS FULL| AP_INVOICES_ALL |
    | 4 | TABLE ACCESS FULL| AP_SUPPLIERS |
    | 5 | TABLE ACCESS FULL | AP_PAYMENT_SCHEDULES_ALL |
    --------------------------------------------------------
    11 rows selected.
  • 9. Re: UTL_FILE reqeust taking too much time for completion
    AlbertoFaenza Expert
    Currently Being Moderated
    Hi,

    If you believe that is UTL_FILE and you don't want to trace your proble, make a test commenting out the UTL_FILE.put line in this way:
    /*
            UTL_FILE.put_line (v_file,
                                  xxc_tbl1(i).supplier_ref
                               || ','
                               || xxc_tbl1(i).supplier_name
                               || ','
                               || xxc_tbl1(i).invoice_number
                               || ','
                               || xxc_tbl1(i).invoice_date
                               || ','
                               || xxc_tbl1(i).amount
                               || ','
                               || xxc_tbl1(i).unique_id
                               || ','
                               ||  xxc_tbl1(i).date_invoice_entered
                               || ','
                               ||  xxc_tbl1(i).date_invoice_paid
                               || ','
                               ||  xxc_tbl1(i).user_id
                               || ','
                               ||  xxc_tbl1(i).payment_status_flag
                               || ','
                               ||  xxc_tbl1(i).doc_type
                               || ','
                               ||  xxc_tbl1(i).description
                               || ','
                               ||  xxc_tbl1(i).gross_amount);
    */
    This will definitely show to you that your procedure is slow even without using UTL_FILE.

    Regards.
    Al

Legend

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