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

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 Wed Feb 14 2001 - 14:46:53 CST