SQL*Loader and SQL Operators

From: Scott Stout <Scott_Stout_at_alpha.xerox.com>
Date: 1997/11/12
Message-ID: <Forum.879347860.3879.Scott_Stout_at_usasstout>#1/1


[Quoted] Hello All...

I have the following problem running SQL*Loader against a 7.3.3.0 database on a Solaris 2.4 platform:

The control(.ctl) file:

load data
append

into table pizza
when (1:4) = 'FOOD'
trailing nullcols
(

   name			position(1)	char(10)

,interface_id constant "FEED ME"
,date_received "to_date('010101','ddmmyy')"
,customer_seq_nr "pizza_seq.nextval"

)

into table toppings
when (1:4) = 'TOPP'
trailing nullcols
(

   topping		position(1)	char(10)

,interface_id constant "FEED ME"
,date_received "to_date('010101','ddmmyy')"
,customer_seq_nr "pizza_seq.currval"

)

The data file:

FOODDUMMYINITI
TOPPPEPPERONIS
TOPPPEPPERSONI
TOPPBLACKOLIVE
FOODBETTYBOOPS
TOPPANCHOIVEES
TOPPSAUSAGESSS
FOODBIFFHUDSON The results, as reported by a select from SQL*Plus:

SELECT *
FROM pizza;

interface_id	date_received	customer_seq_nr		name
============	=============	===============		====
FEED ME		01-JAN-01	1			dummyiniti
FEED ME		01-JAN-01	2			bettyboops
FEED ME		01-JAN-01	3			biffhudson

SELECT *
from toppings;

interface_id	date_received	customer_seq_nr		topping
============	=============	===============		=======
FEED ME		01-JAN-01	3			pepperonis
FEED ME		01-JAN-01	3			peppersoni
FEED ME		01-JAN-01	3			blackolive
FEED ME		01-JAN-01	3			anchoivees
FEED ME		01-JAN-01	3			sausagesss

What would be expected from toppings should be:

interface_id	date_received	customer_seq_nr		topping
============	=============	===============		=======
FEED ME		01-JAN-01	2			pepperonis
FEED ME		01-JAN-01	2			peppersoni
FEED ME		01-JAN-01	2			blackolive
FEED ME		01-JAN-01	3			anchoivees
FEED ME		01-JAN-01	3			sausagesss

So...

Any ideas as to why the TOPPINGS records are all being set to the last sequence number selected? I thought SQL*Loader processed, physical, records sequentially, is this not the case?

Thanx in advance...

Please reply to Scott_Stout_at_xn.xerox.com

-Scott Received on Wed Nov 12 1997 - 00:00:00 CET

Original text of this message