dplinnane
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`~1 300117.00~ 2/19/2004 0:00:00~80266.00~11.95~1.0 0~301.00~` C&C Cooler Rental`~`Cooler Rentals `~`Res`
2~130.00~`130-Peachland`~1 300174.00~ 2/19/2004 0:00:00~80267.00~42.00~6.0 0~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.tx t')
)
REJECT LIMIT 1000
LOGGING
NOCACHE
NOPARALLEL;
Here is a sample of my flat file 2 records. Not sure what going on.
1~130.00~`130-Peachland`~1
2~130.00~`130-Peachland`~1
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.tx
)
REJECT LIMIT 1000
LOGGING
NOCACHE
NOPARALLEL;
What version of toad are you using? This works fine for me using version 7.6.0.11 on Oracle 9.2.0.4
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;
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Good Job that's it.