Using UTL_FILE Package
Sometimes I’m surprised. Today, the surprise came when somebody pointed to a potential error in another author’s book. The person who asked the question had to send me a screen shot before I believed it.
The author’s code encounters the following error because the code was designed to loop through a multiple line file, and the code called the UTL_FILE.FOPEN procedure with three instead of four parameters. While it works with only three parameters when the strings are less than or equal to 1,024 (thanks Gary), it throws read errors when a string exceeds the default. You use the fourth parameter when your string exceeds the default length of 1,024.
DECLARE * ERROR AT line 1: ORA-29284: FILE read error ORA-06512: AT "SYS.UTL_FILE", line 106 ORA-06512: AT "SYS.UTL_FILE", line 746 ORA-06512: AT line 26 |
As mentioned, you fix the problem by using the fourth parameter like the call on lines 15 through 18 below:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 | DECLARE /* Declare local input variables. */ lv_location VARCHAR2(60) := 'C:\Data\Direct'; lv_file_name VARCHAR2(40) := 'TextFile.txt'; /* Declare a file reference pointer and buffer. */ lv_file UTL_FILE.FILE_TYPE; -- File reference lv_line VARCHAR2(32767); -- Reading buffer BEGIN /* Check for open file and close when open. */ IF UTL_FILE.is_open(lv_file) THEN UTL_FILE.fclose(lv_file); END IF; /* Open the file for read-only of 32,767 lines of text. The fourth parameter is required when you want to use the GET_LINE procedure to read a file line-by-line. */ lv_file := UTL_FILE.fopen( location => lv_location , filename => lv_file_name , open_mode => 'R' , max_linesize => 32767); /* Read all lines of a text file. */ LOOP /* Read a line of text, when the eof marker is found the get_line procedure raises a NO_DATA_FOUND error, which is why there's no explicit loop. */ UTL_FILE.get_line( file => lv_file , buffer => lv_line ); /* Print the line of text or a line return because UTL_FILE doesn't read line returns. */ DBMS_OUTPUT.put_line(NVL(lv_line,CHR(10))); END LOOP; EXCEPTION WHEN NO_DATA_FOUND THEN UTL_FILE.fclose(lv_file); END; / |
While the foregoing is traditional and uses a physical directory path from the local server’s operating system, you can use a virtual directory from Oracle Database 10g forward. This physical directory is also defined in the utl_file_dir parameter of the database server. A virtual directory hides the physical directory from the application software, which simplifies physical file maintenance when you need to move the files.
3 | lv_location VARCHAR2(60) := 'DirectVirtualDirectory'; |
The preceding example works, and I’ll put one out converting the external to CLOB later in the week.
Subscribe via RSS