Execute PL/SQL code through Report Studio

[Gary]

We need to query Oracle EBS R12 DB through cognos. There are a certain reports in Oracle EBS which user want to migrate to Cognos Application directly querying data to EBS database.

In Oracle EBS MO_GLOBAL.SET_POLICY_CONTEXT (PL/SQL function) , sets the data lever security to execute the Oracle EBS report restricting data on certain parameters. Unless this function is executed one cannot fetch the data .

Is there any possibility to execute that function through RS so that whenever user execute the report in Cognos, data is available.

Thanks
Gary

[ww55d]

It's a bit confusing, but it sounds like you'd like a table of data accessed only if another function is previously executed.

Would a stored procedure be appropriate here, which runs the function then returns the query?

[Gary]

MO_GLOBAL.SET_POLICY_CONTEXT created row lever restriction  (data security  before user executes report in Oracle EBS.

We are trying to do the same in COGNOS so that we are able to process the same function as customer do not want to spend any effort to rework in cognos.

Any idea how to do the same ?

[prikala]

Is it ok to execute the stored procedure once/session?
If it is, then consider this:
https://www-304.ibm.com/support/knowledgecenter/SSEP7J_10.2.2/com.ibm.swg.ba.cognos.ug_cra.10.2.2.doc/c_usecomblocks.html%23UseComBlocks

You can specify commands that are executed when database connection or session starts.

Here is an example of calling oracle stored procedure:
https://www-304.ibm.com/support/knowledgecenter/SSEP7J_10.2.2/com.ibm.swg.ba.cognos.ug_cra.10.2.2.doc/c_example--usingcommandblocksforvirtualprivatedatabasesfororacle.html%23Example--UsingCommandBlocksforVirtualPrivateDatabasesforOracle

[bdbits]

I hate to even tell you how to do it, because using SQL directly in a report though possible, it the antithesis of a proper Cognos implementation. I sincerely hope this is a very limited thing you plan to do, otherwise you might as well look at a different, and likely much cheaper licensing but more elbow grease required, reporting tool.

That said, there is a query property called "Generated SQL". Hit the ellipsis, click on Convert. This will add a SQL object that feeds the query. It in turn has a property SQL Syntax. You can set it to Native (which means Cognos) or Passthrough (which means database SQL).