CODEWITHSUNDEEP
oracle-databasesettingssqlplus
Luc M
Luc M

Reputation: 17314

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

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 ?

Upvotes: 2

Views: 3225

Answers (4)

Vincent Malgrat
Vincent Malgrat

Reputation: 67722

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;
/

Upvotes: 1

Jeffrey Kemp
Jeffrey Kemp

Reputation: 60272

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 ;)

Upvotes: 0

Gary Myers
Gary Myers

Reputation: 35401

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.

Upvotes: 2

Matthew Watson
Matthew Watson

Reputation: 14243

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.

Upvotes: 1

Related Questions