This discussion is archived
4 Replies Latest reply: Feb 6, 2013 5:18 AM by Girish Sharma RSS

Upload Data From Text File

user832095 Newbie
Currently Being Moderated
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 Journeyer
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points