Home » RDBMS Server » Server Utilities » External Table (11g)
External Table [message #670005] Tue, 29 May 2018 07:25 Go to next message
sgt_spike
Messages: 4
Registered: February 2018
Junior Member
I am trying to see the data in my external table's data file. I was able to create the external table and see it in Toad. However, when I try the query the data inside the data file I get several errors (listed below). I've search the net and found nothing to address this error

ORA-29913: Error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-00554: error encountered while parsing access parameters
KUP-01006: error signaled during parse of access parameters
KUP-00562: unknown escape sequence

DROP TABLE my_schema.myexternaltable;

CREATE TABLE my_schema.myexternaltable
(
  site                NUMBER(5),
  check_nbr           NUMBER(6),
  check_date          DATE,
  amount              NUMBER,
  first_name          VARCHAR2(25),
  mid_init            CHAR(1),
  last_name           VARCHAR2(35),
  address_line        VARCHAR2(40),
  address_city        VARCHAR2(30),
  address_state       CHAR(2),
  address_country     VARCHAR2(30),
  address_zip         VARCHAR2(10),
  ssn                 VARCHAR2(9),
  do_code             CHAR(2),
  acctable_user_code  CHAR(3)
)
ORGANIZATION EXTERNAL
  (  TYPE ORACLE_LOADER
     DEFAULT DIRECTORY file_dir
     ACCESS PARAMETERS 
       (RECORDS DELIMITED BY NEWLINE
       BADFILE file_dir:'\load\badfile\check4cash.bad'
       DISCARDFILE file_dir:'\load\discardfile\check4cash.dsc'
       LOGFILE file_dir:'\load\logfile\check4cash.log'
       SKIP 0
       FIELDS TERMINATED BY ','
       OPTIONALLY ENCLOSED BY '"' AND '"'
       MISSING FIELD VALUES ARE NULL
       REJECT ROWS WITH ALL NULL FIELDS
       (
         site CHAR(5),
         check_nbr CHAR(6),
         check_date CHAR(10) DATE_FORMAT DATE MASK "mm/dd/yyyy",
         amount CHAR,
         first_name CHAR(25),
         mid_init CHAR(1),
         last_name CHAR(35),
         address_line CHAR(40),
         address_city CHAR(30),
         address_state CHAR(2),
         address_country CHAR(30),
         address_zip CHAR(10),
         ssn CHAR(9),
         do_code CHAR(2),
         acctable_user_code CHAR(3)
       ) )
     LOCATION (dds_file_dir:'\load\CheckForCash.txt')
  )
REJECT LIMIT UNLIMITED
NOPARALLEL
NOMONITORING
/
Re: External Table [message #670006 is a reply to message #670005] Tue, 29 May 2018 07:51 Go to previous messageGo to next message
BlackSwan
Messages: 26585
Registered: January 2009
Location: SoCal
Senior Member
>amount CHAR,
Why no length for column above?
Re: External Table [message #670008 is a reply to message #670006] Tue, 29 May 2018 08:02 Go to previous messageGo to next message
sgt_spike
Messages: 4
Registered: February 2018
Junior Member
Must have been Toad when creating the external table. Changing it to add a length doesn't solve the problem
Re: External Table [message #670012 is a reply to message #670008] Tue, 29 May 2018 09:50 Go to previous message
sgt_spike
Messages: 4
Registered: February 2018
Junior Member
I got it to work. I took out the extra characters in the badfile, discardfile, and logfile access parameters

ORGANIZATION EXTERNAL
  (  TYPE ORACLE_LOADER
     DEFAULT DIRECTORY file_dir
     ACCESS PARAMETERS 
       (RECORDS DELIMITED BY NEWLINE
       BADFILE file_dir:'check4cash.bad'
       DISCARDFILE file_dir:'check4cash.dsc'
       LOGFILE file_dir:'check4cash.log'
       SKIP 0
       FIELDS TERMINATED BY ','
       OPTIONALLY ENCLOSED BY '"' AND '"'
       MISSING FIELD VALUES ARE NULL
       REJECT ROWS WITH ALL NULL FIELDS
       (
         site CHAR(5),
         check_nbr CHAR(6),
         check_date CHAR(10) DATE_FORMAT DATE MASK "mm/dd/yyyy",
         amount CHAR,
         first_name CHAR(25),
         mid_init CHAR(1),
         last_name CHAR(35),
         address_line CHAR(40),
         address_city CHAR(30),
         address_state CHAR(2),
         address_country CHAR(30),
         address_zip CHAR(10),
         ssn CHAR(9),
         do_code CHAR(2),
         acctable_user_code CHAR(3)
       ) )
     LOCATION (dds_file_dir:'\load\CheckForCash.txt')
  )
REJECT LIMIT UNLIMITED
NOPARALLEL
NOMONITORING
/

Previous Topic: SQL Loader
Next Topic: Multibyte shift columns
Goto Forum:
  


Current Time: Mon Aug 19 17:31:10 CDT 2019