PL/SQL UTL_FILE read trouble on anonymous block

Question

 DECLARE
    data_line   VARCHAR2(200); -- Data line read from input file
    data_file   UTL_FILE.FILE_TYPE; -- Data file handle
    my_dir      VARCHAR2(250); -- Directory containing the data file
    my_filename VARCHAR2(50);


    BEGIN
      my_dir := 'c:\temp';
      my_filename := 'Lab4AData.dat';
      my_file := UTL_FILE.FOPEN(my_dir, my_filename, 'r'); 


    LOOP;
          UTL_FILE.GET_LINE(data_file, data_line);

      EXCEPTION
        WHEN no_data_found THEN
          DBMS_OUTPUT.PUT_LINE('Finished');
          exit;
          END LOOP;
      END;


    /

The problem is I cannot even get this anonymous block of code started. To start, I'm just trying to open my data file and read it, then build from there. But I can't even get the file open.

SQL Developer Error Report starts right off with

Error starting at line 5 in command:
DECLARE

then repeats the block of code and adds this:

ORA-06550: line 12, column 8:
PLS-00103: Encountered the symbol ";" when expecting one of the following:

   ( begin case declare exit for goto if loop mod null pragma
   raise return select update while with <an identifier>
   <a double-quoted delimited-identifier> <a bind variable> <<
   continue close current delete fetch lock insert open rollback
   savepoint set sql execute commit forall merge pipe purge
The symbol "exit" was substituted for ";" to continue.
ORA-06550: line 15, column 3:
PLS-00103: Encountered the symbol "EXCEPTION" when expecting one of the following:

   ( begin case declare end exit for goto if loop mod null
   pragma raise return select update while with <an identifier>
   <a double-quoted delimited-i
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

Your loop is malformed. Loops in the PL/SQL Language reference: docs.oracle.com/cd/E11882_01/appdev.112/e25519/… — Shannon Severance
Note, that the first parameter to fopen is a "string [that] is a directory object name and must be specified in upper case." docs.oracle.com/cd/E11882_01/appdev.112/e25788/…. A directory object is not simply a path. One uses create directory to create a directory object. docs.oracle.com/cd/E11882_01/server.112/e26088/… — Shannon Severance
Find finally, the file system that can be reached is the one where PL/SQL is running. If you are executing this on a server, the directory will be on the server, not on your local computer. To load data from your local computer a utility like SQL*Loader is called for. docs.oracle.com/cd/E11882_01/server.112/e22490/… — Shannon Severance

Bob Jarvis - Reinstate Monica Bob Jarvis - Reinstate Monica · Accepted Answer · 2013-05-27T23:58:17

Try the following:

DECLARE
  data_line   VARCHAR2(200); -- Data line read from input file
  data_file   UTL_FILE.FILE_TYPE; -- Data file handle
  my_dir      VARCHAR2(250); -- Directory containing the data file
  my_filename VARCHAR2(50);
BEGIN
  my_dir := 'c:\temp';
  my_filename := 'Lab4AData.dat';
  data_file := UTL_FILE.FOPEN(my_dir, my_filename, 'r'); 

  LOOP
    UTL_FILE.GET_LINE(data_file, data_line);

    -- add code to do something with data_line here
  END LOOP;
EXCEPTION
  WHEN no_data_found THEN
    DBMS_OUTPUT.PUT_LINE('Finished');
    UTL_FILE.FCLOSE(data_file);
END;

@ShannonSeverance's comments about using directory objects with UTL_FILE.FOPEN are appropriate, except in the instance where your DBA has not embraced their use and insists on sticking with the "tried and true" INIT.ORA parameter UTL_FILE_DIR. Don't ask me how I know... :-)

Share and enjoy.

PL/SQL UTL_FILE read trouble on anonymous block

1 Answers