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;
dplinnaneAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ishandoCommented:
What version of toad are you using? This works fine for me using version 7.6.0.11 on Oracle 9.2.0.4
0
dplinnaneAuthor Commented:
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;
0
boriskalavskyCommented:
If you are using TOAD edit command to display the data, then it is trying to select from Oracle by ROWID and ROWID has no meaning for fixed tables such as External Tables.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dplinnaneAuthor Commented:
Good Job that's it.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.