external table data error in toad for oracle

Question

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

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')
 );

Answer 1

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>