MacLochlainns Weblog

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;
/

SET LONG 100000
SET PAGESIZE 999
 
SELECT read_file_to_clob('SomeVirtualDirectory','TextFile.txt') AS "Output"
FROM   dual;

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

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;
/

  lv_location      VARCHAR2(60) := 'DirectVirtualDirectory';