| Using Sequence In Sql Loader [message #74559] |
Fri, 07 January 2005 00:33  |
ramesh
Messages: 123
Registered: August 2000
|
Senior Member |
|
|
Hi,
I am trying to load data into a table using Sql Loader. I have data in 6 different control files. I have a primary key in the table, so i am using sequence for that. When i try to load data, the data from the first file is loaded properly, but for the subsequent files i am getting an error.
"SQL*Loader-459: error fetching numeric value for sequence on column (DATA_ID)
ORA-01502: index 'CAFCPGN.CD_PK_ID' or partition of such index is in unusable state"
My control file looks like this.
================================================
unrecoverable
LOAD DATA
INFILE 'graph_c7909_cpgndata.dat'
BADFILE 'graph_c7909_cpgndata.bad'
INTO TABLE CPGN_DATA APPEND
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
Data_id SEQUENCE(MAX),
Prod_Id,
Geog_Id,
Time_Id,
Meas_Id,
IData,
Cust_Fmt)
================================================
In my first control file i am using REPLACE and for the subsequent files APPEND. It is failing to append the data.
Kindly help me on this, i am not able to figure out the problem. Thanks in Advance.
Ramesh
|
|
|
|
|
|
|
|
| Re: Using Sequence In Sql Loader [message #74562 is a reply to message #74560] |
Fri, 07 January 2005 00:53  |
ramesh
Messages: 123
Registered: August 2000
|
Senior Member |
|
|
Yes, this is a direct load. This is not a master/detail records, but i have foreign key references to other tables.
Also i want to know will the same sequence used for loading all the 6 files. How does Oracle do this?
|
|
|
|
| Re: Using Sequence In Sql Loader [message #74563 is a reply to message #74559] |
Fri, 07 January 2005 01:18 |
ramesh
Messages: 123
Registered: August 2000
|
Senior Member |
|
|
Hi Mahesh,
I just want to add one more thing to that, i am droping the table and creating that again before loading the data. Once the data is loaded, i am creating the index on the primary key along with other 3 fields which are not null fields. I have data for all these 4 fields.
|
|
|
|
|
|
| Re: Using Sequence In Sql Loader [message #74568 is a reply to message #74562] |
Fri, 07 January 2005 04:56 |
 |
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
|
Senior Member
Account Moderator |
|
|
the SEQUENCE parameter is handy.
But it may not work good when there are multiple sessions.
for complex operations, look into DATABASE Sequences as
JAI has stated.
< quoting docs >
Generating Sequence Numbers for Multiple Tables
Because a unique sequence number is generated for each logical input record, rather than for each table insert, the same sequence number can be used when inserting data into multiple tables. This is frequently useful.
Sometimes, however, you might want to generate different sequence numbers for each INTO TABLE clause. For example, your data format might define three logical records in every input record. In that case, you can use three INTO TABLE clauses, each of which inserts a different part of the record into the same table. When you use SEQUENCE(MAX), SQL*Loader will use the maximum from each table, which can lead to inconsistencies in sequence numbers.
To generate sequence numbers for these records, you must generate unique numbers for each of the three inserts. Use the number of table-inserts per record as the sequence increment and start the sequence numbers for each insert with successive numbers.
< /end quote docs >
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
