Oracle technology _ UTL_FILE package Usage Details _ writing files, reading and receiving tables

Oracle technology _ UTL_FILE package Usage Details _ writing files, reading and receiving tables

UTL_FILE is a standard toolkit provided by oracle for reading and writing files. This document describes how to use this package through an instance. The procedure is as follows:

1. Create a path

To operate a file, you need a corresponding path, and the path used in oracle needs a structure: directories (path, address). The specific usage is as follows:

Create the path to be operated (the server where the ORACLE database is located). Note that the path folder must exist here. If not, create it manually. Otherwise, exceptions may occur in many places. It is recommended that you do not have any Chinese characters in the path. When you use the path in some places, an exception occurs.

The Code is as follows:

Create or replace directory BLOB_FILE_DIR as '/home/oracle/export'; -- linux and unix system paths (unix at the bottom of mac ), BLOB_FILE_DIR is the created path: create or replace directory BLOB_FILE_DIR as 'd: \ test; -- windows path

2. Authorization

Authorize the Directory and the UTL_FILE package to the required users. Note that the authorization must be done, otherwise it will not be available.
The Code is as follows:

Grant read, write on directory BLOB_FILE_DIR to testUesr; -- path authorization, add the permission to read and write paths grant execute on utl_file to testUesr; -- utl_file package authorization, add the execution permission

3. Write a file

Use utl_file to write out the file, query the content in the database, and write it to the specified server path. The overall process is as follows:
(1) Use the UTL_FILE.FOPEN method to find the corresponding path, create a file, and provide write rules.
(2) Use the UTL_FILE.PUT_LINE method to write content to the file (UTL_FILE.PUT_LINE writes VARCHAR2 type data, and the UTL_FILE.PUT_RAW method writes RAW type data. Generally, the RAW capacity is larger and more widely used ), because ORACLE has a length limit, it generally uses the cyclic mode to write data in batches.
(3) After writing, close the file through UTL_FILE.FCLOSE and end writing.
Let's take a look at the specific operation. Here we will create a stored procedure GET_TEST_BLOB to demonstrate this function. For details, see the annotations in it.
The Code is as follows:

Create or replace procedure GET_TEST_BLOB (I _ID VARCHAR2) IS L_FILE UTL_FILE.FILE_TYPE; L_BUFFER VARCHAR2 (4000); -- VARCHAR2 IS up to 4000, so if it IS exceeded, use the loop method OR RAW, the maximum length is 32676 L_FILENAME VARCHAR2 (300); begin select f. C _ text into L_BUFFER FROM TEST_BLOB f where f. C _ ID = I _ID; -- create a table at will, including <span style = "font-family: Arial, Helvetica, sans-serif; "> select f. C _ name into L_FILENAME FROM TEST_BLOB f where f. C _ ID = I _ID; L_FILE: = UTL_FILE.FOPEN ('blob _ FILE_DIR ', L_FILENAME, 'w'); -- the third parameter is the open mode, including 'R ', 'w', 'A' rb', 'wb', and 'AB'-'r': ensure that the file is read, otherwise, the UTL_FILE.INVALID_PATH exception will be reported -- 'W': Write the file. If the file is not added, it will be automatically added. If yes, it will overwrite -- 'A': append the file. Make sure the file exists, append content after existing file content -- use byte mode with the 'B' suffix. BLOB is different from VARCHAR2, when opening BLOB, you must use the mode DBMS_OUTPUT.PUT_LINE ('= open OK =' | L_FILENAME | '=' | LENGTH (L_BUFFER) | '=' | L_BUFFER); UTL_FILE.PUT_LINE (L_FILE, L_BUFFER); -- write the file DBMS_OUTPUT.PUT_LINE ('= export OK = '); UTL_FILE.FCLOSE (L_FILE); exception when UTL_FILE.INVALID_PATH THEN -- invalid path DBMS_OUTPUT.PUT_LINE ('= INVALID_PATH =' | I _ID); RAISE; WHEN UTL_FILE.INVALID_MODE THEN -- invalid open mode DBMS_OUTPUT.PUT_LINE ('= INVALID_MODE =' | I _ID); RAISE; WHEN UTL_FILE.INVALID_OPERATION THEN -- invalid operation. This exception is reported WHEN a file is opened, generally, it is too long or the byte type and non-byte type DBMS_OUTPUT.PUT_LINE ('= INVALID_OPERATION =' | I _ID); RAISE; WHEN UTL_FILE.INVALID_MAXLINESIZE THEN -- invalid maximum length, VARCHAR2 has a maximum value of 4000, and RAW has a maximum value of 32676. If the return value is exceeded, this exception is thrown. Therefore, DBMS_OUTPUT.PUT_LINE ('= INVALID_MAXLINESIZE =' | I _ID); RAISE; WHEN UTL_FILE.ACCESS_DENIED THEN -- refuse to enter the specified path, which may be an authorization problem DBMS_OUTPUT.PUT_LINE ('= ACCESS_DENIED =' | I _ID); RAISE; WHEN processing THEN -- file processing error, uncommon DBMS_OUTPUT.PUT_LINE ('= INVALID_FILEHANDLE =' | I _ID); RAISE; WHEN UTL_FILE.WRITE_ERROR THEN -- write error. The best way to handle this exception is to simplify the file to be written, find the error cause DBMS_OUTPUT.PUT_LINE ('= WRITE_ERROR =' | I _ID); RAISE; WHEN NO_DATA_FOUND THEN--SELECT, DBMS_OUTPUT.PUT_LINE ('= NO_DATA_FOUND =' | I _ID); UTL_FILE.FCLOSE (L_FILE); RAISE; WHEN OTHERS THEN IF UTL_FILE.IS_OPEN (L_FILE) THEN UTL_FILE.FCLOSE (L_FILE); RAISE; end if; END GET_TEST_BLOB; <span style = "font-family: SimSun; font-size: 12px;"> </span>

Input parameters to call the stored procedure.
The Code is as follows:

begin  -- Call the procedure  get_test_blob('T1');end;

After execution, a file is generated in the corresponding directory, for example:

 

4. Read the file using utl_file to read the file. by reading the specified file, the read content is written into the database. The overall process is as follows:
(1) Use the UTL_FILE.FOPEN method to find the corresponding path, read the file (the file must exist), and provide the read rules.
(2) The UTL_FILE.GET_LINE method cyclically writes content to the variable (UTL_FILE.PUT_LINE writes VARCHAR2 type data, UTL_FILE.PUT_RAW writes RAW type data, generally larger RAW capacity, more widely used ), here, because row-by-row reading is required, it is required to perform cyclic operations (the NO_DATA_FOUND exception is reported, that is, the loop jumps out after no data is found ).
(3) INSERT the content variable into the specified database table
(4) After writing, use the UTL_FILE.FCLOSE method to close the file and stop reading.
Let's take a look at the specific operation. Here we will create a stored procedure SET_TEST_BLOB to demonstrate this function. For details, see the annotations in it.

Create or replace procedure SET_TEST_BLOB (I _FILENAME VARCHAR2, I _ID VARCHAR2) IS L_FILE UTL_FILE.FILE_TYPE; L_BUFFER VARCHAR2 (4000); -- VARCHAR2 can be up to 4000, so use the loop method OR RAW, it can be up to 32676 L_TEXT VARCHAR2 (4000): = ''; BEGIN L_FILE: = UTL_FILE.FOPEN ('blob _ FILE_DIR ', I _FILENAME, 'R '); -- if the file exceeds 4000, use a LOOP or RAW (max. 32676) DBMS_OUTPUT.PUT_LINE ('= open OK =' | I _FILENAME); LOOP BEGIN UTL_FILE.GET_LINE (L_FILE, L_BUFFER ); -- read the file. The maximum value is 32676. It is read cyclically until NO_DATA_FOUND EXCEPTION is reported to terminate the loop exception when NO_DATA_FOUND then exit; END; DBMS_OUTPUT.PUT_LINE ('= import OK =' | L_BUFFER); L_TEXT: = L_TEXT | L_BUFFER; DBMS_OUTPUT.PUT_LINE ('= text OK =' | L_TEXT); end loop; insert into TEST_BLOB F (C_ID, C_NAME, C_TEXT) VALUES (I _ID, I _FILENAME, L_TEXT ); -- insert Table UTL_FILE.FCLOSE (L_FILE); exception when UTL_FILE.INVALID_PATH THEN -- invalid path DBMS_OUTPUT.PUT_LINE ('= INVALID_PATH =' | I _ID); RAISE; WHEN UTL_FILE.INVALID_MODE THEN -- invalid open mode DBMS_OUTPUT.PUT_LINE ('= INVALID_MODE =' | I _ID); RAISE; WHEN UTL_FILE.INVALID_OPERATION THEN -- invalid operation. This exception is reported WHEN a file is opened, generally, it is too long or the byte type and non-byte type DBMS_OUTPUT.PUT_LINE ('= INVALID_OPERATION =' | I _ID); RAISE; WHEN UTL_FILE.INVALID_MAXLINESIZE THEN -- invalid maximum length, VARCHAR2 has a maximum value of 4000, and RAW has a maximum value of 32676. If the return value is exceeded, this exception is thrown. Therefore, DBMS_OUTPUT.PUT_LINE ('= INVALID_MAXLINESIZE =' | I _ID); RAISE; WHEN UTL_FILE.ACCESS_DENIED THEN -- refuse to enter the specified path, which may be an authorization problem DBMS_OUTPUT.PUT_LINE ('= ACCESS_DENIED =' | I _ID); RAISE; WHEN processing THEN -- file processing error, uncommon DBMS_OUTPUT.PUT_LINE ('= INVALID_FILEHANDLE =' | I _ID); RAISE; WHEN UTL_FILE.WRITE_ERROR THEN -- write error. The best way to handle this exception is to simplify the file to be written, find the cause of the error DBMS_OUTPUT.PUT_LINE ('= WRITE_ERROR =' | I _ID); RAISE; WHEN NO_DATA_FOUND THEN -- no data is found during SELECT, DBMS_OUTPUT.PUT_LINE ('= NO_DATA_FOUND =' | I _ID); UTL_FILE.FCLOSE (L_FILE); RAISE; WHEN OTHERS THEN IF UTL_FILE.IS_OPEN (L_FILE) THEN UTL_FILE.FCLOSE (L_FILE); RAISE; end if; END SET_TEST_BLOB; <span style = "font-family: SimSun; font-size: 12px;"> </span>

Input parameters to call the stored procedure. Note that the request must be submitted after execution. Otherwise, the request will not be inserted.
The Code is as follows:

begin  -- Call the procedure  set_test_blob('set_test.txt','ST2');end;

After execution, the file content will be stored in the table, for example:

Click to enter the ooppookid blog