1 2 Previous Next 20 Replies Latest reply: May 28, 2013 2:40 PM by 1011260 RSS

    Trouble with UTF File on anonymous block

    1011260
      I'm just trying to get started on this program in Oracle SQL Developer but cannot seem to get the basic commands down to read the data. It starts on DECLARE and any help is greatly appreciated:

      Error starting at line 5 in command:
      DECLARE
      data_line VARCHAR2(200); -- Data line read from input file
      data_file SYS.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);
      END LOOP;
      EXCEPTION
      WHEN no_data_found THEN
      DBMS_OUTPUT.PUT_LINE('Finished');
      UTL_FILE.FCLOSE(data_file);

      END;
      Error report:
      ORA-06550: line 11, 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.
      06550. 00000 - "line %s, column %s:\n%s"
      *Cause:    Usually a PL/SQL compilation error.
      *Action:

      Edited by: 1008257 on May 27, 2013 7:31 PM
        • 1. Re: Trouble with UTF File on anonymous block
          sb92075
          below mine compiles
          DECLARE 
              data_line   VARCHAR2(200); -- Data line read from input file 
              data_file   sys.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); 
              END LOOP; 
          EXCEPTION 
              WHEN no_data_found THEN 
                dbms_output.Put_line('Finished'); 
          
                utl_file.Fclose(data_file); 
          END; 
          but how/when do you ever EXIT from the LOOP?
          • 2. Re: Trouble with UTF File on anonymous block
            rp0428
            Welcome to the forum!

            Whenever you post provide your 4 digit Oracle version.
            >
            LOOP;
            >
            Lose the semicolon.
            LOOP    -- no semicolon
            • 3. Re: Trouble with UTF File on anonymous block
              1011260
              I still receive these errors when I try to compile your code. Could it be permissions related? I am logged in as SYSTEM. My version of Oracle is 11gXE

              Error report:
              ORA-06550: line 3, column 17:
              PLS-00201: identifier 'SYS.UTL_FILE' must be declared
              ORA-06550: line 3, column 17:
              PL/SQL: Item ignored
              ORA-06550: line 11, column 5:
              PLS-00320: the declaration of the type of this expression is incomplete or malformed
              ORA-06550: line 11, column 5:
              PL/SQL: Statement ignored
              ORA-06550: line 14, column 27:
              PLS-00320: the declaration of the type of this expression is incomplete or malformed
              ORA-06550: line 14, column 9:
              PL/SQL: Statement ignored
              ORA-06550: line 20, column 23:
              PLS-00320: the declaration of the type of this expression is incomplete or malformed
              ORA-06550: line 20, column 7:
              PL/SQL: Statement ignored
              06550. 00000 - "line %s, column %s:\n%s"
              *Cause:    Usually a PL/SQL compilation error.
              *Action:                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
              • 4. Re: Trouble with UTF File on anonymous block
                rp0428
                >
                I still receive these errors when I try to compile your code
                >
                My code? I don't have any code. SB provided code that compiles.
                • 5. Re: Trouble with UTF File on anonymous block
                  sb92075
                  FUNCTION FOPEN RETURNS RECORD
                   Argument Name                  Type                    In/Out Default?
                   ------------------------------ ----------------------- ------ --------
                     ID                           BINARY_INTEGER          OUT
                     DATATYPE                     BINARY_INTEGER          OUT
                     BYTE_MODE                    BOOLEAN                 OUT
                   LOCATION                       VARCHAR2                IN
                   FILENAME                       VARCHAR2                IN
                   OPEN_MODE                      VARCHAR2                IN
                   MAX_LINESIZE                   BINARY_INTEGER          IN     DEFAULT
                  http://www.lmgtfy.com/?q=oracle+utl_file+example
                  • 6. Re: Trouble with UTF File on anonymous block
                    1011260
                    Thank you SB but I'm not sure what you are trying to tell me.
                    • 7. Re: Trouble with UTF File on anonymous block
                      1011260
                      It is not the code. Other sample programs on the net using UTF do not compile for me either.

                      Edited by: 1008257 on May 27, 2013 9:33 PM

                      Edited by: 1008257 on May 27, 2013 9:35 PM
                      • 8. Re: Trouble with UTF File on anonymous block
                        jeneesh
                        Looks like permission issue..
                        Does the user have access to UTL_FILE?
                        Login as SYS and provide the access..
                        grant execute on utl_file to your_user;
                        And you cannot access OS folder like this..

                        You need to create a directory object in the database - http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_5007.htm
                        create directory test_dir as 'C:\temp';
                        grant read,write on directory test_dir to your_user;
                        
                        --And use TEST_DIR in your program..
                        
                        DECLARE
                        data_line VARCHAR2(200); -- Data line read from input file
                        data_file SYS.UTL_FILE.FILE_TYPE; -- Data file handle
                        my_dir VARCHAR2(250); -- Directory containing the data file
                        my_filename VARCHAR2(50);
                        BEGIN
                        my_dir := 'TEST_DIR'; --"use directory
                        my_filename := 'Lab4AData.dat';
                        data_file := UTL_FILE.FOPEN(my_dir, my_filename, 'r');
                        LOOP
                        UTL_FILE.GET_LINE(data_file, data_line);
                        END LOOP;
                        EXCEPTION
                        WHEN no_data_found THEN
                        DBMS_OUTPUT.PUT_LINE('Finished');
                        UTL_FILE.FCLOSE(data_file);
                        END;
                        {code}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
                        • 9. Re: Trouble with UTF File on anonymous block
                          1011260
                          I'm already logged in as SYSTEM. I confirmed with SELECT * FROM DBA_TAB_PRIVS WHERE TABLE_NAME='UTL_FILE' that SYS has execute privlidges. I also executed the command: 'create directory test_dir as 'C:\temp';' But still no change: 'UTL_FILE' must be declared
                          • 10. Re: Trouble with UTF File on anonymous block
                            jeneesh
                            1008257 wrote:
                            I'm already logged in as SYSTEM.
                            I confirmed with SELECT * FROM DBA_TAB_PRIVS WHERE TABLE_NAME='UTL_FILE' that SYS has execute privlidges.
                            Does SYSTEM has the privilege?
                            I also executed the command: 'create directory test_dir as 'C:\temp';' But still no change: 'UTL_FILE' must be declared
                            Can, you paste the latest code?

                            Use {noformat}
                            {noformat} tags to preserve formatting..                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
                            • 11. Re: Trouble with UTF File on anonymous block
                              1011260
                              Hi Jeneesh,

                              Here is the latest code:.
                              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 := 'TEST_DIR'; 
                              
                                  my_filename := 'Lab4AData.txt'; 
                              
                                  data_file := utl_file.Fopen(my_dir, my_filename, 'r'); 
                              
                                  LOOP 
                                      utl_file.Get_line(data_file, data_line); 
                                  END LOOP; 
                              EXCEPTION 
                                  WHEN no_data_found THEN 
                                    dbms_output.Put_line('Finished'); 
                              
                                    utl_file.Fclose(data_file); 
                              END; 
                              I executed the command: 'create directory test_dir as 'C:\temp';' only once on a command line. It is not included in the code above but if that is an error, please let me kn

                              If I try to grant privlidges to SYSTEM, I receive the error: SQL Error: ORA-01749: you may not GRANT/REVOKE privileges to/from yourself
                              01749. 00000 - "you may not GRANT/REVOKE privileges to/from yourself". And I don't think I ever had a SYS login.

                              Edited by: 1008257 on May 28, 2013 12:09 AM

                              Edited by: 1008257 on May 28, 2013 12:53 AM
                              • 12. Re: Trouble with UTF File on anonymous block
                                jeneesh
                                You have to login as SYS and provide privilege to SYSTEM (coz, from the error message, it looks like SYSTEM is missng the priv)
                                • 13. Re: Trouble with UTF File on anonymous block
                                  1011260
                                  But when I go to login as sys is says I have to login as sysdba. Sysdba does not like the username/password. Is it possible to login as sys? If yes, where do you do it?
                                  • 14. Re: Trouble with UTF File on anonymous block
                                    jeneesh
                                    Are you working on some test db in your desktop/laptop?

                                    Then you could try login as
                                    SQL> conn
                                    Enter user-name: / as sysdba
                                    Connected.
                                    If not, ask your DBAs to provide the required access..
                                    1 2 Previous Next