--Step1: Create Procedure--
CREATE OR REPLACE PROCEDURE INV_DATA (errbuf OUT VARCHAR2,
Retcode OUT VARCHAR2)
AS
CURSOR cur_inv
IS
SELECT inventory_item_id,
organization_id,
segment1,
description
FROM mtl_system_items_b
WHERE ROWNUM < 100;
Outpointer UTL_FILE.file_type;
BEGIN
-- To get the path:
--Select value from
V$parameter where name like '%utl_file_dir'
Outpointer :=
UTL_FILE.fopen (
'/u02/maxprod/db/tech_st/11.2.0/appsutil/outbound/ERPBKP_erpbkp',
'LOKANADHAM_INVENTORY_DATA.txt',
'W');
FOR c IN cur_inv
LOOP
UTL_FILE.put_line (
outpointer,
c.inventory_item_id
|| ','
|| c.organization_id
|| ','
|| c.segment1
|| ','
|| c.description);
END LOOP;
UTL_FILE.fclose (Outpointer);
EXCEPTION
WHEN UTL_FILE.invalid_mode
THEN
raise_application_error (-20051, 'Invalid Mode Parameter');
WHEN UTL_FILE.invalid_path
THEN
raise_application_error (-20052, 'Invalid File Location');
WHEN UTL_FILE.invalid_filehandle
THEN
raise_application_error (-20053, 'Invalid Filehandle');
WHEN UTL_FILE.invalid_operation
THEN
raise_application_error (-20054, 'Invalid Operation');
WHEN UTL_FILE.write_error
THEN
raise_application_error (-20055, 'Write Error');
WHEN UTL_FILE.internal_error
THEN
raise_application_error (-20057, 'Internal Error');
WHEN UTL_FILE.charsetmismatch
THEN
raise_application_error (
-20058,
'Opened With
FOPEN_NCHAR But Later I/O Inconsistent');
WHEN UTL_FILE.file_open
THEN
raise_application_error (-20059, 'File Already Opened');
WHEN UTL_FILE.invalid_maxlinesize
THEN
raise_application_error (-20060, 'Line Size Exceeds 32K');
WHEN UTL_FILE.invalid_filename
THEN
raise_application_error (-20061, 'Invalid File Name');
WHEN UTL_FILE.access_denied
THEN
raise_application_error (-20062, 'File Access Denied By');
WHEN UTL_FILE.invalid_offset
THEN
raise_application_error (-20063, 'FSEEK Param Less Than 0');
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG, 'Unknown UTL_FILE Error');
raise_application_error (-20099, 'Unknown UTL_FILE Error' || SQLERRM);
END INV_DATA;
--Step2 : Create Concurrent Program and Attach it to Request Group
--Step 3: Open the Server --> go to path
--Step 4: Open Output File