Solved

Select on Oracle view returns no rows

Posted on 2014-02-21
7
1,041 Views
Last Modified: 2014-06-09
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
Comment
Question by:codyvance1
7 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39877311
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
 

Author Comment

by:codyvance1
ID: 39877318
I tried that as well, still shows no rows.
0
 
LVL 23

Expert Comment

by:paquicuba
ID: 39877437
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39877464
>>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
 

Accepted Solution

by:
codyvance1 earned 0 total points
ID: 39877486
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
 
LVL 22

Expert Comment

by:Steve Wales
ID: 40121499
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now