Shulou(Shulou.com)06/01 Report--

In database development, when the PL/SQL statement is very complex, I want to write some Log, just like java's Log4j. In this way, you can well track the running of the program. The article found on the Internet is not very useful, so I revised it.

1: create a directory on the Oracle server and specify permissions (mine is unix. Windows can just use c:/tmp and so on)

CREATE DIRECTORY TEMP AS'/ tmp'

GRANT READ,WRITE ON DIRECTORY TEMP TO PUBLIC

GRANT EXECUTE ON SYS.UTL_FILE TO YOU_USER_NAME

If there is a permission error, pay attention to the way you log in. I used the oralce user TelNet to Unix, and then connect / as sysdba. (the windows user should be: log in to the database with the sys user (ora9i): conn sys/password@oraclesid as sysdba, the default password of the sys user is the same as system, both are manager)

2: the SQL statement for writing Log files is as follows:

Declare

File_handle utl_file.file_type

Buff varchar2 (20)

Cursor c1 is select acloumn from t_atable

Begin

File_handle: = utl_file.fopen ('TEMP',log.txt','w')

Open c1

Loop

Fetch c1 into buff

Exit when c1%notfound

Utl_file.put_line (file_handle,buff)

End loop

Close c1

Utl_file.fclose (file_handle)

End

In this way, it should be OK, file_handle: = utl_file.fopen ('TEMP',log.txt','w'); the' w' in 'means write, or it can be' axiomain 'append.

After encapsulation, it looks like this:

Procedure writeLog (logMessage in Varchar2) is

Begin

IF logSwitch THEN

File_handle: = utl_file.fopen ('TEMP','x.txt','w')

Utl_file.put_line (file_handle,logMessage)

Utl_file.fclose (file_handle)

END IF

End

Where logSwitch is Boolean, which I define in the package. If used directly, it can be procedure writeLog (logMessage in Varchar2, logSwitch in boolean).

/ * /

Recently used Oracle package UTL _ FILE, but the Internet did not find its function, process instructions, although it is not very difficult things, but a simple list can also improve some efficiency.

Hence this article.

The following translation is from Chapter 6 of Oracle Built-in Packages, which is only partially translated. For more details, please refer to the original text. Http://www.oreilly.com/catalog/oraclebip/chapter/ch06.html

FOPEN

IS_OPEN

GET_LINE

PUT

NEW_LINE

PUT_LINE

PUTF

FFLUSH

FCLOSE

FCLOSE_ALL

UTL_FILE.FOPEN usage

FOPEN opens the specified file and returns a file handle for manipulating the file.

All PL/SQL versions: Oracle 8.0 and above:

FUNCTION UTL_FILE.FOPEN (FUNCTION UTL_FILE.FOPEN (

Location IN VARCHAR2, location IN VARCHAR2

Filename IN VARCHAR2, filename IN VARCHAR2

Open_mode IN VARCHAR2) open_mode IN VARCHAR2

RETURN file_type; max_linesize IN BINARY_INTEGER)

RETURN file_type

Parameters.

Location

File address

Filename

File name

Openmode

The mode of opening the file (see instructions below)

Max_linesize

The maximum number of characters per line of the file, including newline characters. Minimum is 1, maximum is 32767

3 file opening modes:

R read-only mode. Generally cooperate with GET_LINE of UTL_FILE to read files.

W write (replace) mode. All lines of the file are deleted. PUT, PUT_LINE, NEW_LINE, PUTF and FFLUSH are all available

A write (attach) mode. All lines of the original file are retained. Append a new line to the last line. PUT, PUT_LINE, NEW_LINE, PUTF and FFLUSH are all available

Note the following when opening a file:

The file path and file name must represent a legitimate file in the operating system.

The file path must exist and be accessible; FOPEN does not create a new folder.

If you want to open a file for reading, the file must exist; if you want to open the file for writing, a new file will be created when the file does not exist.

If you want to open a file for additional operations, the file must exist. A mode is different from W mode. An INVALID_OPERATION exception is thrown when the file does not exist.

Note: when I use mode A, the file is automatically created if the file does not exist, and a UTL_FILE.INVALID_OPERATION exception is thrown when the directory does not exist (linux system, oracle 9i)

FOPEN throws the following exception

UTL_FILE.INVALID_MODE

UTL_FILE.INVALID_OPERATION

UTL_FILE.INVALID_PATH

UTL_FILE.INVALID_MAXLINESIZE

UTL_FILE.IS_OPEN usage

Returns TRUE if the file specified by the file handle is open, otherwise FALSE

FUNCTION UTL_FILE.IS_OPEN (file IN UTL_FILE.FILE_TYPE) RETURN BOOLEAN

UTL_FILE provides only one way to read data: GET_LINE

UTL_FILE.GET_LINE usage

Reads a line of the specified file to the provided cache.

PROCEDURE UTL_FILE.GET_LINE

(file IN UTL_FILE.FILE_TYPE

Buffer OUT VARCHAR2)

File

File handle returned by FOPEN

Buffer

Storage cache of a row of data read

The buffer must be large enough. Otherwise, a VALUE_ERROR exception is thrown. The line Terminator is not passed into the buffer.

Abnormal

NO_DATA_FOUND

VALUE_ERROR

UTL_FILE.INVALID_FILEHANDLE

UTL_FILE.INVALID_OPERATION

UTL_FILE.READ_ERROR

UTL_FILE.PUT usage

Output data on the current line

PROCEDURE UTL_FILE.PUT

(file IN UTL_FILE.FILE_TYPE

Buffer OUT VARCHAR2)

File

File handle returned by FOPEN

Buffer

Contains the data cache to be written to the file; the maximum 32kB allowed for Oracle8.0.3 and above is 1023B for earlier versions

No line Terminator is appended when UTL_FILE.PUT outputs data.

UTL_FILE.PUT will produce the following exception

UTL_FILE.INVALID_FILEHANDLE

UTL_FILE.INVALID_OPERATION

UTL_FILE.WRITE_ERROR

UTL_FILE.NEW_LINE

To output a new line or line Terminator at the current location, you must use NEW_LINE to end the current line, or use PUT_LINE to output the full line data with the line Terminator.

PROCEDURE UTL_FILE.NEW_LINE

(file IN UTL_FILE.FILE_TYPE

Lines IN NATURAL: = 1)

File

File handle returned by FOPEN

Lines

Number of rows to insert

If you do not specify the lines parameter, NEW_LINE wraps at the end of the current line with the default value of 1. If you want to insert a blank line, use the following statement:

UTL_FILE.NEW_LINE (my_file, 2)

If the lines parameter is 0 or a negative number, nothing is written to the file.

NEW_LINE will produce the following exception

VALUE_ERROR

UTL_FILE.INVALID_FILEHANDLE

UTL_FILE.INVALID_OPERATION

UTL_FILE.WRITE_ERROR

Examples

If you want to change lines immediately after UTL_FILE.PUT, you can do the following:

PROCEDURE add_line (file_in IN UTL_FILE.FILE_TYPE, line_in IN VARCHAR2)

IS

BEGIN

UTL_FILE.PUT (file_in, line_in)

UTL_FILE.NEW_LINE (file_in)

END

UTL_FILE.PUT_LINE

Output a string and a system-related line Terminator

PROCEDURE UTL_FILE.PUT_LINE

(file IN UTL_FILE.FILE_TYPE

Buffer IN VARCHAR2)

File

File handle returned by FOPEN

Buffer

Contains the data cache to be written to the file; the maximum 32kB allowed for Oracle8.0.3 and above is 1023B for earlier versions

You must open the file before calling UTL_FILE.PUT_LINE.

UTL_FILE.PUT_LINE will produce the following exception

UTL_FILE.INVALID_FILEHANDLE

UTL_FILE.INVALID_OPERATION

UTL_FILE.WRITE_ERROR

Examples

Here we use UTL_FILE.PUT_LINE to read data from the table emp to the file:

PROCEDURE emp2file

IS

FileID UTL_FILE.FILE_TYPE

BEGIN

FileID: = UTL_FILE.FOPEN ('/ tmp', 'emp.dat',' W')

/ * Quick and dirty construction here! * /

FOR emprec IN (SELECT * FROM emp)

LOOP

UTL_FILE.PUT_LINE

(TO_CHAR (emprec.empno) | |','| |

Emprec.ename | |','| |

...

TO_CHAR (emprec.deptno))

END LOOP

UTL_FILE.FCLOSE (fileID)

END

PUT_LINE is equivalent to PUT, followed by NEW_LINE; is also equivalent to PUTF's format string "% szone".

UTL_FILE.PUTF

Output up to 5 strings in a template style, similar to printf in C #

PROCEDURE UTL_FILE.PUTF

(file IN FILE_TYPE

, format IN VARCHAR2

, arg1 IN VARCHAR2 DEFAULT NULL

, arg2 IN VARCHAR2 DEFAULT NULL

, arg3 IN VARCHAR2 DEFAULT NULL

, arg4 IN VARCHAR2 DEFAULT NULL

, arg5 IN VARCHAR2 DEFAULT NULL)

File

File handle returned by FOPEN

Format

Format string that determines the format

ArgN

5 optional parameters, up to 5

Format strings can use the following styles

% s

You can use up to 5% s in the format string, corresponding to the next five parameters one by one.

/ n

Newline character. There is no limit on the number of format strings.

% s will be populated in turn by the following parameters. If there are not enough parameters,% s will be ignored and will not be written to the file.

UTL_FILE.PUTF will produce the following exception

UTL_FILE.INVALID_FILEHANDLE

UTL_FILE.INVALID_OPERATION

UTL_FILE.WRITE_ERROR

UTL_FILE.FFLUSH

Ensure that all data is written to the file.

PROCEDURE UTL_FILE.FFLUSH (file IN UTL_FILE.FILE_TYPE)

File

File handle returned by FOPEN

The operating system may cache data to improve performance. Therefore, it is possible to open the file and not see the written data after calling put. You can use UTL_FILE.FFLUSH if you want to read data before closing the file.

Typical usage includes analyzing execution progress and debugging records.

UTL_FILE.FFLUSH will produce the following exception

UTL_FILE.INVALID_FILEHANDLE

UTL_FILE.INVALID_OPERATION

UTL_FILE.WRITE_ERROR

UTL_FILE.FCLOSE

Close the file

PROCEDURE UTL_FILE.FCLOSE (file IN OUT FILE_TYPE)

File

File handle returned by FOPEN

Note that file is an IN OUT parameter because it will be set to NULL when the file is closed

When trying to close a file, cached data is not written to the file, and a WRITE_ERROR exception is thrown

UTL_FILE.FCLOSE will produce the following exception

UTL_FILE.INVALID_FILEHANDLE

UTL_FILE.WRITE_ERROR

UTL_FILE.FCLOSE_ALL

Close all open files

PROCEDURE UTL_FILE.FCLOSE_ALL

To make sure that all open files are closed at the end of the program, use FCLOSE_ALL

It can also be used in EXCEPTION, and when an exception exits, the file will be closed.

EXCEPTION

WHEN OTHERS

THEN

UTL_FILE.FCLOSE_ALL

... Other clean up activities...

END

Note: when all files are closed using FCLOSE_ALL, the file handle is not marked as NULL, and IS_OPEN returns TRUE. However, files that are closed cannot be read or written (unless you open the file again).

UTL_FILE.FCLOSE_ALL will produce the following exception

UTL_FILE.WRITE_ERROR

/ / *

-write log files

Before using the utl_file package, you should pay attention to setting the utl_file_dir initialization parameters

1. Log in as the oracle user and create a directory under the oracle directory (or its subdirectories): $mkdir tgm_test

two。 Create a directory object in the database: create or replace directory TGM_LOG_DIR as'/ home/oracle/tgm_test'

3. Log in as an administrator user, such as sqlplus "/ as sysdba"

4. Set the operable directory, alter system set utl_file_dir='/home/oracle/tgm_test' scope=spfile

5. Authorized to the specified user to execute utl_file: GRANT EXECUTE ON utl_file TO train

6. Conn train/train can use utl_file normally.

7. Exec write_log ('this is a testings')

-3, 4, 5, 5 seems to be omitted.

-- write_log stored procedure

CREATE OR REPLACE PROCEDURE write_log (text_context VARCHAR2) IS

File_handle utl_file.file_type

Write_content VARCHAR2 (1024)

File_name VARCHAR2 (50)

BEGIN

-- open file

File_name: = 'tgm.log'

File_handle: = utl_file.fopen ('TGM_LOG_DIR', file_name,' w');-- write mode

Write_content: = to_char (SYSDATE, 'yyyy-mm-dd hh34:mi:ss') | |'-- >'| | text_context

-- write file

IF utl_file.is_open (file_handle)

THEN

Dbms_output.put_line ('is openings')

Utl_file.put_line (file_handle, write_content)

END IF

-- close file

Utl_file.fclose (file_handle)

EXCEPTION

WHEN OTHERS THEN

BEGIN

IF utl_file.is_open (file_handle)

THEN

Utl_file.fclose (file_handle)

END IF

EXCEPTION

WHEN OTHERS THEN

NULL

END

END write_log

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.