Shulou(Shulou.com)05/31 Report--

Oracle how to set UTL_FILE_DIR parameters, I believe that many inexperienced people do not know what to do, so this paper summarizes the causes of the problem and solutions, through this article I hope you can solve this problem.

Setting UTL_FILE_DIR parameters in oracle

Step 1: log in as administrator user

Such as: conn sys/password@sid as sysdba

Step 2: set up the operable directory

You need to specify a directory that the utl_file package can operate on. Before oracle 10g, you can use the following methods:

1. Alter system set utl_file_dir='e:\ utl' scope=spfile

Use''for long paths, such as utl_file_dir='c:\ my temp'

2. In the init.ora file, the configuration is as follows: UTL_FILE=E:\ utl or UTL_FILE_DIR=E:\ utl

The following methods are recommended for configuration in oracle10g: CREATE DIRECTORY utl AS'E:\ utl'

Step 3: authorize the designated user to perform utl_file

GRANT EXECUTE ON utl_file TO scott

Step 4: conn scott/tiger

You can use utl_file normally.

The fifth step: the implementation of the file Iswap O

The UTL_FILE package provides a number of practical functions for performing the Icano operation, mainly the following functions:

Fopen: opens the file in 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.

These functions are used to fetch data from the file and write the data to the corresponding database.

Create or replace procedure loadfiledata (p_path varchar2,p_filename varchar2) as

V_filehandle utl_file.file_type;-defines a file handle

V_text varchar2;-- 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 is null) then

Goto to_end

End if

V_totalinserted:=0

V_filehandle:=utl_file.fopen (paired pathdirection paired filename pamphlet r')

Loop

Begin

Utl_file.get_line (vandalism filehandledvanttext)

Exception

When no_data_found then

Exit

End

V_firstlocation:=instr (vandalism textbook, page1, page1)

V_secondlocation:=instr (vandalism textbook, pencils, page1, 2)

V_name:=substr (vandalism textbook 1, book 1, book first location 1)

V_addr_jd:=substr (vandalism textbook vandalism firstlocationalization 1, recording secondlocationhouse vandalism firstlocation1)

V_region:=substr (vroomtexttextvested secondlocationroom1)

Insert into test_loadfile

Values (vandalism _ jd _ recording region)

Commit

End loop

Null

End loadfiledata

After reading the above, have you mastered the method of setting UTL_FILE_DIR parameters in oracle? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

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.