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
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.
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
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_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.
WHEN no_data_found THEN
— 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