Utl File Query 4ypz

UTL File UTL file is mainly used for writing and reading files in the database. Here with this document I have given the basic example for reading and writing files using UTL. 1. Writing File: For the purpose of writing we are using the below procedure

DECLARE CURSOR c1 IS SELECT po_header_id, segment1, vendor_id, authorization_status FROM po_headers_all WHERE ROWNUM < = 5; lc_file_handle UTL_FILE.file_type; lc_file_dir VARCHAR2 (100); lc_file_name VARCHAR2 (50); gov_005_payment_rec VARCHAR2 (100); BEGIN lc_file_dir := '/usr/tmp'; lc_file_name := 'SCB_ePAY_OUT_' || TO_CHAR (SYSDATE, 'MMDDYYYY') || '.txt'; lc_file_handle := UTL_FILE.fopen (lc_file_dir, lc_file_name, 'W'); FOR i IN c1 LOOP gov_005_payment_rec := i.po_header_id || ',' || i.segment1 || ',' || i.vendor_id || ',' || i.authorization_status; UTL_FILE.put_line (lc_file_handle, gov_005_payment_rec); END LOOP; EXCEPTION WHEN OTHERS THEN UTL_FILE.fclose (lc_file_handle); END; In the above procedure I am trying to write po_header_id and po number in the database. Hence for that we need 1. In which path we have to write the file(Eg:'/usr/tmp') 2. The file name(Eg: 'SCB_ePAY_OUT_' || TO_CHAR (SYSDATE, 'MMDDYYYY')|| '.txt') Before writing the file, we have to open the file with our name in the database. For that purpose we are using UTL_FILE.fopen procedure. Now the file will be created in that path. We have to note „W‟ as a parameter to write the file. Then data should be printed inside the file, hence for that purpose we are using UTL_FILE.put_line procedure to print the data inside the file.

2.Reading File Here I am trying to read the file from the database which I had written above. Hence for that purpose we are using the below procedure.

CREATE TABLE apps.utl_table ( v1 VARCHAR2(50 BYTE), v2 VARCHAR2(50 BYTE), v3 VARCHAR2(50 BYTE), v4 VARCHAR2(50 BYTE) );

DECLARE lc_file_handle UTL_FILE.file_type; lc_file_dir VARCHAR2 (100); lc_file_name VARCHAR2 (50); gov_005_payment_rec VARCHAR2 (100); v1 VARCHAR2 (50); v2 VARCHAR2 (50); v3 VARCHAR2 (50); v4 VARCHAR2 (50); BEGIN lc_file_dir := '/usr/tmp'; lc_file_name := 'SCB_ePAY_OUT_' || TO_CHAR (SYSDATE, 'MMDDYYYY') || '.txt'; lc_file_handle := UTL_FILE.fopen (lc_file_dir, lc_file_name, 'R'); LOOP BEGIN UTL_FILE.get_line (lc_file_handle, gov_005_payment_rec); v1 := SUBSTR (gov_005_payment_rec, 1, INSTR (gov_005_payment_rec, ',', 1) - 1 ); v2 := SUBSTR (gov_005_payment_rec, INSTR (gov_005_payment_rec, ',', 1, 1) + 1, INSTR (gov_005_payment_rec, ',', 1, 2) - INSTR (gov_005_payment_rec, ',', 1, 1) -1 ); v3 := SUBSTR (gov_005_payment_rec, INSTR (gov_005_payment_rec, ',', 1, 2) + 1, INSTR (gov_005_payment_rec, ',', 2, 3) - INSTR (gov_005_payment_rec, ',', 1, 2) -1 ); v4 := SUBSTR (gov_005_payment_rec, INSTR (gov_005_payment_rec, ',', 2, 3) + 1 ); INSERT INTO utl_table VALUES (v1, v2, v3, v4); COMMIT; DBMS_OUTPUT.put_line (gov_005_payment_rec); EXCEPTION

WHEN NO_DATA_FOUND THEN EXIT; END; END LOOP; EXCEPTION WHEN OTHERS THEN UTL_FILE.fclose (lc_file_handle); END; Declaring variables, asg path, asg filename all are same as writing the file. Only changes is instead of using „W‟ in UTL_FILE.fopen procedure ,we have to use „R‟ for reading purpose. And also Instead of using UTL_FILE.put_line we have to use UTL_FILE.get_line. After running the script check the data table “utl_table”. Data will be inserted in this table. Hence above are the basic examples of reading and writing the files using UTL.