How may I see the data in the Oracle view like I see it through the application ?

Question

When I navigate through the Oracle application with my application user and the right responsibility, I see the data.
I use the "record history" menu to see which table/view is used by application.
So, I got PA_EXPEND_ITEMS_ADJUST2_V.

When I'm connected with apps user in a sqlplus session,

SELECT * FROM PA_EXPEND_ITEMS_ADJUST2_V

gives me 0 rows.

I guess that there's something is misconfigurated with the apps but what ?

How may I view the rows of PA_EXPEND_ITEMS_ADJUST2_V using apps user in a sqlplus session ?

How may I see the data in the Oracle view like I see it through the application ?

Answer 1

You will need to log into oracle with the same user ( or a user with the same rights/roles ) as what the application is using.

You need to talk to your DBA.

Answer 2

There is probably some row-level security happening here. Possibly based on views, possibly the built-in RLS/FGAC/VPD (or whatever acronym they give it with that version). That's where the database rewrites the query behind the scenes to add in filters.

Generally there are based on SYS_CONTEXT values.

Answer 3

Another possibility (apart from row-level security, which may be involved) is that the view is based on one or more global temporary tables - which means you won't see the data unless you query from within the same session that inserts it.

Or, perhaps, the app is deleting the data after it's finished with it ;)

Answer 4

Hi Luc,

In Oracle Applications you have to execute the APPS.FND_GLOBAL.apps_initialize procedure to have the same context in a SQL*Plus session. I use the following script to start a session:

SET SERVEROUTPUT ON
DECLARE
   l_user_id   NUMBER;
   l_resp_id   NUMBER;
   l_app_id    NUMBER;
   l_resp_name VARCHAR2(100) := '<Name of your responsibility>';
   l_login VARCHAR2(30) := '<USERLOGIN>'
BEGIN
   SELECT user_id INTO l_user_id FROM fnd_user WHERE user_name = l_login;
   SELECT application_id, responsibility_id
     INTO l_app_id, l_resp_id
     FROM fnd_responsibility_vl
    WHERE responsibility_name = l_resp_name;
   apps.fnd_global.apps_initialize(l_user_id, l_resp_id, l_app_id);
   dbms_output.put_line('l_user_id = '||l_user_id);
   dbms_output.put_line('l_resp_id = '||l_resp_id);
   dbms_output.put_line('l_app_id = '||l_app_id);
END;
/