Re: "unique" violation problem

If you use the DIRECT method NO constraint checking takes place as you are bypassing the 'normal' load process and writing directly ( hence the name) to the data blocks...

"Frank" <franjoe_at_frisurf.no> wrote:

>Hi!
>
>Could you try without direct path option?
>
>Read about "8.SQL*Loader: Conventional and Direct Path Loads"
>- "Direct Loads, Integrity Constraints, and Triggers"
>in Oracle Documentation.
>
>Frank
>
>Zhiliang Hu <zhu_at_genomicfx.com> wrote in message
>news:3ACB4609.626F0419_at_genomicfx.com...
>>
>> I created a table:
>>
>> create table test_unique
>> (name VARCHAR2(20) unique NOT NULL,
>> color VARCHAR2(20),
>> temp VARCHAR2(20)
>> )
>> /
>>
>> Then used SQL Loader to append data:
>>
>> ________Control file__________
>> options (direct = true)
>> load data
>> infile 'raw_data.file'
>> badfile 'raw_data.bad'
>> discardfile 'raw_data.dsc'
>> append into table test_unique
>> fields terminated by "#"
>> (name VARCHAR2(20) unique NOT NULL,
>> color VARCHAR2(20),
>> temp VARCHAR2(20)
>> )
>>
>> ________Data file_____________
>> Scott Tempo#red#north
>> Jeff Geo#green#east
>>
>> *** THE PROBLEM IS, I can load the SAME data twice into the table (as
>> checked on SQL command line: select count(1) from test_unique) and the
>> same names appear TWICE in the "name" field where "UNIQUE" was defined.
>> NO ERROR was found on the second load. However, a third load gets an
>> error saying:
>>
>> "Error calling col array build, block formatter
>> ORA-26031: index maintenance error, the load cannot continue
>> ORA-26028: index ZHU.SYS_C00627 initially in unusable state"
>>
>> Any hint what was going wrong?
>>
>> Thanks!
>>
>> Zhiliang
>>
>>
>
  Received on Wed Apr 04 2001 - 11:49:02 CDT