Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.tools -> Re: create view as select a sequence.nextval

Re: create view as select a sequence.nextval

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 20 Jul 2001 06:43:49 -0700
Message-ID: <9j9cil0bab@drn.newsguy.com>

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:
ORA-02287: sequence number not allowed here

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;

  7 end;
  8 /

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 Corp 
Received on Fri Jul 20 2001 - 08:43:49 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US