Setting application context

[yodahart]

I am writing a report that connects to an oracle database using table policies for security.  In these policies it expects there to already be some session context variables set before a query is run.  Is there a way to call a procedure before the sql is run for a report to set these variables?  I am planning on passing the user id to the report and calling a procedure/function with the id to set the context.

 

Thanks!

[yodahart]

FYI...I've found a solution to this problem.  Here are my results:

 

It will require 2 parameters to be added to each report.  We will also have to pass 1 extra parameter to the report.

 

The 2 parameters that need to be added to each report are : CURRENT_USER_ID and SET_SECURITY.  They must be added in this order since they are dependent on each other!

 

CURRENT_USER_ID:

  This will be passed as a parameter through the url.  It will be the current user that is logged into you application.  You shouldn’t prompt for the value if none is passed.

 

SET_SECURITY:

  This will call a function in the database to set the session based on the first parameter before the actual SQL query is run.  Do not prompt for this value.

  The default value expression should be:

   $P{REPORT_CONNECTION}.prepareStatement(

"BEGIN SECURITY_PACK.SET_APP_CONTEXT('" + $P{CURRENT_USER_ID} + "'); END;" ).execute()