2 Replies Latest reply: May 18, 2013 8:50 AM by LarryBaugh RSS

    Method to set context value and use bind variables in a table report??

    LarryBaugh
      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