Re: Sequences

In article <9jclrv$5b9$1_at_ctb-nnrp2.saix.net>, "Billy says...
>
>"Daniel A. Morgan" <Daniel.Morgan_at_attws.com>
>
>> > Was wondering what is the reason behind sequences not generating
>> > numbers when used in a UNION. Also, why does Oracle tell you no-no and
>> > perform the SQL without any errors?
>>
>> Please provide an example of your code. What you have writen does not
>> compute for me.
>
>No problem. Try and grok this in its fullness.
>
>Create any sequence. Then try something like:
>
>CREATE TABLE foobar
>TABLESPACE clause
>STORAGE clause
>UNRECOVERABLE AS
>SELECT
> sequence.nextval SEQ_KEY,
> col1,
> col2
>FROM table1
>UNION ALL
>SELECT
> sequence.nextval,
> col1,
> col2
>FROM table2
>
>The SEQ_KEY column in table FOOBAR will be nulls. No ora errors.
>
>--
>Billy
>
>
>
>

(sigh, no versions..... guess it makes it more fun trying it in every release ;)

I ran this:

drop table foobar;
drop sequence seq;
create sequence seq;

create table foobar
unrecoverable
as
select seq.nextval seq_key, dummy from dual union all
select seq.nextval, to_char(rownum) from dual /

select * from foobar;

in 716, 734, 803, 816, 817, 901 -- worked fine (well, it gave me the expected:

select seq.nextval seq_key, dummy from dual

           *
ERROR at line 4:
ORA-02287: sequence number not allowed here )

Then, I did 815 -- looks like an issue specific to 815 only. In 815 it erroneously allows the seq.nextval there and inserts nulls.

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp