creating view on "oracle view"

Question

I am using oracle 12c. We have an oracle view ALL_SCHEUDLER_JOBS. As we will loosing the access to this table, I create a view on it as below :

the below function is placed inside a package called PCK_SUP which is in SCH1

function read_scheduler_jobs return t_scheduler_jobs pipelined
    as
    l_scheduler_jobs all_scheduler_jobs%ROWTYPE;
    cursor c
    is select * from all_scheduler_jobs where owner in ('SCH1', 'SCH2');
    begin
      open c ;
      loop
        fetch c into l_scheduler_jobs;
        exit when c%NOTFOUND;
        pipe row(l_scheduler_jobs);
      end loop;
      close c;
      return;
    end read_scheduler_jobs;

create or replace view vis_scheduler_jobs as
select "OWNER","JOB_NAME","JOB_SUBNAME","JOB_STYLE","JOB_CREATOR","CLIENT_ID","GLOBAL_UID","PROGRAM_OWNER","PROGRAM_NAME","JOB_TYPE","JOB_ACTION","NUMBER_OF_ARGUMENTS","SCHEDULE_OWNER","SCHEDULE_NAME","SCHEDULE_TYPE","START_DATE","REPEAT_INTERVAL","EVENT_QUEUE_OWNER","EVENT_QUEUE_NAME","EVENT_QUEUE_AGENT","EVENT_CONDITION","EVENT_RULE","FILE_WATCHER_OWNER","FILE_WATCHER_NAME","END_DATE","JOB_CLASS","ENABLED","AUTO_DROP","RESTART_ON_RECOVERY","RESTART_ON_FAILURE","STATE","JOB_PRIORITY","RUN_COUNT","MAX_RUNS","FAILURE_COUNT","MAX_FAILURES","RETRY_COUNT","LAST_START_DATE","LAST_RUN_DURATION","NEXT_RUN_DATE","SCHEDULE_LIMIT","MAX_RUN_DURATION","LOGGING_LEVEL","STORE_OUTPUT","STOP_ON_WINDOW_CLOSE","INSTANCE_STICKINESS","RAISE_EVENTS","SYSTEM","JOB_WEIGHT","NLS_ENV","SOURCE","NUMBER_OF_DESTINATIONS","DESTINATION_OWNER","DESTINATION","CREDENTIAL_OWNER","CREDENTIAL_NAME","INSTANCE_ID","DEFERRED_DROP","ALLOW_RUNS_IN_RESTRICTED_MODE","COMMENTS","FLAGS","RESTARTABLE","CONNECT_CREDENTIAL_OWNER","CONNECT_CREDENTIAL_NAME" from table(PCK_SUP.read_scheduler_jobs)
readonly;

We have two schemas SCH1 and SCH2. I created the above function and view in SCH1. If I run "select distinct owner from ALL_SCHEDULER_JOBS", I can see both SCH1 and SCH2. But, when I do the same from VIS_SCHEDULER_JOBS, I cannot see SCH2. I only see SCH1.

Thanks for your help

Answer 1

Creating this custom view makes no sense. The view ALL_SCHEDULER_JOBS is accessible to everyone. You will not lose access to it, so creating VIS_SCHEDULER_JOBS is just pointless. ALL_SCHEDULER_JOBS lists the jobs the current user can access. You may lose access to the jobs, but not to ALL_SCHEDULER_JOBS view.

ALL_SCHEDULER_JOBS

Answer 2

If the Permission for the ALL_SCHEUDLER_JOBS is removed, i guess you will not be able to access inside the function as well. So make sure that the function is created in a schema that has the access to ALL_SCHEUDLER_JOBS and provide an execute privilege to SCH1 schema.