CODEWITHSUNDEEP
oracle11gjobs
Tom Sebastian
Tom Sebastian

Reputation: 3433

How to know current max job_id value in Oracle as a non-dba user?

I have connected to oracle11g as a non-dba user. I need to create a new Oracle Job. In my job creation query I need to set a unique 'job_id' that do not conflict with existing dbms_jobs. My plan is to select job_ids from entire dbms_jobs so that from the result set i can decide my job_id is existing or not. But as a non dba user i couldn't query dbms_jobs (from sys.job$). What should I do?

Upvotes: 0

Views: 533

Answers (1)

Ben
Ben

Reputation: 52863

Jobs are created by a user, so query USER_JOBS. This has all the jobs for this user.

Equally, as a DBA user you should not be querying JOB$, use DBA_JOBS.

In 11g, DBMS_JOB is only provided "for backwards compatability". There's now the much more powerful DBMS_SCHEDULER instead.

However, the premise of your question appears to be flawed. You've written:

In my job creation query I need to set a unique 'job_id' that do not conflict with existing dbms_jobs. My plan is to select job_ids from entire dbms_jobs so that from the result set i can decide my job_id is existing or not

The job ID in DMBS_JOB.SUBMIT() is an OUT parameter. You do not assign this, the package does if for you.

declare

   l_job_id number;

begin

   dbms_job.submit( l_job_id, what => ... );

end;

Upvotes: 3

Related Questions