Utl_file Package Read-write file Operation example Learning in Oracle

In Oracle, the Utl_file package provides a number of functions and procedures for manipulating text files, learning about his basic operations 1. Create a directory and give the user authorization to copy the Code code as follows:--Creating directory Create or Replace Directory testfile as '/home/oracle/zxx/test '; --Authorize grant read to user, write on directory testfile to Zxx; Detailed introduction http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/u_file.htm 2. Write operation copy code code as follows:---test write DECLARE FileHandle Utl_file.file_type; --Handle begin FileHandle: = Utl_file.fopen (' testfile ', ' hello.txt ', ' W '); --Open File Utl_file.put_line (filehandle, ' Hello oracle! '); --Write a row of records Utl_file.put_line (FileHandle, ' Hello world! '); Utl_file.put_line (FileHandle, ' Hello, Fatso! ‘); Utl_file.fclose (FileHandle);--close handle end; Note: fopen has a parameter max_linesize, the following is the original explanation Maximum number of characters for every line, including the newline character, for this F Ile (minimum value 1, maximum value 32767). If unspecified, Oracle supplies a default value of 1024. 3. The read operation copy code code is as follows:--Test read set serveroutput on; DECLARE FileHandle Utl_file.file_type; Filebuffer VARCHAR2 (500); Begin FileHandle: = Utl_file.fopen (' testfile ', ' hello.tXT ', ' R '); IF Utl_file.is_open (FileHandle) then Dbms_output.put_line (' file is open! '); END IF; Loop begin Utl_file.get_line (Filehandle,filebuffer); Dbms_output.put_line (Filebuffer); EXCEPTION when No_data_found then exit; When OTHERS then Dbms_output.put_line (' EXCEPTION1: ' | | SUBSTR (SQLERRM, 1, 100)); End End Loop; Utl_file.fclose (FileHandle); IF Utl_file.is_open (FileHandle) then Dbms_output.put_line (' file is open! '); else Dbms_output.put_line (' file is close! '); END IF; Utl_file.fcopy (' testfile ', ' hello.txt ', ' testfile ', ' hello.dat ');--Copy Utl_file.fcopy (' Testfile ', ' hello.txt ', ' Testfile ', ' hello2.dat '); Utl_file.fcopy (' testfile ', ' hello.txt ', ' testfile ', ' Hello.xls '); Utl_file.frename (' testfile ', ' Hello.xls ', ' testfile ', ' Frenamehello.xls ', TRUE);--Rename Utl_file.fremove (' Testfile ', ' Hello2.dat ');--delete file EXCEPTION when OTHERS then Dbms_output.put_line (' EXCEPTION2: ' | | SUBSTR (SQLERRM, 1, 100)); End 4. Determine if the file exists (read, rename, copy, delete to determine whether the file exists) copy the code as follows:--Determine if the file exists DECLARE ex boolean;--file exists Flen number;--File length? This place does not know how to do (the original file_length the length of the file in bytes. NULL If file does not exist.) bsize number;--File size BEGIN utl_file.fgetattr (' testfile ', ' Hello.txt ', ex, Flen, bsize); IF ex then Dbms_output.put_line (' File Exists '); ELSE dbms_output.put_line (' File Does not Exist '); END IF; Dbms_output.put_line (' File Length: ' | | To_char (Flen)); Dbms_output.put_line (' Block Size: ' | | To_char (bsize));  END fgetattr;

Utl_file Package Read-write file Operation example Learning in Oracle