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

From: Jane Lockhart <lockjane_at_cadvision.com>
Date: Thu, 15 Feb 2001 21:52:34 -0700
Message-ID: <96if53$q5s$1@news3.cadvision.com>

I am attempting to load from an external source, conventional method, with a sequence in the ID column. I will load to a temp table, then insert to the primary table.
INSERT INTO main_table (id, next_column) AS

       SELECT seq_id.NEXTVAL, next_column
       FROM tmp_table;

I had thought of an insert trigger to fire the sequence each the data come in. That way I could load directly to the table. Am unsure of the script for the trigger, so I'll use the temp table.

"Jeffrey Mark Braun" <jeffb_at_halcyon.com> wrote in message news:96eqrt$q07$1_at_halcyon.com...
> 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.
>
> 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 - 22:52:34 CST