Oracle read-write file Method __oracle

This describes the 9i and above version of the situation, the environment for the WINDOWS2003 Oracle 10G R2.
Using initialization parameter Utl_file_dir to set and resolve file reads and writes is a cliché and inflexible approach.
New is the use of Oracle Object directory to read and write, and to control permissions.
Here's an example:
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
Declare
--Create or replace directory Uce_dir as ' d:/ucedata/filedir/';
--GRANT Read/write/all on DIRECTORY uce_dir to TESTUSER;
Fileid Utl_file.file_type;
L_line VARCHAR2 (32767);
L_eof BOOLEAN;
BEGIN
---1) Test writing file
--W means Rewrite the file,a means append the file
Fileid: = Utl_file.fopen (' Uce_dir ', ' TUSER. TXT ', ' W ');
For Emprec in (SELECT rownum,rpad (userid,12, ") Userid,name UNAME from TUSER)
LOOP
L_line:=rpad (To_char (Emprec.rownum), 6, "") | | '||    emprec.userid| | ' '|| Emprec. UNAME;
Utl_file.putf (Fileid, '%s ', l_line); --like C language printf, here F means five stirng parameters
Utl_file.new_line (Fileid);
--this following row does the same as the two rows upon.
--utl_file.put_line (Fileid,l_line);
End LOOP;
Utl_file.fclose (Fileid);
--2) Test Reading file
Fileid: =utl_file.fopen (' Uce_dir ', ' TUSER. TXT ', ' R ');
Begin
LOOP
Utl_file.get_line (Fileid, l_line);
Dbms_output.put_line (L_line);
End LOOP;
exception
When No_data_found THEN
Utl_file.fclose (Fileid);
End
--3) Test with CLOB
End
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
One thing to be aware of is that it was written in GRANT for convenience. Refer to the other documentation for the correct format.
The results of the final output are as follows (only the parts are extracted):
－－－－－－－－－－－－－－－－－－－－－－－
7 000100000005 005 Crazy Stone
8 000100000006 006 Crazy Stone
9 000100000007 007 Crazy Stone
Ten 000100000001 001 Crazy Stone
One 000100000008 008 Crazy Stone
000100000009 009 Crazy Stone
－－－－－－－－－－－－－－－－－－－－－－－－
Precautions:
1 in the Windows operating system, the directory should end up with a backslash "/", at least 10g R2 and its previous, should be changed so, otherwise there will be the following error message:
ORA-29283: Invalid file operation
ORA-06512: In the SYS. Utl_file ", line 449
ORA-29283: Invalid file operation
Whether Oracle will be smarter on 11g or later (actually a very simple question) depends on Oracle's mood.
2 pay attention to your users on the operating system for a specific directory has the required access rights, or you will be prompted to error.
3 Read and write binary files, please use Put_raw or Get_raw,:) It's a matter of being taken for granted.

Another method mentioned in the article:

To realize the interaction between the two, you can use the Utl_file package to implement I/O to the file. The following is a description of the file write table and the table data write file.

[1] Table information exported to a file

Suggest a folder/home/zxin10/file on SuSE, then authorize its chmod g+w file (otherwise it cannot be exported to a file), and then the system table to Oracle for the path you specify (/home/zxin10/file) sys.dir$ To register (otherwise you will not be able to successfully export the information to the file), you can query sys.dir$ can see the table in the Os_path you specify the path location.

Registration mode: Execute SQL statement Create or replace directory BBB as '/home/zxin10/file '; Can

The stored procedure is as follows: (When writing a file, the file name can not be created first, the specified file will be created automatically in the program)

CREATE OR REPLACE PROCEDURE v3_sub_fetch_test_2
(
V_temp VARCHAR2,
--1 for success, 0 for failure
V_retvalue out number
)
As
--Cursor definition
Type ref_cursor_type is REF CURSOR;
Cursor_select Ref_cursor_type;
Select_cname VARCHAR2 (1000);

V_file_handle Utl_file.file_type;

V_sql VARCHAR2 (1000);
V_filepath VARCHAR2 (500);
V_filename VARCHAR2 (500);
--Buffer
V_results VARCHAR2 (500);

V_pid VARCHAR2 (1000);
V_cpcnshortname VARCHAR2 (500);

Begin
V_filepath: = v_temp;
If V_filepath is null then
V_filepath: = '/home/zxin10/file3 ';
End If;
v_filename:= ' Free_ ' | | substr (To_char (sysdate, ' Yyyymmddhh24mi '), 1,10) | | All ';
--Cursor start
select_cname:= ' Select Cpid,cpcnshortname from Zxdbm_ismp.scp_basic ';
--Opens a file handle, while the first argument of fopen must be uppercase
V_file_handle:=utl_file.fopen (' BBB ', V_filename, ' A ');
Open Cursor_select for Select_cname;
Fetch cursor_select into V_pid,v_cpcnshortname;
While Cursor_select%found
Loop
V_results: = v_pid| | ' | | V_cpcnshortname;
--Write v_results to file
Utl_file.put_line (V_file_handle,v_results);
Fetch cursor_select into V_pid,v_cpcnshortname;
End Loop;

Close Cursor_select; --Close cursor
Utl_file.fclose (V_file_handle); --Close handle
V_retvalue: = 1;
Exception when others then
V_retvalue: = 0;
End V3_sub_fetch_test_2;



[2] Importing file information into a table

As above, first chmod the specified file path and then sys.dir$ the path to Oracle.

The file Zte.apsuic is located under/home/zxin10/file with the data format:
1|22|cheng
2|33|zhou
3|44|heng
4|55|yaya


Table LoadData Script:

--Create table
CREATE TABLE LoadData
(
ID VARCHAR2 (50),
Age VARCHAR2 (50),
NAME VARCHAR2 (50)
)
Tablespace SYSTEM
Pctfree 10
Pctused 40
Initrans 1
Maxtrans 255
Storage
(
Initial 64K
Minextents 1
Maxextents Unlimited
);

The program is as follows: (when reading a file, the specified filename must exist beforehand, otherwise the program will fail)

Create or replace directory BBB as '/home/zxin10/file ';
/
--The action method is to register the specific file path information with Oracle (registration information is stored in the sys.dir$ table)

CREATE OR REPLACE PROCEDURE v3_sub_fetch_test_3
(
--The information in the file is imported into the table
V_temp VARCHAR2,
V_retvalue out number--1 succeeded, 0 failed
As
V_file_handle Utl_file.file_type;
V_sql VARCHAR2 (1000);
V_filepath VARCHAR2 (500);
V_filename VARCHAR2 (500);
--Mapping files to table fields
v_id VARCHAR2 (1000);
V_age VARCHAR2 (1000);
V_name VARCHAR2 (1000);
--Buffer
V_str VARCHAR2 (1000);
--Column pointers
V_i number;
--String positioning resolution pointer
V_sposition1 number;
V_sposition2 number;
Begin
V_filepath: = v_temp;
If V_filepath is null then
V_filepath: = '/home/zxin10/file ';
End If;
v_filename:= ' Zte.apsuic ';
--v_sql:= ' Create or replace directory CCC as ' | | V_filepath | | '''';
--execute immediate v_sql;

V_file_handle:=utl_file.fopen (' CCC ', V_filename, ' R ');
Loop
--Reads the file information into the buffer v_str, reads one row at a time
Utl_file.get_line (V_FILE_HANDLE,V_STR);
--dbms_output.put_line (V_STR);
--For the number of columns per row
V_i: = 1;
--The Move pointer for each string
V_sposition1: = 1;
--3 columns of information per line in a file, 3 times in circulation
For I in 1..3 loop
--When InStr (V_str, ' | ', 6) where V_str is 1|22|wuzhuocheng, it returns 0
V_sposition2: = InStr (V_str, ' | ', V_sposition1);
--String parsing normal
If V_sposition2 <> 0 Then
If V_i=1 Then
v_id: = substr (V_str, V_sposition1, V_sposition2-v_sposition1); --The first column
Elsif v_i=2 Then
V_age: = substr (V_str, V_sposition1, V_sposition2-v_sposition1); --The second column
Elsif V_i=3 Then
V_name: = substr (V_str, V_sposition1, V_sposition2-v_sposition1); --The third column
Else
Return
End If;
--String parsing exception case
Else
If V_i=1 Then
v_id: = substr (V_str, V_sposition1); --The first column
Elsif v_i=2 Then
V_age: = substr (V_str, V_sposition1); --The second column
Elsif V_i=3 Then
V_name: = substr (V_str, V_sposition1); --The third column
Else
Return
End If;
End If;
V_sposition1: = V_sposition2 + 1;
V_i: = v_i+1;
End Loop;
--Inserts the information into the table after each column has been recycled
INSERT into Zxdbm_ismp.loaddata values (v_id,v_age,v_name);
End Loop;
--Close handle
Utl_file.fclose (V_file_handle);
V_retvalue: = 1;
Exception when others then
V_retvalue: = 0;
End V3_sub_fetch_test_3;