Re: "unique" violation problem

On Wed, 04 Apr 2001 11:04:25 -0500, Zhiliang Hu <zhu_at_genomicfx.com> wrote:

>
>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
>

Sure,
you are using direct load and during direct load constraints are not checked.

Hth, Received on Wed Apr 04 2001 - 16:54:06 CDT