Step 1: Log in with administrator users
CONN
Sys / password @ SID As Sysdba
Step 2: Set an operable directory
You need to specify the directory that the UTL_FILE package can operate. Before Oracle 10g, the following methods can be used:
1, alter system set UTL_FILE_DIR = 'E: / UTL' Scope = SPFILE;
2. In the init.ora file, the configuration is as follows:
UTL_FILE = E: / UTL or UTL_FILE_DIR = E: / UTL
In Oracle 10g, it is recommended to configure the following method: Create Directory UTL AS 'E: / UTL';
See Oracle Online:
In the past, accessible directories for the UTL_FILE functions were specified in the initialization file using the UTL_FILE_DIR parameter. However, UTL_FILE_DIR access is not recommended. It is recommended that you use the CREATE DIRECTORY feature, which replaces UTL_FILE_DIR. Directory objects offer more flexibility and . granular control to the UTL_FILE application administrator, can be maintained dynamically (that is, without shutting down the database), and are consistent with other Oracle tools CREATE DIRECTORY privilege is granted only to SYS and SYSTEM by default step 3: authorization to Specify the user to perform UTL_FILE
Grant Execute on UTL_FILE TO Scott;
Step 4: CONN Scott / Tiger
UTL_FILE can be used normally.
Specific use example:
Create or Replace Procedure Test_Data_txt
IS
LOG_FILE UTL_FILE.FILE_TYPE;
Curr_time varchar2 (25);
Begin
- Open the log file
LOG_FILE: = UTL_FILE.FOPEN ('E: / UTL', 'Test.log', 'A');
- Get the current time
Select to_char (sysdate, 'YYYY-MM-DD HH24: MI: SS') INTO CURR_TIME FROM DUAL
--Enter information
UTL_FILE.PUTF (log_file, '- start_time:% s - / nauthor =% s / n', curr_time, 'Tom');
- Output cache
UTL_FILE.fflush (log_file);
--shut down
UTL_FILE.FCLOSE (log_file);
--UTL_FILE.FCLOSE_ALL;
End test_data_txt;
