Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How to create csv file with column headings using UTL.

user263656Jan 19 2010 — edited Jan 19 2010
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;
/ 
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Feb 16 2010
Added on Jan 19 2010
4 comments
5,804 views