Copying data from a text file to a CLOB in PL/SQL

Question

I have used the following code to copy the text from a file to a CLOB. However it is giving me a PL/SQL numeric or value error at the position where writeappend is performed.

    declare
  l_fhandle utl_file.file_type;
  l_clob    CLOB;
  l_buffer  VARCHAR2(4096);
BEGIN
  l_fhandle := utl_file.fopen('/data',
                              'FILE.TXT',
                              'R');
  dbms_lob.createtemporary(l_clob, TRUE, DBMS_LOB.CALL);
  LOOP
    BEGIN
      utl_file.get_line(L_FHANDLE, l_buffer);
      dbms_output.put_line(l_buffer);
     dbms_lob.writeappend(l_clob, length(l_buffer), l_buffer);

    EXCEPTION
      WHEN no_data_found THEN
        dbms_output.put_line('Inside No data found');
        INSERT INTO TAB_CLOB_FILE
          (FILENAME, BODYCONT)
        VALUES
          ('FILE', l_clob);
          dbms_output.put_line('Inserted data into table');
        EXIT;

    END;
  END LOOP;
END;

Please help me figure out what is wrong

Answer 1

Your problem is most likely here:

utl_file.fopen('/data', 'FILE.TXT','R');

The fist parameter is the name of an oracle directory OBJECT, not a physical path to a directory. From the Oracle docs, "Directory location of file. This string is a directory object name and is case sensitive. The default is uppercase. Read privileges must be granted on this directory object for the UTL_FILE user to run FOPEN." The incorrect call should have thrown an exception though.

You need to first create a directory object such as:

create directory MY_DIR as '/data';  

Then change the fopen call to: utl_file.fopen('MY_DIR', 'FILE.TXT','R');

You can read about directory objects here.