Home » RDBMS Server » Server Utilities » External table select error (10.2.0.4, OEL 5.0)
| External table select error [message #484805] |
Thu, 02 December 2010 20:02  |
timvand
Messages: 7
Registered: December 2005
Location: NorCal
|
Junior Member |
|
|
I receive the following error message
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, (,
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 6 column 21
ORA-06512: at "SYS.ORACLE_LOADER", line 19
when I select count(*) on the external table created below.
SQL> CREATE TABLE cac_500_load
2 (
3 EMAILADDRESS VARCHAR2(80),
4 FIRSTNAME VARCHAR2(60),
5 LASTNAME VARCHAR2(60),
6 STREETADDRESS VARCHAR2(100),
7 CITY VARCHAR2(50),
8 STATE VARCHAR2(30),
9 ZIP NUMBER,
10 PHONE NUMBER,
11 UPDATEDON DATE,
12 IPADDRESS VARCHAR2(40),
13 SITE VARCHAR2(100),
14 GENDER VARCHAR2(30),
15 DOB VARCHAR2(40)
16 )
17 ORGANIZATION EXTERNAL
18 (
19 TYPE ORACLE_LOADER
20 DEFAULT DIRECTORY xtrnl_tbls_dir
21 ACCESS PARAMETERS
22 (
23 RECORDS DELIMITED BY NEWLINE
24 FIELDS TERMINATED BY ','
25 OPTIONALLY ENCLOSED BY '"'
26 MISSING FIELD VALUES ARE NULL
27 (
28 EMAILADDRESS VARCHAR2(80),
29 FIRSTNAME VARCHAR2(60),
30 LASTNAME VARCHAR2(60),
31 STREETADDRESS VARCHAR2(100),
32 CITY VARCHAR2(50),
33 STATE VARCHAR2(30),
34 ZIP NUMBER,
35 PHONE NUMBER,
36 UPDATEDON DATE MASK "MM/DD/YYYY",
37 IPADDRESS VARCHAR2(40),
38 SITE VARCHAR2(100),
39 GENDER VARCHAR2(30),
40 DOB VARCHAR2(40),
41 )
42 )
43 LOCATION ('cac-500.csv')
44 )
45 ;
Table created.
SQL>
Here is the db version info:
SQL> select * from v$instance;
INSTANCE_NUMBER INSTANCE_NAME
--------------- ----------------
HOST_NAME
----------------------------------------------------------------
VERSION STARTUP_T STATUS PAR THREAD# ARCHIVE LOG_SWITCH_WAIT
----------------- --------- ------------ --- ---------- ------- ---------------
LOGINS SHU DATABASE_STATUS INSTANCE_ROLE ACTIVE_ST BLO
---------- --- ----------------- ------------------ --------- ---
1 orcl01
actidb01.actihq.net
10.2.0.4.0 01-DEC-10 OPEN NO 1 STARTED
ALLOWED NO ACTIVE PRIMARY_INSTANCE NORMAL NO
SQL>
And access to the directory where the file resides:
SQL> create or replace directory xtrnl_tbls_dir
2 as '/u01/app/oracle/xtrnl_tbls'
3 ;
Directory created.
What am I missing here or where have I made a mistake? I have uploaded a sample of the csv as well.
Thanks in advance for any and all suggestions / support.
Cheers,
T
-
Attachment: cac-500.csv
(Size: 3.44KB, Downloaded 2515 times)
|
|
|
|
|
|
|
|
| Re: External table select error [message #484808 is a reply to message #484807] |
Thu, 02 December 2010 20:37  |
 |
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
|
Senior Member |
|
|
I can more or less reproduce what you posted
1 CREATE TABLE cac_500_load
2 (
3 EMAILADDRESS VARCHAR2(80),
4 FIRSTNAME VARCHAR2(60),
5 LASTNAME VARCHAR2(60),
6 STREETADDRESS VARCHAR2(100),
7 CITY VARCHAR2(50),
8 STATE VARCHAR2(30),
9 ZIP NUMBER,
10 PHONE NUMBER,
11 UPDATEDON DATE,
12 IPADDRESS VARCHAR2(40),
13 SITE VARCHAR2(100),
14 GENDER VARCHAR2(30),
15 DOB VARCHAR2(40)
16 )
17 ORGANIZATION EXTERNAL
18 (
19 TYPE ORACLE_LOADER
20 DEFAULT DIRECTORY xtrnl_tbls_dir
21 ACCESS PARAMETERS
22 (
23 RECORDS DELIMITED BY NEWLINE
24 FIELDS TERMINATED BY ','
25 OPTIONALLY ENCLOSED BY '"'
26 MISSING FIELD VALUES ARE NULL
27 (
28 EMAILADDRESS VARCHAR2(80),
29 FIRSTNAME VARCHAR2(60),
30 LASTNAME VARCHAR2(60),
31 STREETADDRESS VARCHAR2(100),
32 CITY VARCHAR2(50),
33 STATE VARCHAR2(30),
34 ZIP NUMBER,
35 PHONE NUMBER,
36 UPDATEDON DATE MASK "MM/DD/YYYY",
37 IPADDRESS VARCHAR2(40),
38 SITE VARCHAR2(100),
39 GENDER VARCHAR2(30),
40 DOB VARCHAR2(40),
41 )
42 )
43 LOCATION ('cac-500.csv')
44* )
SQL> /
Table created.
SQL> select count(*) from cac_500_load
2 ;
select count(*) from cac_500_load
*
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, (,
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 6 column 23
Since Oracle is too dumb to mis-report reality, now I need to determine what I/we did not correctly.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: External table select error [message #484820 is a reply to message #484819] |
Thu, 02 December 2010 21:41 |
timvand
Messages: 7
Registered: December 2005
Location: NorCal
|
Junior Member |
|
|
Barbara,
Like this?
CREATE TABLE cac_500_load
(
EMAILADDRESS VARCHAR2(80),
FIRSTNAME VARCHAR2(60),
LASTNAME VARCHAR2(60),
STREETADDRESS VARCHAR2(100),
CITY VARCHAR2(50),
STATE VARCHAR2(30),
ZIP NUMBER,
PHONE NUMBER,
UPDATEDON DATE,
IPADDRESS VARCHAR2(40),
SITE VARCHAR2(100),
GENDER VARCHAR2(30),
DOB VARCHAR2(40)
)
ORGANIZATION EXTERNAL
(
TYPE ORACLE_LOADER
DEFAULT DIRECTORY xtrnl_tbls_dir
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
MISSING FIELD VALUES ARE NULL
(
EMAILADDRESS CHAR(80),
FIRSTNAME CHAR(60),
LASTNAME CHAR(60),
STREETADDRESS CHAR(100),
CITY CHAR(50),
STATE CHAR(30),
ZIP NUMBER,
PHONE NUMBER,
UPDATEDON DATE MASK "MM/DD/YYYY",
IPADDRESS CHAR(40),
SITE CHAR(100),
GENDER CHAR(30),
DOB CHAR(40),
)
)
LOCATION ('cac-500.csv')
)
;
What about the date?
UPDATEDON DATE MASK "MM/DD/YYYY", |
|
|
|
| Re: External table select error [message #484821 is a reply to message #484820] |
Thu, 02 December 2010 21:51 |
 |
Barbara Boehmer
Messages: 9102
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
SCOTT@orcl_11gR2> create or replace directory xtrnl_tbls_dir as 'c:\my_oracle_files'
2 /
Directory created.
SCOTT@orcl_11gR2> CREATE TABLE cac_500_load
2 (
3 EMAILADDRESS VARCHAR2(80),
4 FIRSTNAME VARCHAR2(60),
5 LASTNAME VARCHAR2(60),
6 STREETADDRESS VARCHAR2(100),
7 CITY VARCHAR2(50),
8 STATE VARCHAR2(30),
9 ZIP NUMBER,
10 PHONE NUMBER,
11 UPDATEDON DATE,
12 IPADDRESS VARCHAR2(40),
13 SITE VARCHAR2(100),
14 GENDER VARCHAR2(30),
15 DOB VARCHAR2(40)
16 )
17 ORGANIZATION external
18 (
19 TYPE oracle_loader
20 DEFAULT DIRECTORY xtrnl_tbls_dir
21 ACCESS PARAMETERS
22 (
23 RECORDS DELIMITED BY NEWLINE
24 FIELDS TERMINATED BY ','
25 OPTIONALLY ENCLOSED BY '"'
26 REJECT ROWS WITH ALL NULL FIELDS
27 (
28 "EMAILADDRESS" CHAR(80),
29 "FIRSTNAME" CHAR(60),
30 "LASTNAME" CHAR(60),
31 "STREETADDRESS" CHAR(100),
32 "CITY" CHAR(50),
33 "STATE" CHAR(30),
34 "ZIP" CHAR(255),
35 "PHONE" CHAR(255),
36 "UPDATEDON" CHAR(255) DATE_FORMAT DATE MASK 'MM/DD/YYYY hh24:mi',
37 "IPADDRESS" CHAR(40),
38 "SITE" CHAR(100),
39 "GENDER" CHAR(30),
40 "DOB" CHAR(40)
41 )
42 )
43 LOCATION ('cac-500.csv')
44 ) REJECT LIMIT UNLIMITED
45 /
Table created.
SCOTT@orcl_11gR2> SELECT * FROM cac_500_load
2 /
EMAILADDRESS
--------------------------------------------------------------------------------
FIRSTNAME
------------------------------------------------------------
LASTNAME
------------------------------------------------------------
STREETADDRESS
--------------------------------------------------------------------------------
CITY
--------------------------------------------------
STATE ZIP PHONE UPDATEDON
------------------------------ ---------- ---------- ---------
IPADDRESS
----------------------------------------
SITE
--------------------------------------------------------------------------------
GENDER DOB
------------------------------ ----------------------------------------
sosick_9@yahoo.com
Kristin
Conroy
2803 Westbranch Dr
San Jose
CA 95148 4088243385 27-DEC-09
192.82.175.10
popularliving.com
\N \N
aberudzhanyan@ameritech.net
Annette
Berudzhanyan
6350 Colbath Ave
Van Nuys
CA 91401 8186816233 26-DEC-09
67.59.169.56
cbsmarketwatch.com
\N \N
jodulio@bellsouth.net
JOEL
ODULIO
717 HAWAIIAN AVE
WILMINGTON
CA 90744 3102008453 22-DEC-09
69.178.187.45
morningstar.com
\N \N
mstimson@juno.com
KAREN
STIMSON
40334 RACQUET LN
PALMDALE
CA 93551 6619656223 25-DEC-09
208.69.250.254
cbsmarketwatch.com
\N \N
raeleen.riedell@yahoo.com
RAELEEN
RIEDELL
7563 CALAIS CT
RANCHO CUCAMONGA
CA 91730 7146556736 24-DEC-09
72.252.78.221
expedia.com
\N \N
smakaryan@hotmail.com
SAHAK
MAKARYAN
6250 ALCOVE AVE
NORTH HOLLYWOOD
CA 91606 8186314432 25-DEC-09
71.10.249.45
eventful.com
\N \N
lorena.magana@myway.com
LORENA
MAGANA
PO BOX 2412
CERES
CA 95307 2099884588 24-DEC-09
162.0.79.39
cbsmarketwatch.com
\N \N
daniel.gerpheide@netzero.net
DANIEL
GERPHEIDE
13521 E ASHLAN AVE
SANGER
CA 93657 5599054625 22-DEC-09
195.112.191.118
morningstar.com
\N \N
hobbiehenry@yahoo.com
karen
norris
205 s. lemon ave
azusa
CA 91702 6266734560 26-DEC-09
69.90.2.95
cbsmarketwatch.com
\N \N
jmontero@att.net
JOEL
MONTERO
422 PETALUMA BLVD N
PETALUMA
CA 94952 7072179979 23-DEC-09
65.39.195.248
cbsmarketwatch.com
\N \N
shadyboggs_nays@yahoo.com
susanne
boggs
7353 saladin ave
twentynine palms
ca 92277 7603673602 20-APR-10
205.154.158.128
netflix.com
f 5/26/1987
shaeestrada@yahoo.com
shae
estrada
5574 w homecoming circle
mira loma
ca 91752 9517270419 20-APR-10
99.162.100.144
123freetravel.com
f 8/23/1972
shaegonz24@yahoo.com
shannon
gonzlaez
1621 orange st
redlands
ca 92374 9097937156 20-APR-10
72.87.179.193
hulu.com
shafertony@aol.com
tony
shafer
6429 jasper
altaloma
ca 91701 9099878840 20-APR-10
71.104.119.241
buy.com
m 1/17/1965
mathew.soto@aol.com
mathew
soto
1455 e date st
san bernardino
ca 92404 8186347909 30-JUL-10
66.165.90.194
asseenontv.com
mathew.theriault@att.net
mathew
theriault
6301 ridgemar ct
rancho palos verdes
ca 90275 3233634913 31-JUL-10
69.90.110.154
ning.com
mathewsjack@hotmail.com
jack
mathews
68 peninsula rd
belvedere
ca 94920 4156373633 31-JUL-10
71.139.187.77
buy.com
m 8/7/1955
matilda.martinez@att.net
matilda
martinez
490 w. minarets
fresno
ca 93650 5595792496 31-JUL-10
200.58.50.90
btobfranchise.com
matilde.caro@aol.com
matilde
caro
2507 e oris st
compton
ca 90222 3105038352 30-JUL-10
65.39.192.98
buy.com
matinrahasaf@yahoo.com
mojgan
matin
2717 wheatstone st
san diego
ca 92111 8582925345 31-JUL-10
24.165.6.107
ecoupons.com
f 2/29/1988
matlen1@yahoo.com
madi
galstian
16408 flanders st
granada hills
ca 91344 8184212119 31-JUL-10
192.188.131.87
tagged
21 rows selected.
SCOTT@orcl_11gR2>
|
|
|
|
|
|
Goto Forum:
Current Time: Sat Jan 11 02:27:08 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
