OraFAQ Forum: Data Integration » creation of external table

Home » Developer & Programmer » Data Integration » creation of external table (Oracle 11g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

creation of external table [message #635374]

Sat, 28 March 2015 08:43

dmitry1986
Messages: 4
Registered: March 2015
Location: Torornto

Junior Member

I want to crate external table based on datafile, but I got error. I use whitespace to delimit my record but this doesn 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

Report message to a moderator

Re: creation of external table [message #635377 is a reply to message #635374]

Sat, 28 March 2015 10:19

Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

ORA-29400: data cartridge error\n%s
 *Cause:  An error has occurred in a data cartridge external procedure.
          This message will be followed by a second message giving
          more details about the data cartridge error.
 *Action: See the data cartridge documentation
          for an explanation of the second error message.

Report message to a moderator

Re: creation of external table [message #635379 is a reply to message #635377]

Sat, 28 March 2015 14:59

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

In the meantime (while waiting for a second message): where did you create a directory? Should be on a database server. Is it there, or is it on your own PC?

Report message to a moderator

Re: creation of external table [message #635380 is a reply to message #635379]

Sat, 28 March 2015 20:06

Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA

Senior Member

You need to use CHAR instead of VARCHAR2 in the lower section of your external table creation. Also, if you just delimit by whitespace, then only the first word of the second column will be loaded, so you need to delimit that field by '/n'. Please see the reproduction of the problem and correction below.

-- test data file and directory object:

SCOTT@orcl12c> HOST TYPE c:\my_oracle_files\nfl_teams.dat
NO New Orleans Saints
PIT Pittsburgh Steelers
IND Indianapolis Colts

SCOTT@orcl12c> CREATE OR REPLACE DIRECTORY ext_tab_data AS 'c:\my_oracle_files'
  2  /

Directory created.

-- reproduction of problem (your code):

SCOTT@orcl12c> create table nflteams_ext (
  2  ACR varchar2(4),
  3  NAME varchar2(20))
  4  organization external
  5  (
  6  type oracle_loader
  7  default directory ext_tab_data
  8  access parameters (
  9  records delimited by newline CHARACTERSET US7ASCII
 10  fields terminated by whitespace
 11  missing field values are null
 12  (ACR varchar2(4),
 13  NAME varchar2(20))
 14  )
 15  LOCATION ('NFL_Teams.dat')
 16  )
 17  REJECT LIMIT UNLIMITED NOPARALLEL
 18  /

Table created.

SCOTT@orcl12c> SELECT * FROM nflteams_ext
  2  /
SELECT * FROM nflteams_ext
*
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-00554: error encountered while parsing access parameters
KUP-01005: syntax error: found "identifier": expecting one of: "binary_double,
binary_float, comma, char, date, defaultif, decimal, double, float, integer, (,
no, nullif, oracle_date, oracle_number, position, raw, recnum, ), unsigned,
varrawc, varchar, varraw, varcharc, zoned"
KUP-01008: the bad identifier was: varchar2
KUP-01007: at line 4 column 6

-- correction:

SCOTT@orcl12c> DROP TABLE nflteams_ext
  2  /

Table dropped.

SCOTT@orcl12c> CREATE TABLE nflteams_ext
  2    (acr   VARCHAR2( 4),
  3  	name  VARCHAR2(20))
  4  ORGANIZATION EXTERNAL
  5    (TYPE ORACLE_LOADER
  6  	DEFAULT DIRECTORY ext_tab_data
  7  	ACCESS PARAMETERS
  8  	  (RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
  9  	   FIELDS TERMINATED BY WHITESPACE
 10  	   MISSING FIELD VALUES ARE NULL
 11  	     (acr   CHAR( 4),
 12  	      name  CHAR(20) TERMINATED BY '/n'))
 13  	LOCATION ('NFL_Teams.dat'))
 14  REJECT LIMIT UNLIMITED
 15  NOPARALLEL
 16  /

Table created.

SCOTT@orcl12c> SELECT * FROM nflteams_ext
  2  /

ACR  NAME
---- --------------------
NO   New Orleans Saints
PIT  Pittsburgh Steelers
IND  Indianapolis Colts

3 rows selected.

Report message to a moderator

Re: creation of external table [message #635449 is a reply to message #635380]

Mon, 30 March 2015 09:27

dmitry1986
Messages: 4
Registered: March 2015
Location: Torornto

Junior Member

Thanks a lot Michel ( for your time and desire to help)! Your response really helped me a lot . I am sorry for being so rude ( I am new on this forum).

Report message to a moderator