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.

1 roles are *ignored* in stored procedures, unless you define the procedure with authid current_user. This issue is well known and explained very often in this group.
2
The second remark is just rubbish.
The value returned from select sequence.currval from dual is accurate, provided you have called sequence.nextval at least once. However if you want no gaps as you stated previously, then don't sequences. Sequences always have gaps and should be used for meaningless numbers only.
Likely your numbers are equally meaningless, so I don't see why you don't want gaps.

--
Sybrand Bakker
Senior Oracle DBA