1) Reading or writing data to physical files is required in reporting or transferring to legacy systems.
2) A pre-defined package called "UTL_FILE" is used for the purpose. UTL_FILE" allows PL/SQL programs to both read and write to any operating system files that are accessible from the server on which your database instance is running. This package gets automatically installed when you install Oracle database on your system.
3) Now the very first thing you would be requiring is to set the value for UTL_FILE_DIR parameter in init.ora file.
The path for the init.ora file is <instance_home>\pfile.
In my case it is:- E:\oracle\admin\ORCL\pfile
4) Now, open the file and insert the line UTL_FILE_DIR=<directory_name>. In my case it is UTL_FILE_DIR=c:\harsh. This line indicates that the harsh folder in c drive is available for reading and writing the files inside it. Permissions to any folder from host operating system can be provided by modifying the content as shown below:
E.g. UTL_FILE_DIR = *
5) Bounce the database services to reflect the changes.
6) Create a new text file inside the directory you set above. In my case it is test.txt.
7) Create a variable of utl_file.file_type .
e.g:- f utl_file.file_type
"utl_file.file_type" is a different data type which points to a particular file specified by the user. On successfully identification, operations like reading, writing can be performed.
8) Now, Open the file test.txt as shown below:-
f := UTL_FILE.FOPEN('c:\harsh','test.txt','R');
The file is opened in Read mode.
9) You can also do the Exception Handling for error tracking.
10) Code for reading single line from the file.
DECLARE f UTL_FILE.FILE_TYPE; s VARCHAR2(200); BEGIN f := UTL_FILE.FOPEN('c:\harsh','test.txt','R'); UTL_FILE.GET_LINE(f,s); UTL_FILE.FCLOSE(f); DBMS_OUTPUT.PUT_LINE(s); END;
DECLARE f UTL_FILE.FILE_TYPE; s VARCHAR2(200); c number:=0; BEGIN f := UTL_FILE.FOPEN('c:\harsh','test.txt','R'); LOOP UTL_FILE.GET_LINE(f,s); DBMS_OUTPUT.PUT_LINE(s); c:=c+1; END LOOP; EXCEPTION WHEN NO_DATA_FOUND THEN UTL_FILE.FCLOSE(f); DBMS_OUTPUT.PUT_LINE('Number of lines: ' || c); UTL_FILE.FCLOSE(f); END;
set serveroutput on before executing the above PL/SQL block.
[P.S]:- steps 3 to 5 will save you from the error :- ORA-06510: PL/SQL: unhandled user-defined exception.