Toad World® Forums

Add support for setting context for VPD in Auto Optimize SQL

We have Oracle databases that use the Virtual Private Database (VPD) feature and would like to use Toad's Auto Optimize SQL feature.  The problem we have is that in each new session we need to call two procedures to set the context before we can get any data from the queries we run.  The Auto Optimize SQL feature opens a new window and session but does not allow us to set the context and therefore it will not properly tune the query since it will not get any data.

So what we would like to have is a feature where  we can have the CALL or EXEC statements that set the context run in the optimizer session prior to it running and tuning the query.  This could be done either in the options before running the optimizer session or the statements could be part of the statements that are being optimized (each alternative would run the calls/execs then run the query as the text that you can see is being optimized).  Below is an example of what we are trying to do.

EXEC sysadmin_vpd.client_ctx_pkg.set_client_id ('CLIENT_NAME');

EXEC sysadmin_vpd.org_ctx_pkg.set_org (1234);

Select * from table_name;