0

I am trying to create an external table in toad but getting the error shown below: enter image description here

Here is my code for the external table, it is executed successfully but when I click on the data tab in toad it gives error as shown in the above screenshot.

CREATE TABLE emp_load
  (          country_id      CHAR(5),
             country_name    VARCHAR(50),
             region_id   number
  )
ORGANIZATION EXTERNAL
  (TYPE ORACLE_LOADER
  DEFAULT DIRECTORY OUTER
  ACCESS PARAMETERS
    (RECORDS DELIMITED BY NEWLINE
     FIELDS (country_id      CHAR(2),
             country_name    VARCHAR(40),
             region_id   number

           )
   )
  LOCATION ('externalfile1.csv')
 );
Wernfried Domscheit
  • 54,457
  • 9
  • 76
  • 110
zain ul abidin
  • 197
  • 2
  • 13

1 Answers1

0

Here's an example which, actually, works. See if it helps.

My CSV file:

HR,Croatia,385
SLO,Slovenia,386

Create external table - don't forget to

  • create directory (as Oracle object, using SYS account)
  • grant read (and write?) privileges on that directory to user who will be using it

.

SQL> create table emp_load
  2    (country_id      varchar2(5),
  3     country_name    varchar2(50),
  4     region_id       varchar2(5)
  5    )
  6  organization external
  7    (type oracle_loader
  8     default directory ext_dir
  9     access parameters
 10       (records delimited by newline
 11        fields terminated by ','
 12          (country_id      char(5),
 13           country_name    char(50),
 14           region_id       char(5)
 15          )
 16       )
 17     location ('externalfile1.txt')
 18    )
 19  reject limit unlimited;

Table created.

SQL> select * from emp_load;

COUNT COUNTRY_NAME                                       REGIO
----- -------------------------------------------------- -----
HR    Croatia                                            385
SLO   Slovenia                                           384

SQL>
Littlefoot
  • 131,892
  • 15
  • 35
  • 57
  • shouldn't the file type be "csv" in location ('externalfile1.txt') – zain ul abidin Oct 10 '18 at 04:43
  • It doesn't matter, @zain. I prefer TXT as I don't have to change file extension and answer annoying "are you sure ...?" question when creating a file. It is the contents that matters, not extension. Could have been ".xyz" or ".littlefoot", it would still work. – Littlefoot Oct 10 '18 at 04:58
  • it is now working nicely......data types were the issues thanks for guidance and I had to put the correct extension of file "externalfile1.csv" – zain ul abidin Oct 10 '18 at 06:12
  • You are welcome. Of course, if your filename has a different extension, you have to use it. I am glad if it helped. – Littlefoot Oct 10 '18 at 07:22