UTL_FILE Package

UTL_FILE Package

1.      WE have to create a directory

       Directory is an oracle object which points to Operating System Folder

Syntax for Creating a Directory

       Create DIRECTORY <DIR_NAME> as  ‘<Folder_Path>;create directory raghu_utl as 'D:\Raghu_utl_demos';

Query to get the directory information is –

Select Owner, Directory_Name,Directory_Path from all_directories;

2.      We have to create a File Handler

 Syntax for creating FileHandler –

<File_Handler_Name>  UTL_FILE.FILE_TYPE;

UTL_FILE à Is a package

FILE_TYPE

F1 UTL_FILE.FILE_TYPE;

3.      Open a file and associate the filehandler to that file.

            

            FOPEN is a Function which opens the file which is in OS Folder in a Specified Mode

<File_Handler>:= UTL_FILE.FOPEN(‘ORACLE_DIR’,’FILE_NAME.EXT’,’MODE’);

Modes à R(Read), W(Write) and A(Append)

F1 := UTL_FILE.FOPEN(‘RAGHU_UTL’,’Dept_ff.txt’,’W’);

4.      WE have to use the procedure to read and write into the files.

UTL_FILE.GET_LINE(<File_Handler>, <String_Variable>);

-          This is a procedure which is used To read data from files

UTL_FILE.PUT_LINE(<FILE_HANDLER>,<String_Variable);

-          This is a procedure which is used to write the data into the files.

5.      Close the FileHandler

            UTL_FILE.FCLOSE(<File_Handler>);

            UTL_FILE.FCLOSE_ALL;

           DB to Flat File

Create or replace procedure sp_Utl_DB_FF

Is

F1 UTL_FILE.FILE_TYPE;

Begin

F1 := UTL_FILE.FOPEN(’RAGHU_UTL’,’Dept_ff.txt’,’A’);

For i in (Select * from dept) loop

UTL_File.put_line(F1,i.Deptno||’,’||i.Dname||’,’||i.loc);

End loop;

UTL_FILE.FCLOSE(F1);

End;

           Flat File to Flat File

Create or Replace Procedure SP_UTL_DEPT_FF_FF

IS

F1 UTL_FILE.FILE_TYPE;

F2 UTL_FILE.FILE_TYPE;

V_STR varchar2(200);

Begin

F1:=UTL_FILE.FOPEN(’RAGHU_UTL’,’Dept_ff.txt’,’R’);

F2:=UTL_FILE.FOPEN(’RAGHU_UTL’,’Dept_ff_Copy.txt’,’A’);

LOOP

Begin

UTL_FILE.GET_LINE(F1,v_STR);

DBMS_OUTPUT.PUT_LINE(V_Str);

UTL_FILE.PUT_LINE(F2,v_Str);

Exception

When No_Data_Found then

Exit;

End;

End loop;

UTL_FILE.FCLOSE(F1);

UTL_FILE.FCLOSE(F2);

End;

          Flat File to Dept table

Create or replace procedure sp_UTl_FF_DB

Is

F1 UTL_FILE.FILE_TYPE;

V_Str varchar2(200);

V_DEPTNO NUMBER;

V_DNAME VARCHAR2(20);

V_LOC VARCHAR2(20);

Begin

F1 := UTL_FILE.FOPEN(’RAGHU_UTL’,’Dept_ff.txt’,’R’);

Loop

Begin

UTL_FILE.GET_LINE(F1,v_Str);

 --- ’10,sales,newyork’;

V_deptno := substr(v_Str,1,instr(v_Str,’,’,1)-1);

V_dname := substr(v_Str,instr(v_str,’,’,1,1)+1,instr(v_Str,’,’,1,2)-instr(v_Str,’,’,1,1)-1);

V_loc := substr(v_Str,instr(v_str,’,’,1,2)+1);

Insert into dept values(v_deptno,v_dname,v_loc);

Exception

When no_data_found then

Exit;

End;

End loop;

UTL_FILE.FCLOSE(F1);

End;