Re: "SET ROLE" in stored procedure

engsys_at_merlion.singnet.com.sg wrote:
>
> Hello everyone,
>
> 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
>
> Can anyone please explain, what needs to be done. First i would like to
> know if i can use set role in a procedure. I am using Oracle 7.3.2.3 on
> AIX 4.1
>
> Thanks for speding your time and thanks a lot in advance.
>
> Regards,
> P Lakshmi Prasad

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

Cheers,
Terry Received on Mon Jul 28 1997 - 00:00:00 CDT