nope, that wont work have tried already
This is something whichwould be fired by a logon trigger and it wouldnt allow me to execute it:
ORA-00604: error occurred at recursive SQL level 1
ORA-06565: cannot execute SET ROLE from within stored procedure
ORA-06512: at line 14
Is there a way/workaround to do a SET ROLE using PL/SQL ? Please advise
No - you cannot set roles within a definer's rights procedure.
See How Roles Work in PL/SQL Blocks in the Database Security doc
How Roles Work in PL/SQL Blocks
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's rights or invoker's rights.
Roles Used in Named Blocks with Definer's Rights
All roles are disabled in any named PL/SQL block (stored procedure, function, or trigger) that executes with definer's rights. Roles are not used for privilege checking and you cannot set roles within a definer's rights procedure.
And be careful to heed this warning about LOGON triggers (see Using triggers in the PL/SQL doc - http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/triggers.htm)
•If you use a LOGON trigger to monitor logons by users, include an exception-handling part in the trigger, and include a WHEN OTHERS exception in the exception-handling part. Otherwise, an unhandled exception might block all connections to the database.