External table creation in oracle using csv from ftp location

Question

I am trying to load the csv file data into external_tables for this i have tried

  create table ext_table_csv (
      i   Number,
      n   Varchar2(20),
      m   Varchar2(20)
    )
    organization external (
      type              oracle_loader
      default directory ext_dir
      access parameters (
        records delimited  by newline
        fields  terminated by ','
        missing field values are null
      )
      location ('f

ile.csv')
)
reject limit unlimited;

but i got error as

Error starting at line 8 in command:

    create table ext_table_csv (
      i   Number,
      n   Varchar2(20),
      m   Varchar2(20)
    )
    organization external (
      type              oracle_loader
      default directory ext_dir
      access parameters (
        records delimited  by newline
        fields  terminated by ','
        missing field values are null
      )
      location ('f

ile.csv')
)
reject limit unlimited


Error at Command Line:15 Column:23
Error report:

    SQL Error: ORA-06564: object DATA_DIR does not exist
    06564. 00000 -  "object %s does not exist"
    *Cause:    The named object could not be found.  Either it does not exist
               or you do not have permission to access it.
    *Action:   Create the object or get permission to access it.

what i have to do is i want to read the csv file from ftp location. Is there any way to acheive this?

How come create statement has `default directory ext_dir` but error is `default directory data_dir` ? Did you created Oracle Directory before creating external table? — San, Mar 04 '14 at 13:13

score 0 · Answer 1 · answered Mar 04 '14 at 13:13

0

You probably need to specify the location of the file.

 location (FTP_DIRECTORY:'emp.dat')

Where FTP_DIRECTORY is an Oracle Directory object pointing to the location where you placed the file.

answered Mar 04 '14 at 13:13

Rene

10,391
5
33
46

I am in client side don't have permission to create directory in server side means shall i give my directory path C:/temp – jackyesind Mar 04 '14 at 13:19
@jackyesind - the directory *has* to be server-side. You can't have an external table where the file isn't visible to the Oracle server (on a local, or mounted-remote, filesystem). If you can't move the file to the server you can use SQL*Loader from the client, loading into a normal table. – Alex Poole Mar 04 '14 at 13:58

External table creation in oracle using csv from ftp location

1 Answers1