Posted by : Akshay Patil
Monday, 11 May 2015
- UTL_FILE is an oracle pl/sql package that is supplied to allow PL/SQL to read and create text files in the file system.
- UTL_FILE can only read and create text files. Specifically, it cannot be used to read or create binary files.
- UTL_FILE is an appropriate tool for creating reports in the form of flat file from the database. UTL_FILE is also used for reading files.
The following steps must be followed in order to run UTL_FILE package.
Step 1) Connect as sys database user
Step 2) Create a directory:
SQL> CREATE OR REPLACE DIRECTORY utl_file_dir AS ‘E:\PLSQL’;Directory created.
In the above query utl_file_dir is the logical name for the path ‘E:\PLSQL’.
- We can mention the logical name utl_file_dir inside the program in uppercase within single quotes (utl_file_dir is mapped to the directory ‘E:\PLSQL’)
- We can create any number of logical path names(DBA directories) in oracle 10g.
- A DBA user needs to grant you access to read from/ write to a specific directory before using this package. Here is an example:
CONNECT / AS SYSDBA
GRANT read, write ON DIRECTORY utl_file_dir TO scott;
- Provide user access to the UTL_FILE package (created by catproc.sql):
GRANT EXECUTE ON UTL_FILE TO scott;Consider following example that performs read and write operations on file from client machine to server machine:
Note: In Client Server File operations please make your source and destination folder shared to avoid issues while directory creation.
CREATE OR REPLACE DIRECTORY server_dir AS '\\NINANI-D01\joss_upload';
GRANT READ, WRITE ON DIRECTORY server_dir TO scott;
CREATE OR REPLACE DIRECTORY client_dir AS '\\APATIL-D01\joss_upload';
GRANT READ, WRITE ON DIRECTORY client_dir TO scott;
DECLARE
V1 VARCHAR2(32767);
F_READ UTL_FILE.FILE_TYPE;
F_WRITE UTL_FILE.FILE_TYPE;
v_count number:=0;
BEGIN
F_READ := UTL_FILE.FOPEN('CLIENT_DIR','Extract.csv','R');
F_WRITE := UTL_FILE.FOPEN('SERVER_DIR', 'Extract.csv', 'W');
BEGIN
LOOP
UTL_FILE.GET_LINE(F_READ,V1);
UTL_FILE.PUT_LINE(F_WRITE, V1);
v_count:=v_count+1;
END LOOP;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('End of file..');
END;
UTL_FILE.FCLOSE(F_read);
UTL_FILE.FCLOSE(F_WRITE);
DBMS_OUTPUT.PUT_LINE(V_COUNT||' Records Inserted');
EXCEPTION
WHEN UTL_FILE.INVALID_PATH THEN
raise_application_error(-20000, 'ERROR: Invalid PATH FOR file.');
END ;