Oracle Utl_file_dir Parameters Detailed __oracle

1 utl_file_dir parameter definition

Utl_file_dir is a "static parameter" in Oracle, and you can set one or more paths. Specifies the path for file I/O operations in Pl/sql (which can be utl_file packages). Utl_file_dir is a "static parameter" in Oracle, and you can set one or more paths. Used to qualify a path for file I/O operations in Pl/sql (which can be utl_file packages), Utl_file packages can only be created under a specified path and read files. When Utl_file_dir is empty, the path is not qualified.

2 Utl_file Package Introduction

There is no direct file I/O interface in Pl/sql, and it is generally possible to use the Put_Line function of Oracle's Dbms_output package (that is, I/O to the screen) while debugging a program, but I/O can not be used on disk files. File I/O is important for database development, for example, if part of the data in a database comes from a disk file, then you need to use the I/O interface to import the data into the database.

3 Experiments

3.1 Setting Utl_file_dir parameters

Sql> alter system set utl_file_dir= '/u01/app/oracle ' scope=spfile;

System altered.

sql> startup force;

Sql> Show Parameter Utl_file

NAME TYPE VALUE

-------------------------------- ----------- ------------------------------

Utl_file_dir string/u01/app/oracle

To set multiple paths:

Sql> alter system set utl_file_dir= '/u01/app/oracle ', '/oradata ' scope=spfile;

System altered.

sql> Startup force

NAME TYPE VALUE

-------------------------------- ----------- ------------------------------

Utl_file_dir String/u01/app/oracle,/oradata

IO operations for 3.2 utl_file

Sql> Declare

FN Utl_file.file_type;

Begin

fn: = Utl_file.fopen ('/u01/app/oracle ', ' utl_test.txt ', ' W ');

Utl_file.fclose (FN);

End

/

Pl/sql procedure successfully completed.

When you use the Fopen method, the error occurs when you are not utl_file_dir the path you specify:

Sql> Declare

FN Utl_file.file_type;

Begin

fn: = Utl_file.fopen ('/u01/app/oracle/admin ', ' utl_test.txt ', ' W ');

Utl_file.fclose (FN);

End

/

Declare

*

ERROR at line 1:

Ora-29280:invalid directory path

Ora-06512:at "SYS. Utl_file ", line 33

Ora-06512:at "SYS. Utl_file ", line 436

Ora-06512:at Line 4

To avoid the above error, you can use the Path object.

Sql> Create directory Dir_test as '/oradata ';

Directory created.

Sql> Declare

FN Utl_file.file_type;

Begin

fn: = Utl_file.fopen (' dir_test ', ' test.txt ', ' W ');

Utl_file.fclose (FN);

End