Re: "SET ROLE" in stored procedure

terryg8 wrote:
>
> engsys_at_merlion.singnet.com.sg wrote:
> >
> > I was trying to create a procedure with a "set role" statement. I want to
> > call this procedure from all the forms which require access privileges
> > on the tables required. But when i run the script to create the procedure
> > i get an error as follows :
> >
> > > LINE/COL ERROR
> > > -------- -----------------------------------------------------------------
> > > 8/7 PLS-00103: Encountered the symbol "ROLE" when expecting one of
> > > the following:
> > > transaction
> >
 

> I think the problem is that a "set role" statement isn't a valid
> PL/SQL statement at all. (I think.) Similar to DDL statements
> not being cool. Try using the DBMS_SESSION.SET_ROLE package
> supplied with the database. You can call that from your forms.
> Get your DBA to provide you with the specs from
> $ORACLE_HOME/rdbms/admin/dbmsutil.sql

The problem is that all roles are disabled within stored procedures. You can't even call DBMS_SESSION.SET_ROLE from within a stored procedure. You may, however, call it from an anonymous block within a script.

-- 
Tomm Carr
--
"Can you describe your assailant?"
"No problem, Officer.  That's exactly what I was doing when he hit me!"