| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.tools -> Re: Updates with sequences : not allowed ?
In article <39000E6C.69747F7_at_uhb.fr>,
Joel SEGUILLON <Joel.Seguillon_at_uhb.fr> wrote:
> Thanks again,
> That's the second time you help me :). I tried to improve your idea
> with a function like :
>
> 1 create or replace function get_s
> 2 (seq_name IN VARCHAR2(30))
> 3 return number
> 4 as
> 5 l_s number;
> 6 begin
> 7 select seq_name.currval into l_s from dual;
> 8 return l_s
> 9* end;
>
> But it does not work... and compiles with errors :(. Anyone has got
a
> better idea ?
>
you would need to use dynamic sql to do this.
You cannot do dynamic sql in PLSQL to be called in a SQL statement until version 8.1 (at which time, this function would not be necessary at all).
You cannot genericize this in version 8.0 and before.
You would need to use dynamic sql to do this, it would look like this in 8.0 and before:
ops$tkyte_at_8.0> create or replace function get_currval( p_sname in
varchar2 ) return number
2 as
3 l_theCursor integer default dbms_sql.open_cursor; 4 l_columnValue number default NULL; 5 l_status integer; 6 begin 7 dbms_sql.parse( l_theCursor, 8 'select ' || p_sname || '.currval from dual', 9 dbms_sql.native ); 10 10 dbms_sql.define_column( l_theCursor, 1, l_columnValue ); 11 l_status := dbms_sql.execute(l_theCursor); 12 if ( dbms_sql.fetch_rows(l_theCursor) > 0 ) 13 then 14 dbms_sql.column_value( l_theCursor, 1, l_columnValue ); 15 end if; 16 dbms_sql.close_cursor( l_theCursor ); 17 return l_columnValue;
Function created.
ops$tkyte_at_8.0>
ops$tkyte_at_8.0> drop sequence my_seq
2 /
Sequence dropped.
ops$tkyte_at_8.0> create sequence my_seq
2 /
Sequence created.
ops$tkyte_at_8.0> ops$tkyte_at_8.0> ops$tkyte_at_8.0> select my_seq.nextval from dual;
NEXTVAL
1
ops$tkyte_at_8.0> select my_seq.nextval from dual;
NEXTVAL
2
ops$tkyte_at_8.0>
ops$tkyte_at_8.0> exec dbms_output.put_line( get_currval( 'my_seq' ) )
2
PL/SQL procedure successfully completed.
ops$tkyte_at_8.0>
ops$tkyte_at_8.0> select get_currval( 'my_seq' ) from dual
2 /
select get_currval( 'my_seq' ) from dual
*
ERROR at line 1:
ORA-06571: Function GET_CURRVAL does not guarantee not to update
database
so that shows how to dynamic refer to the sequence but also shows *you cannot call it* from SQL.
In Oracle8i, release 8.1 and up, this would be:
ops$tkyte_at_dev8i> create or replace function get_currval( p_sname in
varchar2 ) return number
2 as
3 l_columnValue number default NULL;
4 begin
5 execute immediate 'select ' || p_sname || '.currval from
dual' INTO l_columnValue;
6 return l_columnValue;
7 end ;
8 /
Function created.
ops$tkyte_at_dev8i>
ops$tkyte_at_dev8i> drop sequence my_seq
2 /
Sequence dropped.
ops$tkyte_at_dev8i> create sequence my_seq
2 /
Sequence created.
ops$tkyte_at_dev8i> ops$tkyte_at_dev8i> ops$tkyte_at_dev8i> select my_seq.nextval from dual;
NEXTVAL
1
ops$tkyte_at_dev8i> select my_seq.nextval from dual;
NEXTVAL
2
ops$tkyte_at_dev8i>
ops$tkyte_at_dev8i> exec dbms_output.put_line( get_currval( 'my_seq' ) )
2
PL/SQL procedure successfully completed.
ops$tkyte_at_dev8i>
ops$tkyte_at_dev8i> select get_currval( 'my_seq' ) from dual
2 /
GET_CURRVAL('MY_SEQ')
2
The syntax is better, it can dynamically do SQL and understand that this sql does NOT write to the database state (it can see because of the INTO its a select, not an update). However, in your case, this will not be necessary as the sequence can be used directly.
> "Thomas J. Kyte" wrote:
> >
> > In article <38FF2983.7A53C851_at_uhb.fr>,
> > Joel SEGUILLON <Joel.Seguillon_at_uhb.fr> wrote:
> > > Hi,
> > > I am still fighting with my SQL scripts and i have a new
problem.
I'm
> > > just trying to do something like :
> > > Update table1
> > > set field1 = 'myvalue'
> > > where field2 = table2_sequence.CURRVAL;
> > > But SQLplus answers me : ORA-02287 : sequence number not allowed
here.
> > > I tryed to use something like :
> > > ... where field2 = (select last_number from user_sequences
where
> > > sequence_name = 'table2');
> > > But it does not work : currval and last_number do not seem to
be the
> > > same (i had currval = 30 and last_number = 41 :) ).
> > >
> > > If anyone has got an idea...
> > > Thanks.
> > >
> >
> > this is fixed in 8.1 -- you can use the currval directly. Until
then,
> > you might be able to use something like the following:
> >
> > scott_at_ORA734.WORLD> create table t ( x int, y int );
> > Table created.
> >
> > scott_at_ORA734.WORLD> create sequence s;
> > Sequence created.
> >
> > scott_at_ORA734.WORLD>
> > scott_at_ORA734.WORLD> create or replace function get_s return number
> > 2 as
> > 3 l_s number;
> > 4 begin
> > 5 select s.currval into l_s from dual;
> > 6 return l_s;
> > 7 end;
> > 8 /
> >
> > Function created.
> >
> > scott_at_ORA734.WORLD> insert into t values ( s.nextval, 0 );
> > 1 row created.
> >
> > scott_at_ORA734.WORLD> update t set y = y+1 where x = s.currval;
> > update t set y = y+1 where x = s.currval
> > *
> > ERROR at line 1:
> > ORA-02287: sequence number not allowed here
> >
> > scott_at_ORA734.WORLD> update t set y = y+1 where x = ( select get_s
from
> > dual );
> >
> > 1 row updated.
> >
> > --
> > Thomas Kyte tkyte_at_us.oracle.com
> > Oracle Service Industries
> > http://osi.oracle.com/~tkyte/index.html
> > --
> > Opinions are mine and do not necessarily reflect those of Oracle
Corp
> >
> > Sent via Deja.com http://www.deja.com/
> > Before you buy.
>
-- Thomas Kyte tkyte_at_us.oracle.com Oracle Service Industries http://osi.oracle.com/~tkyte/index.html -- Opinions are mine and do not necessarily reflect those of Oracle Corp Sent via Deja.com http://www.deja.com/ Before you buy.Received on Sat Apr 22 2000 - 00:00:00 CDT
 |
 |