Best UTL_FILE Practice
In a post a couple days ago, I promised to provide a best practice approach to reading external files with the UTL_FILE package. My first assumption is that you’re reading unstructured data because structured data is best read by external tables because external tables can read data much faster with the PARALLEL option.
My second assumption is that you’re you don’t know how to use or choose not to use the DBMS_LOB package; specifically, the loadclobfromfile and loadblobfromfile procedures. By the way, Chapter 10 of the Oracle Database 12c PL/SQL Programming has several complete examples using the DBMS_LOB package. My third assumption is that you’d like the external file packaged as a whole, which means you want it returned as a CLOB or BLOB data type.
Here’s a generic function that achieves that and avoids the nonsense with closing the file in the exception handler, or worse yet, wrapping it in another PL/SQL schema-level function:
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 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 | CREATE OR REPLACE FUNCTION read_file_to_clob ( pv_location VARCHAR2 , pv_file_name VARCHAR2 ) RETURN CLOB IS /* Declare local input variables. */ lv_location VARCHAR2(60); lv_file_name VARCHAR2(40); /* Declare a file reference pointer and buffer. */ lv_file UTL_FILE.FILE_TYPE; -- File reference lv_line VARCHAR2(32767); -- Reading buffer /* Declare local sizing variables. */ lv_file_size NUMBER; lv_line_size NUMBER; lv_read_size NUMBER :=0; /* Declare local file attribute data. */ lv_file_exists BOOLEAN := FALSE; lv_block_size BINARY_INTEGER; /* Declare a control variable and return CLOB variable. */ lv_enable BOOLEAN := FALSE; lv_return CLOB; BEGIN /* Declare local input variables. */ lv_location := pv_location; lv_file_name := pv_file_name; /* Check for open file and close when open. */ IF UTL_FILE.is_open(lv_file) THEN UTL_FILE.fclose(lv_file); END IF; /* Read the file attributes to get the physical size. */ UTL_FILE.fgetattr( location => lv_location , filename => lv_file_name , fexists => lv_file_exists , file_length => lv_file_size , block_size => lv_block_size ); /* Open only files that exist. */ IF lv_file_exists THEN /* Create a temporary CLOB in memory. */ DBMS_LOB.createtemporary(lv_return, FALSE, DBMS_LOB.CALL); /* Open the file for read-only of 32,767 byte lines. */ 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. */ WHILE (lv_read_size < lv_file_size) LOOP /* Read a line of text until the eof marker. */ UTL_FILE.get_line( file => lv_file , buffer => lv_line ); /* Add the line terminator or 2 bytes to its length. */ lv_line := NVL(lv_line,'')||CHR(10); lv_read_size := lv_read_size + LENGTH(NVL(lv_line,CHR(10))) + 2; /* Write to an empty CLOB or append to an existing CLOB. */ IF NOT lv_enable THEN /* Write to the temporary CLOB variable. */ DBMS_LOB.WRITE( lv_return, LENGTH(lv_line), 1, lv_line); /* Set the control variable. */ lv_enable := TRUE; ELSE /* Append to the temporary CLOB variable. */ DBMS_LOB.writeappend( lv_return, LENGTH(lv_line),lv_line); END IF; END LOOP; /* Close the file. */ UTL_FILE.fclose(lv_file); END IF; /* This line is never reached. */ RETURN lv_return; EXCEPTION WHEN OTHERS THEN UTL_FILE.fclose(lv_file); RAISE NO_DATA_FOUND; END; / |
You can test the function with the following:
SET LONG 100000 SET PAGESIZE 999 SELECT read_file_to_clob('SomeVirtualDirectory','TextFile.txt') AS "Output" FROM dual; |
If anybody has suggestions for improvements, please pass them along. As always, I hope this helps other developers.
Subscribe via RSS