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
– Shannon Severance, Commented May 27, 2013 at 23:16
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
– Shannon Severance, Commented May 27, 2013 at 23:22
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
– Shannon Severance, Commented May 27, 2013 at 23:24

Bob Jarvis - Слава Україні · Accepted Answer · 2013-05-27 23:58:17Z

1

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.

answered May 27, 2013 at 23:58

Bob Jarvis - Слава Україні

50.2k10 gold badges81 silver badges119 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Dug Over a year ago

Thanks but I still get: Error starting at line 1 in command: -- then echoes all the code and adds these errors: Error report: ORA-06550: line 3, column 15: PLS-00201: identifier 'UTL_FILE' must be declared ORA-06550: line 3, column 15: PL/SQL: Item ignored ORA-06550: line 9, column 3: PLS-00320: the declaration of the type of this expression is incomplete or malformed ORA-06550: line 9, column 3: PL/SQL: Statement ignored ORA-06550: line 12, column 23: PLS-00320: the declaration of the type of this expression is incomplete or malformed

Jeffrey Kemp Over a year ago

@Dug, check that the user you're logged in as has EXECUTE privilege on UTL_FILE.

Bob Jarvis - Слава Україні Over a year ago

@Dug - to do as @JeffreyKemp suggested, try SELECT * FROM ALL_TAB_PRIVS WHERE TABLE_NAME='UTL_FILE'. I'd expect that you'd find that PUBLIC has been granted EXECUTE access on UTL_FILE, but perhaps your DBA felt the need to mess with this. If neither PUBLIC nor your user ID have EXECUTE privileges on UTL_FILE, I'd say this would be something for your site admins to look at.

Dug Over a year ago

Thank you Bob and Jeffrey. I am running 11g on my home pc. I am the only DBA. I ran that select statement you recommended Bob. It comes back with columns but no data. It looks noone has permissions. I have a separate question going in an Oracle forum about why I am getting an 'insufficient privileges' error message when I try to login as sysdba. I think when I can login as sysdba, I should be able to set privileges and actually move on with this project...

Dug Over a year ago

I was logged in as a user, not the administrator on Windows 7. It was my lack of privileges on Windows that was causing the problem. I share this PC with members of my family so at the time it seemed like a good idea to set permissions up this way. But it got me today. Once I logged into my Windows Admin account, everything resolved. I was able to grant permissions.

Collectives™ on Stack Overflow

PL/SQL UTL_FILE read trouble on anonymous block

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related