Home » SQL & PL/SQL » SQL & PL/SQL » Conditional Sequence usage (ORACLE 11.2.0.3.0)
Conditional Sequence usage [message #576113] Thu, 31 January 2013 10:29 Go to next message
tmcallister
Messages: 97
Registered: December 2007
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!
Re: Conditional Sequence usage [message #576115 is a reply to message #576113] Thu, 31 January 2013 11:03 Go to previous messageGo to next message
Bill B
Messages: 1099
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;
Re: Conditional Sequence usage [message #576116 is a reply to message #576113] Thu, 31 January 2013 11:05 Go to previous messageGo to next message
Michel Cadot
Messages: 59118
Registered: March 2007
Location: Nanterre, France, http://...
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

Re: Conditional Sequence usage [message #576118 is a reply to message #576116] Thu, 31 January 2013 11:40 Go to previous messageGo to next message
tmcallister
Messages: 97
Registered: December 2007
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);
Re: Conditional Sequence usage [message #576120 is a reply to message #576118] Thu, 31 January 2013 11:50 Go to previous messageGo to next message
BlackSwan
Messages: 22789
Registered: January 2009
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> 


Re: Conditional Sequence usage [message #576121 is a reply to message #576120] Thu, 31 January 2013 11:55 Go to previous messageGo to next message
tmcallister
Messages: 97
Registered: December 2007
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 Go to previous message
BlackSwan
Messages: 22789
Registered: January 2009
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.
Previous Topic: Convert to numberic/date
Next Topic: Multiple Queries into different columns
Goto Forum:
  


Current Time: Wed Sep 17 23:03:34 CDT 2014

Total time taken to generate the page: 0.12992 seconds