There is a very old forum thread on this, but I decided to open a new one instead to bring it to your attention. Take a look at the following image from TOAD 16.0:
With 'Script Options->Drop Statement' checked, the call in the remove block for the selected job is to DBMS_IJOB though the calls in the create block are to DBMS_JOB. The remove is calling the, as of 19c, non-existent DBMS_IJOB even though I am logged in as the owner of the job.
Cheers,
Russ
Appears to be a discrepancy there, and thanks for bringing it to our attention... Dev will need to take a closer look...
DBMS_IJOB still exists on my 19c database, we shouldn't have any references to it if it doesn't exist. I do, however, see a way that the check to make sure it exists and you have privs on it could get bypassed.
If you go in here and uncheck "Schema Name", you should see DBMS_JOB instead.
Also, look on the "Jobs" tab in that dialog and make sure that the dbms_ijob option is unchecked.
Yep. That works. It's just that I need Schema Name.
When I am actually logged in as the owner, I should not get DBMS_IJOB at all or the set current_schema calls.
The use of DBMS_IJOB w/o having privs is a bug and I'll fix it.
Why do you need Schema Name here? Or is it just that you don't want to uncheck it for Jobs, then have to recheck it when you go back to some other object type?
Here is the reason that the SET CURRENT SCHEMA calls are there even when you are logged in as the job owner:
Think of having the "schema name" box checked on the tables tab. There (and for all other object types), the schema name will be included, even if you are logged in as the table owner. This way, when the script is run later, no matter who you are logged in as, the table will be created under the schema from which it was extracted.
The only way to achieve that with jobs is with the SET CURRENT SCHEMA command, or to use DBMS_IJOB.
Yep. We have dozens of schemas and thousands of objects of various kinds. We need schema name references everywhere, and if we have to keep switching this setting, there will inevitably be mistakes that will find their way into our code repository. Like, "Oh, I forgot to set that schema setting back after dorking with that other stuff." Not good. We need to be able to have that setting checked all the time.
Also, I have a bone to pick about the Jobs tab of Script Options. It needs a 'Use ISUBMIT to preserve job number' option. (DBMS_IJOB no longer exists as of 19c, but DBMS_JOB.ISUBMIT does.) As it is, we have to manually edit the script the tool offers so that we can set our job numbers so that they will be the same across many databases. This is something that has chafed me about TOAD for a long time.
Cheers,
Russ
DBMS_IJOB still exists on 19c. See below. I'll test the job number option.
Yep you are right. I just needed to take off my filters and be DBA.
I just tested the "preserve job number" option. I got a script like this, and when I ran it, the job number was the same for the new job. Of course, if you are logged in as a user that does not have access to dbms_ijob, this isn't going to work.
I see what you mean about DBMS_JOB.ISUBMIT though. I'll log an enhancement request to use that when DBMS_IJOB is not available.
BEGIN
SYS.DBMS_IJOB.REMOVE(20883);
COMMIT;
END;
/
DECLARE
NewJobID NUMBER;
BEGIN
NewJobID := 20883;
SYS.DBMS_IJOB.SUBMIT
( job => NewJobID
,luser => 'JDORLON'
,puser => 'JDORLON'
,cuser => 'JDORLON'
,what => 'begin null; end;'
,next_date => TO_DATE('2022-10-15', 'YYYY-MM-DD')
,interval => 'TRUNC(SYSDATE+30)'
,broken => FALSE
,nlsenv => 'NLS_LANGUAGE=''AMERICAN'' NLS_TERRITORY=''AMERICA'' NLS_CURRENCY=''$'' NLS_ISO_CURRENCY=''AMERICA'' NLS_NUMERIC_CHARACTERS=''.,'' NLS_CALENDAR=''GREGORIAN'' NLS_DATE_FORMAT=''DD-MON-RR'' NLS_DATE_LANGUAGE=''AMERICAN'' NLS_SORT=''BINARY'' NLS_TIME_FORMAT=''HH.MI.SSXFF AM'' NLS_TIMESTAMP_FORMAT=''DD-MON-RR HH.MI.SSXFF AM'' NLS_TIME_TZ_FORMAT=''HH.MI.SSXFF AM TZR'' NLS_TIMESTAMP_TZ_FORMAT=''DD-MON-RR HH.MI.SSXFF AM TZR'' NLS_DUAL_CURRENCY=''$'' NLS_COMP=''BINARY'' NLS_LENGTH_SEMANTICS=''BYTE'' NLS_NCHAR_CONV_EXCP=''FALSE'''
,env => '0102000000000000');
SYS.DBMS_OUTPUT.PUT_LINE
('Job Number is: ' || to_char(NewJobID));
COMMIT;
END;
/
The job parameter of DBMS_IJOB.SUBMIT is an IN/OUT while in DBMS_JOB.SUBMIT it is an OUT. With DBMS_JOB.ISUBMIT, it is an IN/OUT. Also, DBMS_IJOB and DBMS_JOB.ISUBMIT have two different purposes. The former, being undocumented, was clearly intended to be used internally--perhaps by Data Pump--to be able to build DBMS_JOBs in any schema. The latter, is solely meant for specifying the job number instead of just taking a generated one all other things being the same.
Hi Russ,
I agree with all of that.
That the dbms_ijob.submit code has been in Toad for, I would guess, 15-20 years (or more). I vaguely remember adding it at user request, I think as a way to create a job for in a different schema. I think the "preserve the job number" idea came later, and when it did, I may have just seen that could be done in dbms_ijob w/o looking for a way in dbms_job. Not sure.
Anyway, I've logged an ER to preserve the job number with dbms_job.isubmit. I'll add that in the next version (17.0). We're wrapping up 16.2 now.
-John