4 Replies Latest reply: Feb 6, 2013 7:18 AM by Girish Sharma RSS

    Upload Data From Text File

    user832095
      Hi experts

      I want to upload data from a text file in Oracle database 10g. I performed following steps

      CREATE DIRECTORY attdata AS 'c:\attdata'; OR 'c:attdata';

      GRANT read, write ON DIRECTORY attdata TO PUBLIC;

      Then I created a folder attdata on c drive and placed a text file prtext in it;

      I created following procedure
      DECLARE
      f UTL_FILE.file_type;
      s VARCHAR2(2000);

      BEGIN
      f := UTL_FILE.fopen('attdata','prtext.txt','R');
      UTL_FILE.get_line(f,s);
      UTL_FILE.fclose(f);
      DBMS_OUTPUT>PUT_LINE(s);
      END;

      When I execute this procedure, it raises following errors

      ORA-29280: Invalid Directory Path
      ORA-6512: at sys.utlfile line 33

      Can anybody solve this issue

      Regards
        • 1. Re: Upload Data From Text File
          dariyoosh
          http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_5007.htm#SQLRF01207
          >
          ... Use the CREATE DIRECTORY statement to create a directory object. A directory object specifies an alias for a directory on the server file system where external binary file LOBs (BFILEs) and external table data are located ...
          >

          As a result, unless both server and client are on the same physical machine, your CREATE DIRECTORY statement doesn't refer to any valid directory.

          Regards,
          Dariyoosh

          Edited by: dariyoosh on Feb 6, 2013 11:36 AM
          • 2. Re: Upload Data From Text File
            Girish Sharma
            Try to put ATTDATA (in caps) in place of attdata. See this demo and it worked for me :
            SQL> CREATE DIRECTORY attdata AS 'c:\attdata';
            
            Directory created.
            
            SQL> GRANT read, write ON DIRECTORY attdata TO PUBLIC;
            
            Grant succeeded.
            
            DECLARE
            f UTL_FILE.file_type;
            s VARCHAR2(2000);
            
            BEGIN
            f := UTL_FILE.fopen('ATTDATA','prtext.txt','R');
            LOOP
            BEGIN
            UTL_FILE.get_line(f,s);
            DBMS_OUTPUT.PUT_LINE(s);
                   exception
                   when no_data_found then
                   --dbms_output.put_line('file is done, so no data found');
                   exit;
            end;
            end loop;
            UTL_FILE.fclose(f);
            END;
            /
            
            111     abc
            222     xxx
            333     zzz
            
            PL/SQL procedure successfully completed.
            
            SQL>
            Regards
            Girish Sharma
            • 3. Re: Upload Data From Text File
              user832095
              Thanks for your reply

              I changed the directory name to uppercase, and this time it raises following error

              ORA-29283: invalid file operation
              ORA-06512: at "SYS.UTL_FILE", line 475
              ORA-29283: invalid file operation
              ORA-06512: at "ATTDATA", line 5
              ORA-06512: at line 1

              I am able to make spool file in sqlplus but getting this error
              • 4. Re: Upload Data From Text File
                Girish Sharma
                Post the output of below command :
                SQL>host dir c:\attdata\prtext.txt  <--- The folder should name be same as you said when you create directory ... command and the file which you are going to open.
                Either you are using different directory object or file does not exits in your directory object, because when I am able to view the file (as I showed above), you should too be able to run the code. Also check the OS user which is currently logged into sqlplus or calling PL/SQL object, having correct permission to access the folder and file.

                Regards
                Girish Sharma