The UTL_FILE package in Oracle allows reading and writing of files. It contains procedures like FOPEN to open a file, PUT_LINE and GET_LINE to write and read from files, and FCLOSE to close files. Developers can use UTL_FILE to read and write data to text files, for example writing employee records from a database table to an external file.
The UTL_FILE package in Oracle allows reading and writing of files. It contains procedures like FOPEN to open a file, PUT_LINE and GET_LINE to write and read from files, and FCLOSE to close files. Developers can use UTL_FILE to read and write data to text files, for example writing employee records from a database table to an external file.
The UTL_FILE package in Oracle allows reading and writing of files. It contains procedures like FOPEN to open a file, PUT_LINE and GET_LINE to write and read from files, and FCLOSE to close files. Developers can use UTL_FILE to read and write data to text files, for example writing employee records from a database table to an external file.
The UTL_FILE package in Oracle allows reading and writing of files. It contains procedures like FOPEN to open a file, PUT_LINE and GET_LINE to write and read from files, and FCLOSE to close files. Developers can use UTL_FILE to read and write data to text files, for example writing employee records from a database table to an external file.
4.GET_LINE: IT IS A PROCEDURE USED TO READ DATA FROM FILE.
SYNTAX: UTL_FILE.GET_LINE (<FILE VARIABLE NAME>, <STRING VARIABLE>); 5.FCLOSE: IT IS PROCEDURE USED TO CLOSE FILE.
SYNTAX: UTL_FILE.FCLOSE(<FILE VARIABLE NAME>);
EX: SQL> CONN SYSTEM/MANAGER; CONNECTED. SQL> GRANT CREATE ANY DIRECTORY TO SCOTT; GRANT SUCCEEDED.
SQL> CONN SCOTT/TIGER;
CONNECTED. SQL> CREATE DIRECTORY XYZ AS 'E:\SUDHAKAR'; DIRECTORY CREATED.
SQL> CONN SYSTEM/MANAGER;
CONNECTED. SQL> GRANT READ, WRITE ON DIRECTORY XYZ TO SCOTT; GRANT SUCCEEDED.
EX1: PROGRAM TO WRITE DATA INTO TEXT FILE?
SQL> DECLARE FV UTL_FILE.FILE_TYPE; BEGIN FV: =UTL_FILE.FOPEN('XYZ','FILE1.TXT','W'); UTL_FILE.PUT_LINE(FV,'HELLO'); UTL_FILE.FCLOSE(FV); END; / EX2: PROGRAM TO READ DATA FROM TEXT FILE? SQL>DECLARE FV UTL_FILE.FILE_TYPE; S VARCHAR2(1000); BEGIN FV: =UTL_FILE.FOPEN('XYZ','FILE1.TXT','R'); LOOP UTL_FILE.GET_LINE (FV, S); DBMS_OUTPUT.PUT_LINE(S); END LOOP; EXCEPTION WHEN NO_DATA_FOUND THEN UTL_FILE.FCLOSE(FV); END; / OUTPUT: HELLO
EX3: PROGRAM TO WRITE EMPLOYEE DATA INTO TEXT FILE?
DECLARE F1 UTL_FILE.FILE_TYPE; TYPE EMP_ARRAY IS TABLE OF EMP%ROWTYPE; E EMP_ARRAY; BEGIN F1: =UTL_FILE.FOPEN('XYZ','EMP.TXT','W'); SELECT * BULK COLLECT INTO E FROM EMP; FOR I IN E. FIRST..E.LAST LOOP UTL_FILE.PUT_LINE (F1, E(I). EMPNO||','||E(I). ENAME||','||E(I). SAL); END LOOP; UTL_FILE.FCLOSE(F1); END; /
EX4: PROGRAM TO READ EMPLOYEE DATA FROM TEXT FILE:
DECLARE F1 UTL_FILE.FILE_TYPE; S VARCHAR2(1000); BEGIN F1: =UTL_FILE.FOPEN('XYZ','EMP.TXT','R'); LOOP UTL_FILE.GET_LINE (F1, S); DBMS_OUTPUT.PUT_LINE(S); END LOOP; EXCEPTION WHEN NO_DATA_FOUND THEN UTL_FILE.FCLOSE(F1); END; /
NOTE: TO VIEW DIRECTORY RELATED TO PHYSICAL PATH THEN USE THE FOLLOWING DATADICTIONARY IS "ALL_DIRECTORIES ".
