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;
/