This content has been marked as final. Show 3 replies
In Oracle, you can create a view like this:
When people use the view between 13:00 and 14:00 any day, they can see all the rows that are in the emp table.
CREATE OR REPLACE emp_view AS SELECT * FROM scott.emp WHERE TO_CHAR (SYSDATE, 'HH24:MI') BETWEEN '13:00' AND '14:00' ;
When people use the view before 13:00, or after 14:00, then it will appear as if there are no rows in the table.
In the future, post questions like this in the SQL and PL/SQL forum:
SQL and PL/SQL
You'll get better replies faster, since more people wathc that forum. This forum is supposed to deal only with the iSQL and SQL*Plus front ends.
Thank you very much for your reply!! IT WORKED!!
My problem is that because I am the ADMIN for the Database why doesn't it allow me to view the tables. I only want to grant permissions to certain members of staff. Me being an ADMIN I want to see the view. At the moment it says 'there are no rows selected' for me as well because I am trying to access it...
So basically how do I change it so I can always view it, never mind the time.
Secondly how I do I grant appropriate permissions to my staff so they cant view it..
If ADMIN is allowed to see all the rows all the time, then you can do something like this:
If user KING is allowed to see data through the view between 1:00 and 5:00 PM, then you can add this condition to the WHERE clause:
CREATE OR REPLACE emp_view AS SELECT * FROM scott.emp WHERE TO_CHAR (SYSDATE, 'HH24:MI') BETWEEN '13:00' AND '14:00' OR USER = 'ADMIN' ;
If you're using Enterprise Edition, you can also do this kind of thing through Virtual Private Database (VPD) using the dbms_rls package:
OR ( USER = 'KING' AND TO_CHAR (SYSDATE, 'HH24:MI') BETWEEN '13:00' AND '17:00' )