In the version of PL / SQL 3.3, the UTL_FILE package allows the user to read the operating system file via the PL / SQL. As follows: declare file_handle; begin file_handle: = UTL_FILE.FOPEN ('TMP', 'file name', 'W', [1-32767]); - four parameters: directory, file name, open mode, Maximum number of lines (defaults to 2000) UTL_FILE.PUTF (File_Handle, 'Write information / n'); UTL_FILE.FCLOSE (File_Handle); Exception When UTL_FILE.INVALID_PATH THEN RAISE_APPLICALID_PATH THEN RAISE_APPLICATION_ERROR (-20000, 'Error: Invalid Path for File OR Path not in init.ora. '); END; PUTF () process is used to write text into a file PUT_LINE () process in a specified format to write a specified string into a file and start a new line in the file.
9i, Function Description: UTL_FILE.FOPEN (location IN VARCHAR2, filename IN VARCHAR2, open_mode IN VARCHAR2, max_linesize IN BINARY_INTEGER) RETURN file_type; Parameters Description location (IN) The directory location of the source file, a DIRECTORY_NAME from the ALL_DIRECTORIES view (case Sensitive) FileName File Name, Including Extension (File Type), WITHOUT DIRECTORY PATH. IN UNIX, The FileName Cannot end with /.
A test 1 using a file, log in to the database (ORA9I) with SYS users: Conn Sys / Orcl @ Eai01 as sysdba2, run Create or Replace Directory TMP as 'D: / Testtmp' in SQLPLUS; Run Grant Read on Sqlplus directory TMP to public3, as established in the stored user scott procedure: pReadFileTestCREATE OR REPLACE pROCEDURE pReadFileTest (FPATH iN STRING, FNAME iN STRING, MAX_NUM iN NUMBER) IS fILE_HANDLE UTL_FILE.FILE_TYPE; TEXT_BUFFER STRING (1000); lINE_NUM NUMBER; BEGIN DBMS_OUTPUT. PUT_LINE ('INPUT PATH =' ||; dbms_output.put_line ('Input FileName =' ||; line_num: = 0; recomgin file_handle: = UTL_FILE.FOPEN (fpath, fname, 'r', max_num); LOOP lINE_NUM: = lINE_NUM 1; UTL_FILE.GET_LINE (fILE_HANDLE, TEXT_BUFFER); DBMS_OUTPUT.PUT_LINE ( 'LINE' || lINE_NUM || ':' || TEXT_BUFFER); END LOOP; EXCEPTION WHEN NO_DATA_FOUND THEN RETURN; WHEN UTL_FILE.INVALID_PATH THEN DBMS_OUTPUT.PUT_LINE ('Invalid Path'); when Utl_File.invalid_Mode Then DBMS_OUTPUT.PUT_LINE ('Invalid Mode'); when Utl_File.invalid_FileHandle Then DBMS_OUTPUT.PU T_LINE ( 'INVALID FILEHANDLE'); WHEN UTL_FILE.INVALID_OPERATION THEN DBMS_OUTPUT.PUT_LINE ( 'INVALID OPERATION'); WHEN UTL_FILE.READ_ERROR THEN DBMS_OUTPUT.PUT_LINE ( 'READ ERROR'); WHEN UTL_FILE.WRITE_ERROR THEN DBMS_OUTPUT.PUT_LINE ( 'WRITE ERROR '); WHEN UTL_FILE.INTERNAL_ERROR THEN DBMS_OUTPUT.PUT_LINE (' INTERNAL ERROR '); WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE (SQLERRM); END; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE (' OTHER ERROR = '|| SQLERRM); END pReadFileTest 4, run Set ServerOutput on5, Exec Preadfiletest ('TMP', 'A.txt', 'R' in SQLPlus
6. Normal output summary: In the 9i, you can add a UTL_FILE_DIR directory in init.ra, but create a Directory directory under the SYS user (actually adding a corresponding OS_PATH in the Dir $ table under the SYS user. Then, the permissions of the read / write operation of the Directory object will be given to public.
