create sequence seq_test
start with  1 
increment by  1 
nocache
nocycle;

grant select on seq_test to scott;

SQL*Plus: Release  9 . 2 . 0 . 1 . 0  - Production on Mon Jun  2   13 : 52 : 01   2003 

Copyright (c)  1982 ,  2002 , Oracle Corporation.  All rights reserved.


Connected.

Sequence created.

Grant succeeded.

set serveroutput on;

declare 
  n_cur_seq numeric default - 1 ;
begin
  select system.seq_test.nextval 
  into n_cur_seq
  from dual;
  
  dbms_output.put_line(to_char(n_cur_seq));
end;

SQL*Plus: Release  9 . 2 . 0 . 1 . 0  - Production on Mon Jun  2   13 : 53 : 42   2003 

Copyright (c)  1982 ,  2002 , Oracle Corporation.  All rights reserved.


Connected.

 1 

PL/SQL procedure successfully completed.

Oracle9i Database Concepts
Release  1  ( 9 . 0 . 1 )
Part Number A88856- 02 

PL/SQL Blocks and Roles 
The use of roles in a PL/SQL block depends on whether it is an anonymous block or a named block (stored procedure, function, or trigger), and whether it executes with definer rights or invoker rights. 

Named Blocks with Definer Rights 
All roles are disabled in any named PL/SQL block (stored procedure, function, or trigger) that executes with definer rights. Roles are not used for privilege checking and you cannot set roles within a definer-rights procedure. 

The SESSION_ROLES view shows all roles that are currently enabled. If a named PL/SQL block that executes with definer rights queries SESSION_ROLES, the query does not return any rows. 

Anonymous Blocks with Invoker Rights 
Named PL/SQL blocks that execute with invoker rights and anonymous PL/SQL blocks are executed based on privileges granted through enabled roles. Current roles are used for privilege checking within an invoker-rights PL/SQL block, and you can use dynamic SQL to set a role in the session.