Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.tools -> Re: create view as select a sequence.nextval
In article <eauflt4lqm28nl63s8k525nbj2h5tcsoki_at_4ax.com>, Colum says...
>
>Oh wise ones
>
>This worked in pre-816 but gives ORA-2201 "sequence not allowed here"
>in 816:
>
>create sequence seq;
>
>create view myseq_view as select seq.nextval from sys.dual;
>
>We have a need to hide a sequence in a view (or table but that turns
>it static), is there a way to do this? I searched Google where someone
>did something similar with functions but this too doesn't "take" in
>816.
>
>02201, 00000, "sequence not allowed here"
>// *Cause: An attempt was made to reference a sequence in a
>from-list.
>// *Action: A sequence can only be referenced in a select-list.
>Headers spam-proofed. Use cmylod at bigfoot . com
here is an 816 example:
ops$tkyte_at_ORA8I.WORLD> create sequence seq;
Sequence created.
ops$tkyte_at_ORA8I.WORLD>
ops$tkyte_at_ORA8I.WORLD> create or replace view v as select seq.nextval from dual;
create or replace view v as select seq.nextval from dual
*ERROR at line 1:
ops$tkyte_at_ORA8I.WORLD>
ops$tkyte_at_ORA8I.WORLD> create or replace function f return number
2 as
3 l_seq number;
4 begin
5 select seq.nextval into l_seq from dual; 6 return l_seq;
Function created.
ops$tkyte_at_ORA8I.WORLD>
ops$tkyte_at_ORA8I.WORLD> create or replace view v as select f from dual;
View created.
ops$tkyte_at_ORA8I.WORLD>
ops$tkyte_at_ORA8I.WORLD> select * from v;
F
1
ops$tkyte_at_ORA8I.WORLD> select * from v;
F
2
-- 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 CorpReceived on Fri Jul 20 2001 - 08:43:49 CDT