Hello guys,
I wonder if you could help me. I have a UTL proc that writes into a csv file. It's working fine. But now I want to add column headings into my csv file. I want the csv file to be created with column headings, otherwise my user won't know what's what. The column headings will be exactly the same as the field names on my cursor. The column heading must be split over two rows. Example. If the column heading is TRANSACTION DATE, then TRANSACTION must be on row (A1) and DATE must be on row (A2) of the csv file. Below and the proc as well as the file example of what my user wants.
INVENTORY ORGANIZATION TRANSACTION
ITEM ID ID DATE
1892010 102 2009/10/19 14:02
1892013 102 2009/10/19 14:02
1892018 102 2009/10/19 14:02
1892020 102 2009/10/19 14:02
CREATE OR REPLACE PROCEDURE APPS.EMP_CSV AS
CURSOR c_data IS
select mmt.inventory_item_id,
mmt.organization_id,
mmt.transaction_date,
((mmt.transaction_quantity)*-1)/12 transaction_quantity,
mmt.subinventory_code,
mmt.transaction_type_id,
msi.segment1 item,
msi.description item_description
from mtl_material_transactions mmt,
mtl_system_items_b msi
where mmt.inventory_item_id = msi.inventory_item_id
and mmt.transaction_type_id in (54,62)
and msi.organization_id = 101
and mmt.transaction_date between add_months(SYSDATE, - 3) and SYSDATE;
v_file UTL_FILE.FILE_TYPE;
BEGIN
v_file := UTL_FILE.FOPEN('APPS_DATA_FILE_DIR','emp_csv.csv','w',32767);
FOR cur_rec IN c_data LOOP
UTL_FILE.PUT_LINE(v_file,
cur_rec.inventory_item_id || ',' ||
cur_rec.organization_id || ',' ||
cur_rec.transaction_date || ',' ||
cur_rec.transaction_quantity || ',' ||
cur_rec.subinventory_code || ',' ||
cur_rec.transaction_type_id || ',' ||
cur_rec.item || ',' ||
cur_rec.item_description);
END LOOP;
UTL_FILE.FCLOSE(v_file);
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.READ_ERROR THEN
UTL_FILE.FCLOSE(v_file);
RAISE_APPLICATION_ERROR(-20004, 'Operating system error occurred during the read operation.');
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.INTERNAL_ERROR THEN
UTL_FILE.FCLOSE(v_file);
RAISE_APPLICATION_ERROR(-20006, 'Unspecified PL/SQL error.');
WHEN UTL_FILE.CHARSETMISMATCH THEN
UTL_FILE.FCLOSE(v_file);
RAISE_APPLICATION_ERROR(-20007, 'A file is opened using FOPEN_NCHAR, but later I/O ' ||
'operations use nonchar functions such as PUTF or GET_LINE.');
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.');
WHEN UTL_FILE.INVALID_FILENAME THEN
UTL_FILE.FCLOSE(v_file);
RAISE_APPLICATION_ERROR(-20010, 'The filename parameter is invalid.');
WHEN UTL_FILE.ACCESS_DENIED THEN
UTL_FILE.FCLOSE(v_file);
RAISE_APPLICATION_ERROR(-20011, 'Permission to access to the file location is denied.');
WHEN UTL_FILE.INVALID_OFFSET THEN
UTL_FILE.FCLOSE(v_file);
RAISE_APPLICATION_ERROR(-20012, 'The ABSOLUTE_OFFSET parameter for FSEEK() is invalid; ' ||
'it should be greater than 0 and less than the total ' ||
'number of bytes in the file.');
WHEN UTL_FILE.DELETE_FAILED THEN
UTL_FILE.FCLOSE(v_file);
RAISE_APPLICATION_ERROR(-20013, 'The requested file delete operation failed.');
WHEN UTL_FILE.RENAME_FAILED THEN
UTL_FILE.FCLOSE(v_file);
RAISE_APPLICATION_ERROR(-20014, 'The requested file rename operation failed.');
WHEN OTHERS THEN
UTL_FILE.FCLOSE(v_file);
RAISE;
END;
/