Re: How get the current value from a sequence?

Ubiquitous <weberm_at_polaris.net> wrote in message news:<cohktc$son$2_at_news.utelfla.com>...
> In article <41ab8adf$0$29974$636a15ce_at_news.free.fr>, micadot{at}altern{dot}org
> says...
>
> >1- To select a table in a procedure you must have been granted the select
> privilege directly
>
> Ahh, so the stored procedure has different privs, even if executed by
> the dba role?
>
> >2- You can't get the real last value with this query as maybe there are values
> in the cache
>
> Yes, I am aware of that, but this is just an approximation stored in
> LAST_VALUE. I use a cache value of zero and increment by one, so it should
> be pretty accurate.

The difference is that in order to write a stored procedure that uses owner authorization that references objected owned by a user other than the procedure owner the procedure owner must have been directly granted access to the objects and must not have obtained the privileges via a role because roles are not active in stored procedures.

If you set the cache to 0 why bother with using a sequence? You will have to read disk on each and every use of the sequence. This would be diaster with RAC and isn't exactly a good thing on an exclusive instance either. You may well be better off to perform an update on a single row table via an anonymous transaction than use a nocache sequence.

Also there is a v$ view which I belive will show you the next sequence value to be used at any given moment in time: v$_sequences (visible only to user SYS).

HTH -- Mark D Powell -- Received on Tue Nov 30 2004 - 09:12:40 CST