tags on the line before and after any code to preserver the formatting. Don't post two sets of unformatted code and results. When you do post code and results explain why those results are NOT correct and what results you are trying to produce. ALWAYS provide your 4 digit Oracle version. There: how'd I do?
TractorEnvy wrote:Because a sequence is a shared/concurrent object and not a serialised object.
why is currval giving me the nextval
why is currval giving me the nextval we should get somewhere?
Within a single SQL statement containing a reference to NEXTVAL, Oracle increments the sequence once:
If any of these locations contains references to both CURRVAL and NEXTVAL, then Oracle increments the sequence and returns the same value for both CURRVAL and NEXTVAL.
Edited by: Stew Ashton on Mar 16, 2013 10:00 AM
create sequence tseq; WITH DATA AS (SELECT LEVEL-1 n FROM dual CONNECT BY LEVEL <= 2) SELECT CASE WHEN n = 1 THEN tseq.nextval ELSE tseq.currval end seq from data; SEQ ---------- 1 2
CREATE SEQUENCE te_seq; CREATE OR REPLACE FUNCTION te_seq_next_curr(p_num IN NUMBER) return number is BEGIN RETURN CASE p_num WHEN 0 THEN te_seq.currval ELSE te_seq.nextval END; END te_seq_next_curr; / SELECT ROWNUM rn, mod(ROWNUM-1,2) rn_mod, te_seq_next_curr(mod(ROWNUM,2)) seq FROM dual connect by rownum <= 10; RN RN_MOD SEQ ---------- ---------- ---------- 1 0 1 2 1 1 3 0 2 4 1 2 5 0 3 6 1 3 7 0 4 8 1 4 9 0 5 10 1 5
TractorEnvy wrote:Maybe because giving the technically correct answer to a wrong approach is anathema to them. They want to help, not steer you faster down the wrong path.
Thank you for the serialized answer it really shouldn't of surprised me. I appreciate it i can now continue on. I wanted to understand why currval wasn't working. If I wanted alternate approaches I would of asked for them. There are a lot of bright people on this forum but 50% of them don't want to answer the question being asked they want to tell the person why it's a dumb question or that they should do it another way. I hope this response helps someone else out there.