0

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

4

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

-1

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.

  • I did select distinct owner from ALL_SCHEDULER_JOBS. it will give you both the owner. Please check again the description. You can try and see that. – Biswajit Buragohain May 30 at 11:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.