[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1265
  • Last Modified:

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;
0
dplinnane
Asked:
dplinnane
  • 2
1 Solution
 
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
 
dplinnaneAuthor Commented:
Good Job that's it.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now