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

[Jeffrey Mark Braun]

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@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
je...@halcyon.com

[Robert Schöller]

On 14 Feb 2001 12:46:53 -0800, je...@halcyon.com (Jeffrey Mark Braun)
wrote:

> NAMEID "NAMEID_SEQ.NEXTVAL"

Try
NAMEID SEQUENCE(MAX, 1)

If it doesn't work (don't know the effects on the table-sequence), you
will have to load the data to a temp table and import it with a little
script (shouldn't be much work).

regard

Ing. Robert Schöller (scho...@inat.at)

[Jane Lockhart]

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" <je...@halcyon.com> wrote in message
news:96eqrt$q07$1...@halcyon.com...