[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1341
  • Last Modified:

Select on Oracle view returns no rows

Hi Experts,

I currently have an issue where I am trying to pull information from an Oracle DB, and when I run a select statement on a view, it returns no rows.  I know that it should return rows, as I can run the same script in the Application SQL utility and see results.

Is there something I am missing here?  It also returns no rows when logged in as sys.

Thanks!
Cody-
0
Cody Vance
Asked:
Cody Vance
1 Solution
 
slightwv (䄆 Netminder) Commented:
The view may be limiting results based on who is executing it.  This is called Label Security (I believe).

Try querying the view when logged into the database as the same use that the application uses.
0
 
Cody VanceSr. Analyst - ERPAuthor Commented:
I tried that as well, still shows no rows.
0
 
paquicubaCommented:
Sounds like somehow your database is restricting access to certain applications. Like slightwv mentioned, a Label Security (add-on that cost money) or perhaps a VPD (Virtual Private Database) which is free with Enterprise Edition.

For example, if you create the following view and query it from SQL Developer, you'll get no rows returned, whereas if you use SQL*Plus it will return 'X'

create view dual2 as select * from dual where sys_context('USERENV', 'MODULE') != 'SQL Developer'
/
select * from dual2
/

What you need to do to test this theory, is go to the application where you see the data and run the following query:

SELECT sys_context('USERENV', 'MODULE') FROM DUAL;

Then set your module and select from the view again to see if it works:

EXEC DBMS_APPLICATION_INFO.SET_MODULE( module_name => '<Result from above query goes here>', action_name => 'Testing');
/
0
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
slightwv (䄆 Netminder) Commented:
>>I tried that as well, still shows no rows.

If you are logged into the database with the same user as the application and it still doesn't work it limits the possibility:

1:  You aren't using the same view the application is using
2:  Different database?
3:  As shown above:  You are limiting the rows based on program calling it, machine it is being called from or something else.

I would check with the developers/DBAs or someone that understands how the database works with the application.
0
 
Cody VanceSr. Analyst - ERPAuthor Commented:
Alright, so the vendor finally got back to me, and it seems in order to view results you need to initialize a package first, who knew...

Thanks everyone for your input.
0
 
Steve WalesSenior Database AdministratorCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now