Re: Newbie: SQL*Loader, Direct Path, and Sequences.

From: Oracle DB Owner <afilonov_at_pro-ns.net>
Date: Thu, 15 Feb 2001 03:32:10 GMT
Message-ID: <3A8B4DB8.2E345E96_at_pro-ns.net>


Jeffrey Mark Braun wrote:

> I'm trying to use SQL*Loader to rapidly load our database with data from
> an outside source. I read about Direct Path loading with SQL*Loader, and
> attempted to perform this action, but encountered a problem which maybe
> someone can mention a way around.
>
> Because this data is from an outside source, I produce my own internal IDs
> as the primary key to guarantee uniqueness in my system (I can't "trust"
> the outside source, and I need to load all data no mater what).
>
> Here's an abbreviated example of the of the problem I'm encountering.
>
> My table looks like this:
>
> CREATE TABLE THE_NAMES
> (NAMEID NUMBER(38) NOT NULL,
> NAME VARCHAR2(4)
> )
> INITRANS 1
> MAXTRANS 255
> PCTUSED 40
> PCTFREE 20
> STORAGE
> (
> INITIAL 3145728
> NEXT 155648
> PCTINCREASE 30
> MINEXTENTS 1
> MAXEXTENTS 255
> FREELISTS 1
> FREELIST GROUPS 1
> ) TABLESPACE NAME_DATA
> NOCACHE
>
> ALTER TABLE THE_NAMES
> ADD (CONSTRAINT TNA_PK PRIMARY KEY
> (NAMEID)
> USING INDEX TABLESPACE NAME_IDX)
> /
>
> The sequence I'm using looks like this:
> CREATE SEQUENCE NAMEID_SEQ
> NOMAXVALUE
> NOMINVALUE
> NOCYCLE
> NOCACHE
> /
>
> I have a control file which looks like this:
>
> LOAD DATA
> INFILE 'THE_NAMES.DAT'
> TRUNCATE
> INTO TABLE THE_NAMES
> (NAME POSITION(01:04) CHAR,
> NAMEID "NAMEID_SEQ.NEXTVAL"
> )
>
> I then try the following statement with SQL*loader with the following
> error message:
>
> $ sqlldr user/password_at_server the_names.ctl direct=true
> errors=1000 data=./THE_NAMES.DAT
>
> SQL*Loader: Release 8.1.6.1.0 - Production on Wed Feb 14 14:51:10
> 2001
>
> (c) Copyright 1999 Oracle Corporation. All rights reserved.
>
> SQL*Loader-417: SQL string (on column NAMEID) not allowed in
> direct path.
>
> I realize you can't make function calls during a direct load, but I do
> need to insert a unique id for every row. If there is some way to do this
> after the fact, that would be great.

To update table after the load:

update the_names
set nameid = nameid_seq.nextval;

There is common misconseption that you can call sequences to get one next value only, not true. In
above statement, all rows will receive increasing numbers. By definition, the order is random, but as
table was just created, most probably rows will be numbered in order they were loaded. One more
suggestion: don't create primary key constraint until you load and update rows. Otherwise you'll get
an error, primary key field can't be null.

>
>
> Do people have suggestions on what I could do? I want to do direct load
> for speed, and so most everything else is secondary.
>
> I will repost any answers sent directly to me, for the convenence of the
> rest of the group.
>
> Thanks.
>
> Jeff Braun
> jeffb_at_halcyon.com
Received on Thu Feb 15 2001 - 04:32:10 CET

Original text of this message