In oracle, The utl_file package provides some functions and procedures for operating text files. Next, let's learn about their basic operations. If you are interested, refer to the following. I hope this will help you.
In oracle, The utl_file package provides some functions and procedures for operating text files. Next, let's learn about their basic operations. If you are interested, refer to the following. I hope this will help you.
In oracle, The utl_file package provides some functions and procedures for operating text files, and learns their basic operations.
1. Create a directory and authorize the user
The Code is as follows:
-- Create directory
Create or replace directory TESTFILE as '/home/oracle/zxx/test ';
-- Authorize the user
Grant read, write on directory TESTFILE to zxx;
Details
Http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/u_file.htm
2. Write operations
The Code is as follows:
--- Test writing
DECLARE
Filehandle utl_file.file_type; -- handle
Begin
Filehandle: = utl_file.fopen('testfile', 'hello.txt ', 'w'); -- open the 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, fat man! ');
Utl_file.fclose (filehandle); -- close the handle
End;
Note:
Fopen has a parameter max_linesize, which is explained in the original article.
Maximum number of characters for each line, including the newline character, for this file (minimum value 1, maximum value 32767). If unspecified, Oracle supplies a default value of 1024.
3. Read operations
The 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 an object
EXCEPTION
WHEN OTHERS THEN
Dbms_output.put_line ('exception2: '| SUBSTR (SQLERRM, 1,100 ));
End;
4. Determine whether a file exists (read, rename, copy, or delete all files)
The Code is as follows:
-- Determine whether a file exists
DECLARE
Ex BOOLEAN; -- whether the file exists
Flen NUMBER; -- file length? I don't know how to solve this problem (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;
