I'm pretty certain this will end up being an artifact of your code, or the .net driver you are using. I've knocked up a quick demo for you using pure SQL - PL/SQL and never get a lost sequence value. Incidentally the ref cursor you are using is probably unnecessary and likely impacts performance and readability of the code - my demo includes an insert_record2 procedure that consistently performs over 10% faster -in about 26s on my laptop vs 36 for the ref cursor version. I at least also think is easier to understand. You could obviously run a modified version against your test database complete with audit trigger.
/*
demo for dbse
assumes a user with create table, create sequence, create procedure pivs and quota.
*/
drop table dbse13142 purge;
create table dbse13142(
the_id number not null
, name varchar2(20)
, userid number)
;
drop sequence x_seq;
CREATE SEQUENCE X_SEQ NOCACHE NOORDER NOCYCLE ;
create or replace PROCEDURE Insert_Record
(p_name IN VARCHAR2,
p_userid IN INTEGER,
cur_out OUT sys_refcursor)
IS
v_id NUMBER := 0;
BEGIN
-- Get id value from sequence
SELECT x_seq.nextval
INTO v_id
FROM dual;
-- Line below is X_PKG line 40
INSERT INTO dbse13142
(the_id,
name,
userid)
VALUES
(v_id,
p_name,
p_userid);
-- Return new id
OPEN cur_out FOR
SELECT v_id the_id
FROM dual;
END;
/
create or replace PROCEDURE Insert_Record2
(p_name IN VARCHAR2,
p_userid IN INTEGER,
p_theid OUT dbse13142.the_id%type)
IS
BEGIN
-- Get id value from sequence
SELECT x_seq.nextval
INTO p_theid
FROM dual;
-- Line below is X_PKG line 40
INSERT INTO dbse13142
(the_id,
name,
userid)
VALUES
(p_theid,
p_name,
p_userid);
END;
/
set timing on
declare
c sys_refcursor;
begin
for i in 1..100000 loop
insert_record('User '||i,i,c);
close c;
end loop;
commit;
end;
/
select count(*) from dbse13142;
truncate table dbse13142;
declare
x number;
begin
for i in 1..100000 loop
insert_record2('User '||i,i,x);
end loop;
commit;
end;
/
select count(*) from dbse13142;
truncate table dbse13142;
Your current query is not giving the desired result because you are using a GROUP BY
clause on the PERSON_ID
column which has a unique value for both entries. As a result you will return both rows.
There are a few ways that you can solve this. You can use a subquery to apply the aggregate function to return the max(LAST_UPDATE_DATE_TIME)
for each SCHOOL_CODE
:
select s1.LAST_UPDATE_DATE_TIME,
s1.SCHOOL_CODE,
s1.PERSON_ID
from SCHOOL_STAFF s1
inner join
(
select max(LAST_UPDATE_DATE_TIME) LAST_UPDATE_DATE_TIME,
SCHOOL_CODE
from SCHOOL_STAFF
group by SCHOOL_CODE
) s2
on s1.SCHOOL_CODE = s2.SCHOOL_CODE
and s1.LAST_UPDATE_DATE_TIME = s2.LAST_UPDATE_DATE_TIME;
See SQL Fiddle with Demo
Or you can use use a windowing function to return the rows of data for each school with the most recent LAST_UPDATE_DATE_TIME
:
select SCHOOL_CODE, PERSON_ID, LAST_UPDATE_DATE_TIME
from
(
select SCHOOL_CODE, PERSON_ID, LAST_UPDATE_DATE_TIME,
row_number() over(partition by SCHOOL_CODE
order by LAST_UPDATE_DATE_TIME desc) seq
from SCHOOL_STAFF
where STAFF_TYPE_NAME='Principal'
) d
where seq = 1;
See SQL Fiddle with Demo
This query implements row_number()
which assigns a unique number to each row in the partition of SCHOOL_CODE
and placed in a descending order based on the LAST_UPDATE_DATE_TIME
.
As a side note, the JOIN with aggregate function is not exactly the same as the row_number()
version. If you have two rows with the same event time the JOIN will return both rows, while the row_number()
will only return one. If you want to return both with a windowing function, then consider using the rank()
windowing function instead as it will return ties:
select SCHOOL_CODE, PERSON_ID, LAST_UPDATE_DATE_TIME
from
(
select SCHOOL_CODE, PERSON_ID, LAST_UPDATE_DATE_TIME,
rank() over(partition by SCHOOL_CODE
order by LAST_UPDATE_DATE_TIME desc) seq
from SCHOOL_STAFF
where STAFF_TYPE_NAME='Principal'
) d
where seq = 1;
See Demo
Best Answer
The presentation of the data should not be the determining factor in how the data is stored - especially in the case of a primary key. That would be like saying that all dates need to be stored in the database as strings so that a particular date format could be displayed.
To make the select max(pk) + 1 work in a system with more than one user is to programmatically force only one session to get the next PK value at a time - seriously compromising your concurrency. Either that or you will have to write code to intercept the PK failure and try again.
The data displayed can be formatted in the presentation layer in any way that makes sense. You can do a to_char to add the leading zero's in the select if needed - another string in the record could be appended - whatever would make it look more like a "code". In our environment we just use the generated sequence as the identifier - its more important that it is generated efficiently and is unique than how it looks.