Re: "SET ROLE" in stored procedure

Tomm Carr wrote:
>
> 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!"

I thought that the forms pl/sql blocks in triggers etc were essentially treated like anonymous blocks and therefore could use the package. I was sure that the last shop I was in was calling the set_role package from within forms.
In any case, I wasn't aware that all roles were disabled in stored procedures.
Thanks,
Terry Received on Tue Jul 29 1997 - 00:00:00 CDT