Do sequences work differently on NT vs. Sparc?

[PFC]

I have a materialized view that uses a sequence. It works fine on Sparc Oracle 8.1.5.0.3, but it doesn't work on NT Oracle 8.1.6.0.0.

--Code example
create sequence seq1;

create materialized view table_id as
select table_name, seq1.nextval AN_ID from user_tables;

On NT I get the error msg:
ORA-02287: sequence number not allowed here

I was told that there was a special notation for sequences on NT, but I cannot find any information on it.

Note: A view with a sequence works fine.i.e.
--code example
create sequence seq1;
select table_name, seq1.nextval AN_ID from user_tables;

Any suggestions, work arounds?

[PSoni]

Hi,

I am using NT 4.0 (8i , Release 8.1.5.0.0 )/Window 95(SQL PLUS 8.0, Release 8.0.6.0.0 ) combination.
I tested you sentece but it is working fine.
What is the version of your SQL PLUS? Sometimes lower vesion of SQL Plus also create probem.

SQL> create sequence seq1;

Sequence created.

SQL> create materialized view table_id as
2 select table_name, seq1.nextval AN_ID from user_tables;

Snapshot created.

SQL> select * from table_id;

TABLE_NAME AN_ID
------------------------------ ---------
ACCOUNT 29
AUDIT_EMP 30
BONUS 31
BOOKS 32
CUST_MST 33
D2_T1 34
DEPARTMENT 35
DEPT 36
EMP 37
EMP2 38
EMPLOYEE 39
EMPVIEW 40
..
..
..



P. Soni


[Edited by PSoni on 03-15-2001 at 07:00 AM]

[PFC]

Hi PSoni,
Thanks for the help! I checked the version of SQL Plus and it's SQL*PLUS: Release 8.1.6.0.0. Seeing that you're running 8i(NT), Release 8.1.5.0.0 and it works like it would on the 8i(Sparc), Release 8.1.5.0.3. I would assume that Oracle changed the permitted behaviour in Release: 8.1.6.0.0.

Now to find a work around. Someone had recommended trying to use a sequence in a trigger. I'll have to see if that works.

Thanks again,

PFC