Read and Write files in Oracle stored procedures

Reading and Writing files in Oracle stored procedures is an important means to achieve interaction between files and database tables. The following describes the knowledge of reading and writing files in Oracle stored procedures in detail and hopes to help you.

Sometimes we need to use programs between files and database tables to achieve interaction between the two. Here we can use the UTL_FILE package to implement I/O operations on files. the following describes the file write tables and table data write files respectively.

[1] export table information to a file

We recommend that you create a folder/home/zxin10/file on SUSE, and then authorize its chmod g + w file (otherwise, the file cannot be exported ), then, the path (/home/zxin10/file) You specified is directed to the system table sys. dir $ is registered (otherwise, information cannot be exported to the file). After the operation, you can query sys. dir $ you can see that the OS _PATH In the table contains the path you specified.

Registration Method: Execute the SQL statement create or replace directory BBB as '/home/zxin10/file';

The storage process is as follows: (when writing a file, you do not need to create the file name first, and the specified file will be automatically created in the Program)

 
 

  
  
Create or replace procedure V3_SUB_FETCH_TEST_2
  
  
(
  
  
V_TEMP VARCHAR2,
  
  
-- 1 indicates success, and 0 indicates 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 zone
  
  
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'),) | '. all ';
  
  
-- Cursor start
  
  
Select_cname: = 'select cpid, cpcnshortname from zxdbm_ismp.scp_basic ';
  
  
-- Open a file handle, and the first parameter of fopen must be in 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 a file
  
  
Utl_file.put_line (v_file_handle, v_results );
  
  
Fetch cursor_select into v_pid, v_cpcnshortname;
  
  
End Loop;
  
  

  
  
Close cursor_select; -- Close the cursor
  
  
Utl_file.fClose (v_file_handle); -- close the handle
  
  
V_retvalue: = 1;
  
  
Exception when others then
  
  
V_retvalue: = 0;
  
  
End V3_SUB_FETCH_TEST_2;
 
 

[2] import file information to the table

As above, chmod is performed on the specified file path first, and then you want to register the path for sys. dir $ of Oracle.

The file zte. apsuic is located in/home/zxin10/file, and its data format is:
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)  
  
  
)  
  
  
    / 
 
 

The program is as follows: (when reading a file, the specified file name must exist in advance; otherwise, the program will fail)

 
 

  
  
Create or replace directory BBB as '/home/zxin10/file ';
  
  
/
  
  
-- The function is to register the specific file path information in Oracle (the registration information is stored in the sys. dir $ table)
  
  
 
  
  
Create or replace procedure V3_SUB_FETCH_TEST_3
  
  
(
  
  
-- Import the information in the file to the table
  
  
V_TEMP VARCHAR2,
  
  
V_retvalue out number -- 1 successful, 0 failed
  
  
AS
  
  
V_file_handle utl_file.file_type;
  
  
V_ SQL varchar2 (1000 );
  
  
V_filepath Varchar2 (500 );
  
  
V_filename Varchar2 (500 );
  
  
-- File-to-table field ing
  
  
V_id varchar2 (1000 );
  
  
V_age varchar2 (1000 );
  
  
V_name varchar2 (1000 );
  
  
-- Buffer zone
  
  
V_str varchar2 (1000 );
  
  
-- Column pointer
  
  
V_ I number;
  
  
-- String location parsing 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
  
  
-- Read the file information to the buffer v_str and read a row each time
  
  
Utl_file.get_line (v_file_handle, v_str );
  
  
-- Dbms_output.put_line (v_str );
  
  
-- For the number of columns in each row
  
  
V_ I: = 1;
  
  
-- Move the pointer to the string each time
  
  
V_sposition1: = 1;
  
  
-- There are three columns of information in each row in the file, which are repeated three times.
  
  
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 is normal
  
  
If v_sposition2 <> 0 then
  
  
If v_ I = 1 then
  
  
V_id: = substr (v_str, v_sposition1, v_sposition2-v_sposition1); -- First Column
  
  
Elsif v_ I = 2 then
  
  
V_age: = substr (v_str, v_sposition1, v_sposition2-v_sposition1); -- 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
  
  
Else
  
  
If v_ I = 1 then
  
  
V_id: = substr (v_str, v_sposition1); -- First Column
  
  
Elsif v_ I = 2 then
  
  
V_age: = substr (v_str, v_sposition1); -- 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;
  
  
-- Insert information into the table after each column is cyclically completed
  
  
Insert into zxdbm_ismp.loaddata values (v_id, v_age, v_name );
  
  
End Loop;
  
  
-- Close the handle
  
  
Utl_file.fClose (v_file_handle );
  
  
V_retvalue: = 1;
  
  
Exception when others then
  
  
V_retvalue: = 0;
  
  
End V3_SUB_FETCH_TEST_3;
 
 

Oracle read/write file bfilename instance

Oracle XE built-in database creation process

Entire Process of creating Oracle Materialized View

ORACLE instance creation process

Statement syntax for oracle time addition and subtraction