UTL_FILE Example

UTL_FILE:

The set of files and directories that are accessible to the user through UTL_FILE is controlled by a number of factors and database parameters. Foremost of these is the set of directory objects that have been granted to the user.

Assuming the user has both READ and WRITE access to the directory object USER_DIR, the user can open a file located in the operating system directory described by USER_DIR, but not in subdirectories or parent directories of this directory.

Lastly, the client (text I/O) and server implementations are subject to operating system file permission checking.

UTL_FILE provides file access both on the client side and on the server side.

Use the CREATE DIRECTORY feature instead of UTL_FILE_DIR for directory access verification.

Note that neither hard nor symbolic links are supported.

The file location and file name parameters are supplied to the FOPEN function as separate strings, so that the file location can be checked against the list of accessible directories as specified by the ALL_DIRECTORIES view of accessible directory objects.

CREATE OR REPLACE PROCEDURE APPS.load_data

AS

v_line VARCHAR2(2000); — Data line read from input filev_file UTL_FILE.FILE_TYPE; — Data file handle

v_dir VARCHAR2(250); — Directory containing the data file

v_filename VARCHAR2(50); — Data filename

v_1st_Comma number;

v_per_id xx_person_details.per_id%type;

v_per_name xx_person_details.per_name%type;

BEGIN

v_dir := ‘/erp/tmp’;

v_filename := ‘xx_sample.dat’;

v_file := UTL_FILE.FOPEN(v_dir, v_filename, ‘r’);

— ——————————————————–

— Loop over the file, reading in each line. GET_LINE will

— raise NO_DATA_FOUND when it is done, so we use that as

— the exit condition for the loop.

— ——————————————————–

LOOP

BEGIN

UTL_FILE.GET_LINE(v_file, v_line);

EXCEPTION

WHEN no_data_found THEN

exit;

END;

— ———————————————————-

— Each field in the input record is delimited by commas. We

— need to find the locations of the two commas in the line,

— and use these locations to get the fields from v_line.

— ———————————————————-

v_1st_Comma := INSTR(v_line, ‘,’ ,1 , 1);

v_per_name := SUBSTR(v_line, 1, v_1st_Comma-1);

v_per_id := SUBSTR(v_line, v_1st_Comma+1);

DBMS_OUTPUT.PUT_LINE(v_per_name ||’ ‘|| v_per_id);

— ——————————————

— Insert the new record into the DEPT table.

— ——————————————

INSERT INTO xx_person_details

VALUES (v_per_name,v_per_id);

END LOOP;

UTL_FILE.FCLOSE(v_file);

COMMIT;

END;

/

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s