Currently Being Moderated
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
