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 Go to next message
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 524 times)
Re: External table select error [message #484806 is a reply to message #484805] Thu, 02 December 2010 20:14 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Decent first post! What ever the error it is not obvious (at least to me for now.)

post the results of following OS command
ls -l /u01/app/oracle/

post the results of following SQL from a new/different session.
SELECT DIRECTORY_NAME from DBA_DIRECTORIES;
Re: External table select error [message #484807 is a reply to message #484806] Thu, 02 December 2010 20:31 Go to previous messageGo to next message
timvand
Messages: 7
Registered: December 2005
Location: NorCal
Junior Member

When one follows the manual, well, the results show as advertised. Smile

Here is the ls output:
[oracle@actidb01 xtrnl_tbls]$ ls -l /u01/app/oracle/
total 20
drwxr-xr-x  3 oracle dba      4096 Dec  2 13:34 dump
drwxr-xr-x  8 oracle dba      4096 Jun 25 23:00 oraInventory
drwxr-xr-x  6 oracle oinstall 4096 Jun 25 22:58 product
drwxr-xr-x  8 oracle dba      4096 Jun 25 23:06 stage
drwxr-xr-x  2 oracle dba      4096 Dec  2 16:43 xtrnl_tbls
[oracle@actidb01 xtrnl_tbls]$



And here is the DIRECTORY_NAME output:
SQL> SELECT DIRECTORY_NAME from DBA_DIRECTORIES;

DIRECTORY_NAME
------------------------------
XTRNL_TBLS_DIR
LOG_FILE_DIR
DATA_FILE_DIR
MEDIA_DIR
SUBDIR
XMLDIR
WORK_DIR
ADMIN_DIR
ORACLE_OCM_CONFIG_DIR
DATA_PUMP_DIR

10 rows selected.

SQL>

Re: External table select error [message #484808 is a reply to message #484807] Thu, 02 December 2010 20:37 Go to previous messageGo to next message
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 #484809 is a reply to message #484808] Thu, 02 December 2010 20:41 Go to previous messageGo to next message
timvand
Messages: 7
Registered: December 2005
Location: NorCal
Junior Member

What is this line referring to?
KUP-01007: at line 6 column 21
Re: External table select error [message #484810 is a reply to message #484809] Thu, 02 December 2010 20:52 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
6	   STREETADDRESS  VARCHAR2(100),

I am still befuddled WRT what is actually wrong.
Re: External table select error [message #484811 is a reply to message #484810] Thu, 02 December 2010 20:55 Go to previous messageGo to next message
timvand
Messages: 7
Registered: December 2005
Location: NorCal
Junior Member

Agreed. Digging through metalink as well. And I quote "However, the error message is a little vague about where the problem truly is within the statement."
Re: External table select error [message #484812 is a reply to message #484811] Thu, 02 December 2010 20:58 Go to previous messageGo to next message
timvand
Messages: 7
Registered: December 2005
Location: NorCal
Junior Member

The one suggestion on ml is to do the create and then select one column at a time in order to identify the issue. One possibility. Your thoughts?
Re: External table select error [message #484813 is a reply to message #484812] Thu, 02 December 2010 21:03 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>And I quote "However, the error message is a little vague about where the problem truly is within the statement."
Frequently the actual error is at the line indicated or "just" above identified line.
I see nothing obviously wrong at this time.
Re: External table select error [message #484819 is a reply to message #484813] Thu, 02 December 2010 21:16 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8922
Registered: November 2002
Location: California, USA
Senior Member
The second section with all of the fields should have datatypes like CHAR and EXTERNAL INTEGER and so forth, as are used with SQL*Loader, not the same SQL datatypes in the upper section with all the fields, like VARCHAR2.

[Updated on: Thu, 02 December 2010 21:16]

Report message to a moderator

Re: External table select error [message #484820 is a reply to message #484819] Thu, 02 December 2010 21:41 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8922
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> 


Re: External table select error [message #484822 is a reply to message #484821] Thu, 02 December 2010 21:55 Go to previous message
timvand
Messages: 7
Registered: December 2005
Location: NorCal
Junior Member

Barbara,

Got it! THANK YOU!!!

[Updated on: Thu, 02 December 2010 21:58]

Report message to a moderator

Previous Topic: oracle table export hangs
Next Topic: import failed
Goto Forum:
  


Current Time: Fri Sep 17 08:34:36 CDT 2021