Curious if anyone has a solution or ideas on slick way for being able to set a context value and use bind variables in a SQL Developer report.

For background/example, most of my work is e-Business Suite R12 related, which uses multi-org access control (MOAC). MOAC basically applies a VPD predicate to synonyms/objects based upon a context value (multi_org2.current_org_id).

So, in a SQL Developer session, if I want to test out a query against a one of these VPD secured objects, I'll do:

1) exec apps.mo_global.set_policy_context('S',492); (where 492 is the org_id I want to initialize and query for)

2) then comment that out

3) select * from po_headers; (po_headers is a VPD secured synonym that I want to query against)

In SQL Developer, what would be slick is to be able to run a report where the logic is setup like:

exec apps.mo_global.set_policy_context('S',:p_org_id);

select * from po_headers where segment1 = :p_po_num;

But we can't really have the exec statement in a table style query, so I'm trying to figure out if there are any other options.

I'm starting to play with a parent child report type, where I have the parent report Style = 'Script' and the exec statement w/bind variable works properly to set the context. Then I have created a child report set up like:

select * from po_headers poh where poh.segment1 = nvl(:P_PO_NUM, poh.segment1)

...but I don't get prompted for a P_PO_NUM and it just returns all records that VPD allows (or if I remove the NVL, I get nothing).

I've also tried playing with using a master report (Style = Script) like the following, but the child report still doesn't pick up the P_PO_NUM:

begin apps.mo_global.set_policy_context('S',:p_org_id); dbms_output.put_line(:P_PO_NUM); end;

Anyone have any other ideas that could help figuring this out???

Thanks a ton!

Larry