RE: sql loader problem
- From: "Mercadante, Thomas F" <thomas.mercadante@xxxxxxxxxxxxxxxxx>
- To: "'oracle-l@xxxxxxxxxxxxx'" <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 22 Jul 2004 11:21:04 -0400
David,
About the only thing that I can think of is to create a function that does
this:
CREATE OR REPLACE FUNCTION tom_get_val
RETURN NUMBER IS
ret_val NUMBER;
BEGIN
BEGIN
SELECT tom_seq.CURRVAL INTO ret_val FROM dual;
EXCEPTION
WHEN OTHERS THEN
SELECT tom_seq.NEXTVAL INTO ret_val FROM dual;
END;
RETURN ret_val;
END;
And then in your sqlldr file:
(BATCHID
EXPRESSION
"tom_get_val"
Havn't tried this, but I have used functions withing sqlldr before.
Good Luck.
Tom Mercadante
Oracle Certified Professional
-----Original Message-----
From: david wendelken [mailto:davewendelken@xxxxxxxxxxxxx]
Sent: Thursday, July 22, 2004 10:58 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: RE: sql loader problem
(Sorry, sent this out under the wrong header)
Now that data loads, I can't figure out how to do one thing.
Two batch identification columns,
batchid
batchrecnum
batchid should have the same value for each record loaded in the same sqlldr
run. batchrecnum should start at 1 for each sqlldr run, and increment by 1.
So, here's the relevant portion of the control file:
(BATCHID
EXPRESSION
"NVL(BATCHID_SEQ.CURRVAL,BATCHID_SEQ.NEXTVAL)"
,BATCHRECNUM
RECNUM
batchrecnum works like a charm.
I didn't actually expect a reference to the sequence currval to work like
this (before a reference to nextval first), but it didn't raise an error.
(I just hoped to illustrate clearly what I wanted.) However, the data
loaded! But, it gives me the next value from the sequence for each new
record.
So, instead of getting, in one load (where the next value of batchid_seq
would be
5:
5,1
5,2
5,3
5,4
I'm getting,
5,1
6,2
7,3
8,4
Any ideas, besides hard-coding the batchid value for the run into the sqlldr
control file? I don't want to do that...
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx put
'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
