Oracle PLSQL Script - How to Generate File and Place it on Oracle File Server Using

 DECLARE

  -- Declare file handler
  file_handle UTL_FILE.FILE_TYPE;

  -- Declare variables for data to be written
  v_line VARCHAR2(100);
  v_filename VARCHAR2(50) := 'handlingunits'||TO_CHAR(SYSDATE,'MMDDRRHH24MISS')||'.csv';  -- Name of the file
  v_dir VARCHAR2(50) := 'XX_CAO_OUTBOUND';  -- Directory path (ensure it's accessible by Oracle)

 

BEGIN
  -- Open the file for writing. If the file does not exist, it will be created.
  file_handle := UTL_FILE.FOPEN(v_dir, v_filename, 'w');  -- 'w' mode means write

  -- Example data to write to the file
  FOR xxtlm_rec IN (SELECT DISTINCT ';;;'||item||';'||vendor_lot||';1;1;1;' line
                    FROM xxtlm.xxtlm_rcv_transactions xrt
                    WHERE xrt.organization_id = (SELECT organization_id FROM mtl_parameters WHERE organization_code = 'TIF')
                        AND xrt.status_id = 2
                        AND transaction_type = 'RECEIVE'
                    UNION
                    SELECT '99999999;99999999;99999999;99999999;99999999;99999999;99999999;99999999;99999999;99999999'
                    FROM dual
                    ORDER BY 1 DESC) 
    LOOP    
    -- Write the data to the file
    UTL_FILE.PUT_LINE(file_handle, xxtlm_rec.line);
  END LOOP;

  -- Close the file after writing
  UTL_FILE.FCLOSE(file_handle);

 

  -- Success message
  DBMS_OUTPUT.PUT_LINE('Data written successfully to ' || v_dir || '/' || v_filename);

 

EXCEPTION
  WHEN OTHERS THEN
    -- Handle any exceptions
    DBMS_OUTPUT.PUT_LINE('Exception: '||SQLERRM);
    IF UTL_FILE.IS_OPEN(file_handle) THEN
      UTL_FILE.FCLOSE(file_handle);
    END IF;
    DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;
/