OraFAQ Forum: SQL & PL/SQL » Conditional Sequence usage

Home » SQL & PL/SQL » SQL & PL/SQL » Conditional Sequence usage (ORACLE 11.2.0.3.0)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Conditional Sequence usage [message #576113]

Thu, 31 January 2013 10:29

tmcallister
Messages: 107
Registered: December 2007

Senior Member

I'd like to have a conditional that only gets the next value from a sequence is the current (or supplied) value is null.

Here are some trivial examples:

CREATE SEQUENCE ts
  MINVALUE 1
  START WITH 1
  INCREMENT BY 1;

SELECT ts.NEXTVAL FROM DUAL;

SELECT COALESCE(ts.CURRVAL, ts.NEXTVAL) FROM DUAL;

SELECT NVL(ts.CURRVAL, ts.NEXTVAL) FROM DUAL;

SELECT   CASE
           WHEN ts.CURRVAL IS NOT NULL THEN ts.CURRVAL
           ELSE ts.NEXTVAL
         END
FROM     DUAL;

Ideally multiple executions of any of these (or a better one of your design) should return the same value from the sequence, but mine do not. Any tips or tricks? Thanks!

Report message to a moderator

Re: Conditional Sequence usage [message #576115 is a reply to message #576113]

Thu, 31 January 2013 11:03

Bill B
Messages: 1971
Registered: December 2004

Senior Member

currval only returns a value if you have already used nextval. If you want to use the same sequence number then

declare
key number;
begin
select my_seq.nextval into key from dual;
insert into mytable1 values(key);
insert into mytable2 values(key);
end;

Report message to a moderator

Re: Conditional Sequence usage [message #576116 is a reply to message #576113]

Thu, 31 January 2013 11:05

Michel Cadot
Messages: 68769
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

If you use NEXTVAL in a query then NEXTVAL is calculated, anyway, and only ONCE and all CURRVAL has the value of this first NEXTVAL.

SQL> SELECT ts.CURRVAL from dual;
   CURRVAL
----------
         6

1 row selected.

SQL> SELECT ts.CURRVAL, ts.NEXTVAL, ts.CURRVAL, ts.NEXTVAL, ts.CURRVAL FROM DUAL;
   CURRVAL    NEXTVAL    CURRVAL    NEXTVAL    CURRVAL
---------- ---------- ---------- ---------- ----------
         7          7          7          7          7

1 row selected.

If you did not previously call NEXTVAL in a session then CURRVAL is not NULL, the call to this function returns an error:

SQL> @c
Connected.
SQL> SELECT ts.CURRVAL from dual;
SELECT ts.CURRVAL from dual
*
ERROR at line 1:
ORA-08002: sequence TS.CURRVAL is not yet defined in this session

Regards
Michel

Report message to a moderator

Re: Conditional Sequence usage [message #576118 is a reply to message #576116]

Thu, 31 January 2013 11:40

tmcallister
Messages: 107
Registered: December 2007

Senior Member

Thanks! Apparently my trivial example was a little misleading to my needs. Actually in a database I have the following trigger which does not work as expected; I'd like to rip it out, tear it to pieces, stomp on it, and tell people that if they want to insert into a table provide the necessary values; but that might not be in my power, so I'm trying to make it work as expected.

After running the following I'd like the values in the table to be 1, 1, 1, 1. Is this possible?

DROP SEQUENCE ts;

DROP TABLE tt;

CREATE TABLE tt (cid NUMBER);

CREATE SEQUENCE ts
  MINVALUE 1
  START WITH 1
  INCREMENT BY 1;

CREATE OR REPLACE TRIGGER tti
  BEFORE INSERT
  ON tt
  FOR EACH ROW
BEGIN
  :new.cid   := NVL(:new.cid, ts.NEXTVAL);
END;
/

INSERT INTO   tt
VALUES        (1);

INSERT INTO   tt
VALUES        (1);

INSERT INTO   tt
VALUES        (1);

INSERT INTO   tt
VALUES        (NULL);

Report message to a moderator

Re: Conditional Sequence usage [message #576120 is a reply to message #576118]

Thu, 31 January 2013 11:50

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

SQL> DROP SEQUENCE ts;

DROP TABLE tt;

CREATE TABLE tt (cid NUMBER);

CREATE SEQUENCE ts
  MINVALUE 1
  START WITH 1
  INCREMENT BY 1;

CREATE OR REPLACE TRIGGER tti
  BEFORE INSERT
  ON tt
  FOR EACH ROW
BEGIN
   IF ( :new.cid is NULL)
   THEN
     :new.cid   := NVL(:new.cid, ts.NEXTVAL);
   END IF;
END;
/

INSERT INTO   tt
VALUES        (1);

INSERT INTO   tt
VALUES        (1);

INSERT INTO   tt
VALUES        (1);

INSERT INTO   tt
VALUES        (NULL);

SELECT * FROM TT;

Sequence dropped.

SQL> SQL> 
Table dropped.

SQL> SQL> 
Table created.

SQL> SQL>   2    3    4  
Sequence created.

SQL> SQL>   2    3    4    5    6    7    8    9   10   11  
Trigger created.

SQL> SQL>   2  
1 row created.

SQL> SQL>   2  
1 row created.

SQL> SQL>   2  
1 row created.

SQL> SQL>   2  
1 row created.

SQL> SQL> 
       CID
----------
         1
         1
         1
         1

SQL>

Report message to a moderator

Re: Conditional Sequence usage [message #576121 is a reply to message #576120]

Thu, 31 January 2013 11:55

tmcallister
Messages: 107
Registered: December 2007

Senior Member

Thanks! Well that seems rather obvious. I feel foolish now Sad

.

I'd probably use

CREATE OR REPLACE TRIGGER tti
  BEFORE INSERT
  ON tt
  FOR EACH ROW
BEGIN
   IF ( :new.cid is NULL)
   THEN
     :new.cid   := ts.NEXTVAL;
   END IF;
END;
/

I don't believe it's possible to use a sequence as the default value of a column; is it?

[Updated on: Thu, 31 January 2013 11:56]

Report message to a moderator

Re: Conditional Sequence usage [message #576122 is a reply to message #576121]

Thu, 31 January 2013 11:59

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>I don't believe it's possible to use a sequence as the default value of a column; is it?
correct.

BTW, INSERT INTO TT VALUES(NULL);
above would not use any "default value" on the column; since INSERT explicitly specified NULL to be new value.

Report message to a moderator

Previous Topic:	Convert to numberic/date
Next Topic:	Multiple Queries into different columns

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Sep 03 00:10:08 CDT 2025