From: Sybrand Bakker <postbus@sybrandb.demon.nl>
Newsgroups: comp.databases.oracle.misc
Subject: Re: "unique" violation problem
Date: Wed, 04 Apr 2001 23:54:06 +0200
Message-ID: <mu5nctc9lk6d0tvogq1ohk3n5rbf2m39hi@4ax.com>
Reply-To: postbus@sybrandb.demon-verwijderdit.nl
References: <3ACB4609.626F0419@genomicfx.com>
X-Newsreader: Forte Agent 1.8/32.548
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
X-Complaints-To: abuse@nl.demon.net
NNTP-Posting-Host: sybrandb.demon.nl
X-NNTP-Posting-Host: sybrandb.demon.nl:212.238.21.78
X-Trace: beta-news.demon.nl 986421119 beta-news:87461 NO-IDENT sybrandb.demon.nl:212.238.21.78
Lines: 56


On Wed, 04 Apr 2001 11:04:25 -0500, Zhiliang Hu <zhu@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,