5 Replies Latest reply on Feb 13, 2015 5:04 PM by thatJeffSmith-Oracle

    4.1 EA1: Scheduler Job Wizard issue

    Raj Jamadagni

      While using wizard to create a job, if you specify job_name in lowercase, SD will try to create the lowercase job name (by enclosing it in double-quotes).

      This is an issue and probably needs to be fixed.

        • 1. Re: 4.1 EA1: Scheduler Job Wizard issue
          thatJeffSmith-Oracle

          I would agree but creating a job involves a package call. Passing the job name is an argument to the package, it's text, so has to be quoted.

           

          It's a little bit different than say, creating a table where quoting the identifiers is optional.

          • 2. Re: 4.1 EA1: Scheduler Job Wizard issue
            Raj Jamadagni

            I have no problem with quoting, but SD double-quotes the job name provided which is in lowercase (if entered so). If you expect users to enter case sensitive job_name, I'd at-least put a note on the wizard. Luckily I reviewed and stopped at generated plsql block hence noticed.

             

            Raj

            • 3. Re: 4.1 EA1: Scheduler Job Wizard issue
              thatJeffSmith-Oracle

              it quotes all strings, b/c you have to quote strings when making pl/sql calls

              • 4. Re: Re: 4.1 EA1: Scheduler Job Wizard issue
                Raj Jamadagni

                I think I am not making myself clear, perhaps an example would help ... here is my test script

                 

                -- This is what I'd normally run if I use handwritten plsql block,
                Set Echo On
                column job_name format a30
                --prompt normal case
                exec DBMS_SCHEDULER.CREATE_JOB (job_name => 'test_job',job_type => 'STORED_PROCEDURE',job_action => 'procedure.from.a.package',number_of_arguments => 0, start_date => NULL, repeat_interval => 'FREQ=WEEKLY;BYTIME=080000;BYDAY=SUN', end_date => NULL,enabled => FALSE, auto_drop => FALSE, comments => 'This is a test_job');
                --Prompt Normal Case - Select From Dba_Scheduler_Jobs With Job_Name In Uppercase
                Select Job_Name, Enabled From Dba_Scheduler_Jobs Where Job_Name = 'TEST_JOB';
                --Prompt Normal Case - Select From Dba_Scheduler_Jobs With Job_Name In Uppercase
                Select Job_Name, Enabled From Dba_Scheduler_Jobs Where Job_Name = 'test_job';
                exec dbms_scheduler.drop_job('TEST_JOB');
                

                 

                And the output looks like this (ran this in SD 4.1EA1 using F5 (run as script)

                 

                > column job_name format a30
                > --prompt normal case
                > exec DBMS_SCHEDULER.CREATE_JOB (job_name => 'test_job',job_type => 'STORED_PROCEDURE',job_action => 'procedure.from.a.package',number_of_arguments => 0, start_date => NULL, repeat_interval => 'FREQ=WEEKLY;BYTIME=080000;BYDAY=SUN', end_date => NULL,enabled => FALSE, auto_drop => FALSE, comments => 'This is a test_job')
                anonymous block completed
                > --Prompt Normal Case - Select From Dba_Scheduler_Jobs With Job_Name In Uppercase
                > Select Job_Name, Enabled From Dba_Scheduler_Jobs Where Job_Name = 'TEST_JOB'
                JOB_NAME                       ENABLED
                ------------------------------ -------
                TEST_JOB                       FALSE   
                
                > --Prompt Normal Case - Select From Dba_Scheduler_Jobs With Job_Name In Uppercase
                > Select Job_Name, Enabled From Dba_Scheduler_Jobs Where Job_Name = 'test_job'
                no rows selected
                
                > Exec Dbms_Scheduler.Drop_Job('TEST_JOB')
                anonymous block completed
                

                 

                This is how SD creates PL/SQL block when used through wizard, remember to enter job_name in lowercase

                 

                prompt SD 4.1EA case
                exec DBMS_SCHEDULER.CREATE_JOB (job_name => '"test_job2"',job_type => 'STORED_PROCEDURE',job_action => 'procedure.from.a.package',number_of_arguments => 0, start_date => NULL, repeat_interval => 'FREQ=WEEKLY;BYTIME=080000;BYDAY=SUN', end_date => NULL,enabled => FALSE, auto_drop => FALSE, comments => 'This is a test_job');
                --prompt SD 4.1EA case - select from dba_scheduler_jobs with job_name in UPPERCASE
                Select Job_Name, Enabled From Dba_Scheduler_Jobs Where Job_Name = 'TEST_JOB2';
                --Prompt Sd 4.1EA Case - Select From Dba_Scheduler_Jobs With Job_Name In Lowercase
                select job_name, enabled from dba_scheduler_jobs where job_name = 'test_job2';
                --Prompt Sd 4.1EA Case - Select From Dba_Scheduler_Jobs With Job_Name In Lowercase Double-Quoted
                Select Job_Name, Enabled From Dba_Scheduler_Jobs Where Job_Name = '"test_job2"';
                Exec Dbms_Scheduler.Drop_Job('TEST_JOB2');
                exec dbms_scheduler.drop_job('"test_job2"');
                

                 

                And the output (hit F5 in SD, run as script option)

                > Column Job_Name Format A30
                > prompt SD 4.1EA case
                SD 4.1EA case
                > exec DBMS_SCHEDULER.CREATE_JOB (job_name => '"test_job2"',job_type => 'STORED_PROCEDURE',job_action => 'procedure.from.a.package',number_of_arguments => 0, start_date => NULL, repeat_interval => 'FREQ=WEEKLY;BYTIME=080000;BYDAY=SUN', end_date => NULL,enabled => FALSE, auto_drop => FALSE, comments => 'This is a test_job')
                anonymous block completed
                > --prompt SD 4.1EA case - select from dba_scheduler_jobs with job_name in UPPERCASE
                > Select Job_Name, Enabled From Dba_Scheduler_Jobs Where Job_Name = 'TEST_JOB2'
                no rows selected
                > --Prompt Sd 4.1EA Case - Select From Dba_Scheduler_Jobs With Job_Name In Lowercase
                > select job_name, enabled from dba_scheduler_jobs where job_name = 'test_job2'
                JOB_NAME                       ENABLED
                ------------------------------ -------
                test_job2                      FALSE   
                > --Prompt Sd 4.1EA Case - Select From Dba_Scheduler_Jobs With Job_Name In Lowercase Double-Quoted
                > Select Job_Name, Enabled From Dba_Scheduler_Jobs Where Job_Name = '"test_job2"'
                no rows selected
                > Exec Dbms_Scheduler.Drop_Job('TEST_JOB2')
                Error starting at line : 20 in command -
                Exec Dbms_Scheduler.Drop_Job('TEST_JOB2')
                Error report -
                ORA-27475: unknown job "USERNAME"."TEST_JOB2"
                ORA-06512: at "SYS.DBMS_ISCHED", line 232
                ORA-06512: at "SYS.DBMS_SCHEDULER", line 778
                ORA-06512: at line 1
                27475. 00000 -  "unknown %s \"%s\".\"%s\""
                *Cause:    The specified object did not exist, privileges were not granted,
                           or the object was of the wrong type.
                *Action:   Specify an object of the correct type on which you have privileges.
                > exec dbms_scheduler.drop_job('"test_job2"')
                anonymous block completed
                

                 

                Let me know if you need more information. The issue is not quoting, but double-quoting that is done inside single-quotes by SD when generating necessary plsql code.

                 

                Cheers

                • 5. Re: 4.1 EA1: Scheduler Job Wizard issue
                  thatJeffSmith-Oracle

                  No, you were clear, I was just not paying close enough attention? Or a spider climbed into my brain?

                   

                  That's a bug.