Link to home
Start Free TrialLog in
Avatar of dplinnane
dplinnaneFlag for United States of America

asked on

EXTERNAL TABLES ora-02031 no rowid for fixed tables or for external-organised

The following executed ok but when I go to view the data in toad I get the above error message.
Here is a sample of my flat file 2 records.   Not sure what going on.
1~130.00~`130-Peachland`~1300117.00~2/19/2004 0:00:00~80266.00~11.95~1.00~301.00~`C&C Cooler Rental`~`Cooler Rentals                `~`Res`
2~130.00~`130-Peachland`~1300174.00~2/19/2004 0:00:00~80267.00~42.00~6.00~150.00~`WI Purified Water 5 Gallon`~`Water Delivered               `~`Res`

create table TMP_CUSTOMER (
CUST_ID             VARCHAR2(100),
FRAN_NO        VARCHAR2(100),
COMPANY        VARCHAR2(100),
CUS_NUM        VARCHAR2(100),
INV_DATE       VARCHAR2(100),
INV_NUM        VARCHAR2(100),
LINETOTAL      VARCHAR2(100),
QUANTITY       VARCHAR2(100),
PRODUCT_CODE   VARCHAR2(100),
PROD_NAME      VARCHAR2(100),
PROD_CATEGORY  VARCHAR2(100),
TYPE_DSC           VARCHAR2(100)
 )
Organization external(
type oracle_loader
default directory ExternalTables
access parameters
(records delimited by newline
badfile 'EmpBad.bad'
logfile 'EmpLog.log'
FIELDS TERMINATED BY "~"  OPTIONALLY ENCLOSED BY '`'
)
location ('Sales2.txt'))
reject limit 1000;

The script generated by toad follows. This indictaes table created but not loaded.
DROP TABLE TMP_CUSTOMER CASCADE CONSTRAINTS;

CREATE TABLE TMP_CUSTOMER
(
  CUST_ID        VARCHAR2(100),
  FRAN_NO        VARCHAR2(100),
  COMPANY        VARCHAR2(100),
  CUS_NUM        VARCHAR2(100),
  INV_DATE       VARCHAR2(100),
  INV_NUM        VARCHAR2(100),
  LINETOTAL      VARCHAR2(100),
  QUANTITY       VARCHAR2(100),
  PRODUCT_CODE   VARCHAR2(100),
  PROD_NAME      VARCHAR2(100),
  PROD_CATEGORY  VARCHAR2(100),
  TYPE_DSC       VARCHAR2(100)
)
ORGANIZATION EXTERNAL
  (  TYPE ORACLE_LOADER
     DEFAULT DIRECTORY EXTERNALTABLES
     ACCESS PARAMETERS
       ( records delimited by newline
badfile 'EmpBad.bad'
logfile 'EmpLog.log'
FIELDS TERMINATED BY "~"  OPTIONALLY ENCLOSED BY '`'
 )
     LOCATION (EXTERNALTABLES:'Sales2.txt')
  )
REJECT LIMIT 1000
LOGGING
NOCACHE
NOPARALLEL;
Avatar of ishando
ishando
Flag of Ireland image

What version of toad are you using? This works fine for me using version 7.6.0.11 on Oracle 9.2.0.4
Avatar of dplinnane

ASKER

TOAD 7.4.0.3
ORACLE Release 9.2.0.1.0

I can run the following example and it works fine, so I don't think its  a toad or oracle issue.

create table emp_ext (
EMPNO  NUMBER(4),
ENAME  VARCHAR2(10),
JOB   VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL    NUMBER(7,2),
 COMM NUMBER(7,2),
 DEPTNO NUMBER(2))
Organization external(
type oracle_loader
default directory ExternalTables
access parameters
(records delimited by newline
badfile 'EmpBad.bad'
logfile 'EmpLog.log'
fields terminated by ','
)
location ('testdb.csv'))
reject limit 1000;
ASKER CERTIFIED SOLUTION
Avatar of boriskalavsky
boriskalavsky

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Good Job that's it.