Oracle Store Procedure Execution from Data Services

[former_member216962]

Hi All,

I have a view in ORACLE System that needs to be extracted and loaded into HANA. A security is built around view so that data in view is not accessible to everyone.

The policy is to execute Store Procedure before accessing the view to see that data .

Step 1 ) alter session set current_schema=apps;

Step 2) apps.mo_global.set_policy_context(\'S\',81);   - This would let the user access all the records for Value 81 for Org ID Column

Step 3) Select * from View ;  - This would give all the values from the view where Org ID = 81

The Step 1 and Step 2 are valid for the current session only - That means once the user logs off , the user have to execute the step1 and step2 again before going to step 3

I can get this thing to work with SQL Developer and Toad but not with BODS

With In BODS - I tried Multiple Ways

Approach 1) Move Step 1 and Step 2 to Data Store Session Parameters and then use View as Source - Job Fails

Approach 2) Step1 in Data Store Session Parameters and Step2 in Job Script and then Use View as Source - Job Executes succesfully but no records from view that the Policy was never set

Approach 3) I just have a script in Job with Below Statements  and the count 0 (Actually the count is not Zero when you the same with Toad)- That means the security procedure is not set

sql('DS_DEV','alter session set current_schema=apps;');

sql('DS_DEV','BEGIN apps.mo_global.set_policy_context(\'S\',81);

END;');

print(sql('DS_DEV','select count(*) from APPS.OKL_CS_BPD_INV_DTL_V'));

This is the feed back from SAP

we proved that all these statements have to be run under one session (one PID )

Any Suggestions

Thanks in Advance

[former_member216962]

Any Suggestions