CODEWITHSUNDEEP
oracle-databaseexternalsql-loader
Jack
Jack

Reputation: 95

Loading data in external table SQLLoader

I want to crate external table based on datafile, but I got error. I use whitespace to delimit my record but this doesn't work . Yes I created directory and gave read and write permissions than I created my external table . However when I selected it I got an error:

ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error

My external table is :

create table nflteams_ext ( 
ACR varchar2(4),
NAME varchar2(20))
organization external 
(
type oracle_loader
default directory ext_tab_data
access parameters (
records delimited by newline  CHARACTERSET US7ASCII
fields terminated by whitespace 
missing field values are null
(ACR varchar2(4),
NAME varchar2(20)) 
)
LOCATION ('NFL_Teams.dat')
) 
REJECT LIMIT UNLIMITED NOPARALLEL;


data file:

 NO     New Orleans Saints 
 PIT    Pittsburgh Steelers 
 IND    Indianapolis Colts

Upvotes: 0

Views: 164

Answers (1)

Jack
Jack

Reputation: 95

The problem is solved . I used char instead of varchar2 in the lower section of my external tablecreation also I delimeted by '/n'.

 CREATE TABLE nflteams_ext
    (acr   VARCHAR2( 4),
    name  VARCHAR2(20))
    ORGANIZATION EXTERNAL
    (TYPE ORACLE_LOADER
    DEFAULT DIRECTORY ext_tab_data
    ACCESS PARAMETERS
      (RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
       FIELDS TERMINATED BY WHITESPACE
   MISSING FIELD VALUES ARE NULL
     (acr   CHAR( 4),
      name  CHAR(20) TERMINATED BY '/n'))
LOCATION ('NFL_Teams.dat'))
REJECT LIMIT UNLIMITED

NOPARALLEL /

SELECT * FROM nflteams_ext

2 /

ACR NAME

NO New Orleans Saints PIT Pittsburgh Steelers IND Indianapolis Colts

3 rows selected.

Upvotes: 0

Related Questions