Home » Developer & Programmer » Data Integration » creation of external table (Oracle 11g)
creation of external table [message #635374] Sat, 28 March 2015 08:43 Go to next message
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
Re: creation of external table [message #635377 is a reply to message #635374] Sat, 28 March 2015 10:19 Go to previous messageGo to next message
Michel Cadot
Messages: 65252
Registered: March 2007
Location: Nanterre, France, http://...
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.
Re: creation of external table [message #635379 is a reply to message #635377] Sat, 28 March 2015 14:59 Go to previous messageGo to next message
Littlefoot
Messages: 21265
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?
Re: creation of external table [message #635380 is a reply to message #635379] Sat, 28 March 2015 20:06 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8779
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.

Re: creation of external table [message #635449 is a reply to message #635380] Mon, 30 March 2015 09:27 Go to previous messageGo to next message
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).
Re: creation of external table [message #635451 is a reply to message #635449] Mon, 30 March 2015 09:53 Go to previous messageGo to next message
dmitry1986
Messages: 4
Registered: March 2015
Location: Torornto
Junior Member
OH Sorry Barbara Sad. I confused your name. Thanks a lot for your desire to help again
Re: creation of external table [message #635454 is a reply to message #635451] Mon, 30 March 2015 10:11 Go to previous message
Michel Cadot
Messages: 65252
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Next time, please answer our question, does not just wait for a complete solution (which may not come as most of us will just wait for you).

Previous Topic: IMPDP utility TRANSPORT_DATAFILES
Next Topic: Loading Data to MOLAP Cubes
Goto Forum:
  


Current Time: Wed Dec 13 11:47:19 CST 2017

Total time taken to generate the page: 0.11279 seconds