Oracle Read-Write file--read and write to disk files using Utl_file package

Summary:

the user presents a requirement that a table in Oracle stores photo information, the field type is blob, and the request can be exported as a file form. I want to write a C # program to do, and later think of Oracle has a lot of packages, the function is very good very strong, so the online reference to some of the article completed. The main use of Oracle's two packages: Utl_file and Dbms_lob.

Implementation process:

The first step: With the Administrator user login settings can operate directory

 

--create directory privilege is granted only to SYS and SYSTEM by default.
Create or replace directory Blobdir as ' D:\PIC ';
Grant Read,write on directory Blobdir to Sharedb;
GRANT EXECUTE on Utl_file to Sharedb;
SELECT * from All_directories;

The second step: ordinary user login, write the stored procedure CREATE OR REPLACE PROCEDURE get_pic_blob (i_xh VARCHAR2) is L_file utl_file.
File_type;
L_buffer RAW (32767);
L_amount Binary_integer: = 32767;
L_pos INTEGER: = 1;
L_blob blob;

L_blob_len INTEGER;
 BEGIN SELECT PIC into L_blob from tb_zp WHERE psnno = i_xh; L_blob_len: = Dbms_lob.
 GetLength (L_BLOB); L_file: = Utl_file. FOPEN (' Blobdir ', I_XH | |
 '. jpg ', ' WB ', 32767); While L_pos<L_blob_lenLOOP Dbms_lob.
    READ (L_blob, L_amount, L_pos, L_buffer); Utl_file.
    Put_raw (L_file, L_buffer, TRUE); L_pos:= L_pos+ l_amount;
 END LOOP; Utl_file.

FCLOSE (L_file);
 EXCEPTION--when No_data_found then--dbms_output.put_line (' NO DATA: ' | | i_xh); When OTHERS and IF Utl_file.is_open (l_file) then utl_file.
  FCLOSE (L_file);
  RAISE;
END IF; END Get_pic_blob;

Step Three: Write a PL/SQL block and loop through the stored procedure

 

copy Code
declare 
        cursor cur_01 is<;

Begin for
    rec_01 in cur_01 loop        
        Get_pic_blob (REC_01.XH);
    End Loop;   
End;
Copy Code

The results of the test. 5,000 data were taken, including 3,407 of the photo information. Spents 1 minutes and 12 seconds, it feels good.

Summary:
1. It is important for an administrator to create an accessible directory and grant it to an ordinary user, initially without notice, always reporting an illegal path error that has been engaged for a longer time on this.

2. The no_data_found exception in the stored procedure is originally the screen display output no photo of the number information, but the actual operation of the error, because the Dbms_output.put_line print too many, so commented out.

The 3.Oracle itself provides a large number of packages, such as utl_http,dbms_output, that encapsulate different functions, and the possibility of extensive application development, thus extending the capabilities of Oracle.

Online reference:

CREATE OR REPLACE PROCEDURE p_write_emp as V_file utl_file.
File_type;
V_buffer VARCHAR2 (32767); BEGIN v_file: = Utl_file. FOPEN (' d_output ', ' EMP ' | | To_char (sysdate, ' yyyy_mm_dd ') | |
'. csv ', ' W ', 32767);
V_buffer: = ' Empno,ename,job,mgr,hiredate,sal,comm,deptno '; Utl_file.
Put_Line (V_file, V_buffer); For I in (select ' "' | | EMPNO | | 
 ' "," ' | | ename | | 
 ' "," ' | | JOB | | 
 ' "," ' | | MGR | | 
 ' "," ' | | HireDate | | 
 ' "," ' | | SAL | | 
 ' "," ' | | COMM | | 
 ' "," ' | | DEPTNO | | ' ' RESULT from EMP ' LOOP utl_file.
 Put_Line (V_file, I.result);
 END LOOP; Utl_file.
 FCLOSE (V_file);


END; In PL/SQL 3.3 or more, the Utl_file package allows users to read and write operating system files through PL/SQL. As follows: Declare File_handle utl_file.
File_type; Begin File_handle: = Utl_file.
FOPEN (' TMP ', ' filename ', ' w ', [1-32767]); --Four parameters: directory, file name, open mode, maximum number of rows (default is Utl_file).
Putf (File_handle, ' write information \ n '); Utl_file.
FCLOSE (File_handle); Exception when Utl_file.invalid_path and Raise_application_error ( -20000, ' error:invalid path for file or path not in INI
T.ora. '); 
End The--putf () procedure is used to specify the formatWrites a text to a file The--put_line () procedure writes a specified string to the file and starts a new line in the file 

CREATE OR REPLACE PROCEDURE preadfiletest (Fpath in String,fname in String,max_num on number) is File_handle UTL _file.
   File_type;
   Text_buffer STRING (1000);
Line_num number; BEGIN Dbms_output. 
   Put_Line (' INPUT path= ' Fpath); Dbms_output.
   Put_Line (' INPUT filename= ' FNAME);
   Line_num: = 0; BEGIN file_handle: = Utl_file.
     FOPEN (fpath,fname, ' R ', max_num);
       LOOP line_num:= Line_num + 1; Utl_file.
       Get_line (File_handle,text_buffer); Dbms_output.
     Put_Line (' line ' line_num ': ' Text_buffer);
   END LOOP;
     EXCEPTION when No_data_found and then RETURN; When Utl_file. Invalid_path then Dbms_output.
     Put_Line (' INVALID PATH '); When Utl_file. Invalid_mode then Dbms_output.
     Put_Line (' INVALID MODE '); When Utl_file. Invalid_filehandle then Dbms_output.
     Put_Line (' INVALID filehandle '); When Utl_file. Invalid_operation then Dbms_output.
     Put_Line (' INVALID operation '); When Utl_file. Read_error then DBMs_output.
     Put_Line (' READ ERROR '); When Utl_file. Write_error then Dbms_output.
     Put_Line (' WRITE ERROR '); When Utl_file. Internal_error then Dbms_output.
     Put_Line (' INTERNAL ERROR '); When OTHERS and then Dbms_output.
   Put_Line (SQLERRM);
END; EXCEPTION when OTHERS and then Dbms_output.
Put_Line (' other error= ' sqlerrm); END preadfiletest;

  file I/O is important for database development, such as the need to import data into a database using an I/O interface if part of the data in the database comes from a disk file. There is no direct I/O interface in PL/SQL, and the Put_Line function (that is, I/O to the screen) can be used to debug a program using Oracle's own Dbms_output package, but it is powerless for I/O operations on disk files.

In fact, Oracle also provides a utility package for file I/O-----utl_file package that uses the functions provided by this utility to implement I/O operations on the disk.

The Utl_file package provides a number of useful functions for I/O operations, mainly with the following functions: fopen Open the file for the specified directory path.

Get_line gets the text of a line of the specified file.

Put_Line writes a line of text to the specified file.

Fclose closes the specified file. The following functions are used to fetch data from a file and then write the data to the appropriate database. 

 Create or Replace procedure Loadfiledata (P_path varchar2,p_filename varchar2) as V_filehandle Utl_file.file_type; --Define a file handle V_text varchar2 (100);

  --Storing text v_name test_loadfile.name%type;

  V_ADDR_JD Test_loadfile.addr_jd%type;

  V_region Test_loadfile.region%type;

  V_firstlocation number;

  V_secondlocation number;

  v_totalinserted number;

  Begin IF (P_path is null or p_filename was null) then Goto to_end;

  End If;

  v_totalinserted:=0;

  /*open specified file*/v_filehandle:=utl_file.fopen (p_path,p_filename, ' R ');

  Loop begin Utl_file.get_line (V_filehandle,v_text);

  Exception when No_data_found then exit;

  End;

  V_firstlocation:=instr (V_text, ', ', 1, 1);

  V_secondlocation:=instr (V_text, ', ');

  V_name:=substr (v_text,1,v_firstlocation-1);

  V_addr_jd:=substr (v_text,v_firstlocation+1,v_secondlocation-v_firstlocation-1);

  V_region:=substr (v_text,v_secondlocation+1); /* Insert Database operation */INSERT INTO test_loadfile values (v_name,v_addr_jd,v_region);

  Commit

   End Loop;<<to_end>> null; End Loadfiledata;

Create or Replace procedure Test_error (str out varchar2, str2 out VARCHAR2) as begin declare Isto_file Utl_f
    Ile.file_type;
    Err_num number;
    I number;
    K number;
    M number;
    Err_msg varchar2 (100);
  Fp_buffer varchar2 (4000);
    Begin Isto_file: = Utl_file.fopen (' Ist0_dir ', ' kj021320.txt ', ' W ');
    i:=0; while (I<2) Loop Utl_file.put_line (isto_file, ' My '); I:=i+1; end Loop;
    Utl_file.fflush (Isto_file);
    
    
    Utl_file.fclose (Isto_file); Isto_file:= Utl_file.fopen (' Ist0_dir ',' kj021320.txt ', ' a '); M:= 0; while (m<2) loop utl_file.put_line (isto_file, ' My '); M:=m+1; end Loop;
    Utl_file.fflush (Isto_file);
    
    Utl_file.fclose (Isto_file); Isto_file:= Utl_file.fopen (' Ist0_dir ',' kj021320.txt ', ' R '); STR2:= "; Loop Utl_file.get_line (Isto_file, Fp_buffer); STR2:=STR2| | | fp_buffer;
    End Loop;       

    Utl_file.fclose (Isto_file); For j in 1..10/* for */loop K:= 11; end Loop; EXCEPTION when OTHERS then Err_num:=sqlcode;/* Often num */err_msg:=substr (sqlerrm,1,100);/* Abnormal MSG */STR:=substr (sqlerrm,1,100); end;

 End Test_error; 

/* 0. To avoid changes to the program, directories can be defined as a constant, or an Oracle directory.
  1.fopen mode: R (read-only), W (Read and write, and first clear the original data), A (Read and write, append data based on the original data). fopen Restrictions: (1) The directory and file name must be valid (2) directory must exist (3) if the R mode, the file must exist (4) if it is W mode, if the file does not exist, then automatically create (5) If it is a mode, the file must exist 2.is_open: Check whether the file is open (actually only
Check if the handle is empty, such as Fclose_all closed file, Is_open still returns True).
  3.get_line: Reads a row of data into the VARCHAR2 variable.
  NVARCHAR2 data uses Get_raw with Get_line_nchar;raw data. Reads to the end of the file, producing an No_data_found exception.
    There are also three cases where the No_data_found exception (1) does not return a row for the Select (2) PL/SQL collection in the undefined row (3) using Dbms_lob to read the bfile file to the end. So the above four kinds of no_data_found cases in a PL/SQL block, to distinguish the catch exception 4.put 9.fclose: If there is still no data written to the file in the buffer before closing, the write_error exception is triggered. Fclose_all: Close all open files. When closed, all file handles remain unchanged (still non-null), such as Step 3 in the test using Fclose_all to close the file, while is_opened is still true.
  However, the file is not writable at this time.
Cause: fclose passed the file handle parameter and was in out mode, the handle was set to NULL after the call, and Fclose_all did not pass in any file handle parameters, so the value of the file handle was not modified (still retains the original value). 6.frename: You can rename the file, rename the path (equivalent to Fcopy+fremove), or change 7.putf:put format 8.fgetattr: Get file attributes (presence, size, block size) */PROCEDURE PRC_
  Utl_file is File_read_handle utl_file.file_type;
  File_write_handle Utl_file.file_type;
Is_opened BOOLEAN;  V_one_line VARCHAR2 (1000);
  B_file_exist BOOLEAN;
  N_file_length number (10,2);
Bi_block_size Binary_integer;
  BEGIN--1. read/write mode open file File_read_handle: = Utl_file.fopen (' Test_utl_file_dir_read ', ' orcl_ora_396.trc ', ' R ');
  File_write_handle: = Utl_file.fopen (' test_utl_file_dir_write ', ' test_utl_file_dir_write.txt ', ' W ');
  --2. Check if the file is open is_opened: = Utl_file.is_open (File_read_handle);
  IF is_opened then Dbms_output.put_line (' file is opened ');
  ELSE dbms_output.put_line (' file is not opened ');
  END IF;
      --3. read file LOOP BEGIN utl_file.get_line (File_read_handle, v_one_line);
      Dbms_output.put_line (V_one_line);
      --4. Write the read-in result to the new file Utl_file.put (File_write_handle, v_one_line);
      Utl_file.new_line (File_write_handle, 2);
      --utl_file.put_line (File_write_handle, v_one_line);
    --utl_file.put_line (File_write_handle, V_one_line, TRUE);
      EXCEPTION when No_data_found then EXIT; When OTHERS then Dbms_output.put_line (' Error1: ' | |
        SQLERRM);
    EXIT;
    
  END;
  
  END LOOP;
  --5. Close file Utl_file.fclose (file_read_handle);
  --6 confirms that all pending data is written to the physical file--utl_file.fflush (file_write_handle);
  Utl_file.fclose (File_write_handle);
  --utl_file.fclose_all;
  --6. Check if the file is closed is_opened: = Utl_file.is_open (File_read_handle);
  IF is_opened then Dbms_output.put_line (' file is still opened ');
  ELSE dbms_output.put_line (' file is already closed ');
  END IF; --7. copy file Utl_file.fcopy (' Test_utl_file_dir_write ', ' test_utl_file_dir_write.txt ', ' test_utl_file_dir_write ', ' TEST
  _utl_file_dir_write_copy.txt ', 1, 10); --8. deleting files utl_file.fcopy (' Test_utl_file_dir_write ', ' test_utl_file_dir_write.txt ', ' test_utl_file_dir_write ', ' TEST
  _utl_file_dir_write_copy_delete.txt ', 1, 10);
  Utl_file.fremove (' Test_utl_file_dir_write ', ' test_utl_file_dir_write_copy_delete.txt '); --9. Renaming--utl_file.frename (' Test_utl_file_dir_write ', ' test_utl_file_dir_write_copy.txt ', ' TEST_UTL_FILE_DIR_ WRITE ', ' Test_utl_file_dir_write_Copy_delete_rename.txt ', FALSE); --10. Get renamed file Properties utl_file.fgetattr (' Test_utl_file_dir_write ', ' test_utl_file_dir_write_copy.txt ', b_file_exist,n_
  File_length, bi_block_size); IF B_file_exist then Dbms_output.put_line (' n_file_length: ' | | n_file_length| | ' \ n ' | | ' Bi_block_size ' | |
  Bi_block_size);
  
END IF;  END;

Article Source: http://www.cnblogs.com/pengyq/archive/2008/08/27/1277739.html

Oracle Read-Write file--read and write to disk files using Utl_file package