5 Replies Latest reply: Dec 4, 2012 1:19 PM by spajdy RSS

    Problem with DBMS_SCHEDULER

    R. Royal
      Hi,

      I need to change my dbms_scheduler job to a PLSQL_BLOCK and use DBMS_SESSION.SET_NLS in there before I call my procedure (the program_name MY_PRG call my stored procedure).

      I don't want drop e recreate 'MY_JOB' but modify NLS_NUMERIC_CHARACTERS into column NLS_ENV of DBA_SCHEDULER_JOBS.

      I tried this:

      exec DBMS_SCHEDULER.SET_ATTRIBUTE(
      name => 'MY_JOB',
      attribute => 'job_action',
      value => 'BEGIN DBMS_SESSION.SET_NLS(''NLS_NUMERIC_CHARACTERS''='',.''); MY_PRG; END;');

      but I get this error:

      ORA-27488: unable to set JOB_ACTION because PROGRAM_NAME was/were already set
      ORA-06512: at "SYS.DBMS_ISCHED", line 2986
      ORA-06512: at "SYS.DBMS_SCHEDULER", line 1847
      ORA-06512: at line 1

      Have you any idea?

      Thanks in advance!
        • 1. Re: Problem with DBMS_SCHEDULER
          sb92075
          R. Royal wrote:
          Hi,

          I need to change my dbms_scheduler job to a PLSQL_BLOCK and use DBMS_SESSION.SET_NLS in there before I call my procedure (the program_name MY_PRG call my stored procedure).

          I don't want drop e recreate 'MY_JOB' but modify NLS_NUMERIC_CHARACTERS into column NLS_ENV of DBA_SCHEDULER_JOBS.

          I tried this:

          exec DBMS_SCHEDULER.SET_ATTRIBUTE(
          name => 'MY_JOB',
          attribute => 'job_action',
          value => 'BEGIN DBMS_SESSION.SET_NLS(''NLS_NUMERIC_CHARACTERS''='',.''); MY_PRG; END;');

          but I get this error:

          ORA-27488: unable to set JOB_ACTION because PROGRAM_NAME was/were already set
          ORA-06512: at "SYS.DBMS_ISCHED", line 2986
          ORA-06512: at "SYS.DBMS_SCHEDULER", line 1847
          ORA-06512: at line 1

          Have you any idea?

          Thanks in advance!
          27488, 00000, "unable to set %s because %s was/were already set"
          // *Cause: An attempt was made to set an object's attribute even though one
          //         or more conflicting attributes of the same object had already been
          //         set.
          // *Action: Set the other conflicting attributes to NULL and then reissue
          //          the command.
          • 2. Re: Problem with DBMS_SCHEDULER
            R. Royal
            I do not understand what is in conflict
            • 3. Re: Problem with DBMS_SCHEDULER
              Hoek
              See:
              http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:652425700346984666
              • 4. Re: Problem with DBMS_SCHEDULER
                R. Royal
                Ok,
                but the problem is on attribute: NLS_ENV

                exec dbms_scheduler.disable( 'MY_JOB' );

                PL/SQL procedure successfully completed.

                exec dbms_scheduler.set_attribute_null ('MY_JOB' ,'nls_env');

                ORA-27469: NLS_ENV is not a valid job attribute

                I'd like modify the NLS_ENV or NLS_NUMERIC_CHARACTERS on JOB

                Is it possible?
                • 5. Re: Problem with DBMS_SCHEDULER
                  spajdy
                  You must drop job and create it again. Set session NLS settings as you want before create job. nls_env is get from session job was created.
                  Or you can use execute immediate 'alter session set ...' in PL/SQL code called in job_action or program.

                  Or you could try to use dbms_isched but it is undocumented (similar as dbms_ijob for all fasion job).