| sequence numbers skipped when records are rejected during a load [message #69021] |
Tue, 16 October 2001 16:33  |
Sonal
Messages: 3
Registered: October 2001
|
Junior Member |
|
|
I have created a database trigger for populating a sequential number for col2 on insert.
CREATE OR REPLACE TRIGGER test_trigger
BEFORE INSERT ON test
FOR EACH ROW
Declare
V_SeqVal test.col2%type;
BEGIN
Select test_seq.NEXTVAL Into :new.col2 From Dual;
END;
My table is :
SQL> desc test
Name Null? Type
------------------------------- -------- ----
COL1 VARCHAR2(1)
COL2 NOT NULL NUMBER
COL3 VARCHAR2(3)
COL1 has a check constraint of the value must be 'T' or 'F'
When i do an insert from sqlplus all is working fine,
however when I try to load data into test using sql*loader, for every
rejected
row the sequence number is skipped.
My control file :
load data
infile 'c:test1.dat'
append
into table test
(col1 position(01:02) char,
col3 position (04:07) char)
my data file :
F TST
y TST
T TST
When using the above file to load record 1 is successful and the sequence
generates value 1 for col2 column,
record 2 gets rejected, because of the data y
record 3 is successful but the sequence generates value 3 instead of value 2
for col2.
What changes do I need to make so that the sequence doesn't skip numbers if
the
records during the load are rejected ?
Thanks,
Sonal.
----------------------------------------------------------------------
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
