10 Replies Latest reply: Apr 12, 2012 12:25 PM by 878451 RSS

    schedule/procedure

    878451
      Hello guys,
      I´m having some trouble by running a job:

      BEGIN
      dbms_scheduler.create_job (
      job_name=>'test_minuto', job_type => 'PLSQL_BLOCK', job_action => 'exec tempo2', start_date=> SYSTIMESTAMP, repeat_interval=> 'freq=minutely', end_date=>null, comments => 'testando 123');
      END;

      and the procedure tempo2 :

      create procedure tempo2 as
      begin
      insert into armazena values ((select to_char(sysdate, 'fmDD/MM/YYYY - HH24:MI:SS ' ) horario from dual), system.seq_test.nextval);
      commit;
      end;


      I checked the table USER_SCHEDULER_JOB_RUN_DETAILS and noticed that the job is running but its status shows 'FAILED' and its ADDITIONAL_INFO shows:
      ORA-06550: line ORA-06550: line 1, column 475:
      PLS-00103: Encountered the symbol "TEMPO2" when expecting one of the following:
      := . ( @ % ;


      Does anyone knows what i am doing wrong ?

      Thanks

      Edited by: BrunoSales on 12/04/2012 09:23
        • 1. Re: schedule/procedure
          sb92075
          BrunoSales wrote:
          Hello guys,
          I´m having some trouble by running a job:

          BEGIN
          dbms_scheduler.create_job (
          job_name=>'test_minuto', job_type => 'PLSQL_BLOCK', job_action => 'exec tempo2', start_date=> SYSTIMESTAMP, repeat_interval=> 'freq=minutely', end_date=>null, comments => 'testando 123');
          END;

          and the procedure tempo2 :

          create procedure tempo2 as
          begin
          insert into armazena values ((select to_char(sysdate, 'fmDD/MM/YYYY - HH24:MI:SS ' ) horario from dual), system.seq_test.nextval);
          commit;
          end;


          I checked the table USER_SCHEDULER_JOB_RUN_DETAILS and noticed that the job is running but its status shows 'FAILED' and its ADDITIONAL_INFO shows:
          ORA-06550: line ORA-06550: line 1, column 475:
          PLS-00103: Encountered the symbol "TEMPO2" when expecting one of the following:


          Does anyone knows what i am doing wrong ?

          Thanks
          try below instead

          job_name=>'test_minuto', job_type => 'PLSQL_BLOCK', job_action => 'tempo2',
          • 2. Re: schedule/procedure
            878451
            Hi, Thanks for answering.

            i tried your suggestion, but it keeps failing and error has changed: ORA-06550: line ORA-06550: line 1, column 478:
            PLS-00103: Encountered the symbol "" when expecting one of the following: := . ( @ % ;
            The symbol ";" was substituted for "" to continue. , column :

            When i do: exec tempo2 works perfectly.

            Here´s the new code:


            BEGIN
            dbms_scheduler.create_job (job_name=>'test_minute', job_type => 'PLSQL_BLOCK', job_action => 'tempo2', start_date=> SYSTIMESTAMP, repeat_interval=> 'freq=minutely', end_date=>null, comments => 'testando 123');
            END;

            Edited by: BrunoSales on 12/04/2012 09:25
            • 3. Re: schedule/procedure
              sb92075
              BrunoSales wrote:
              Hi, Thanks for answering.

              i tried your suggestion, but it keeps failing with the same error.
              SELECT OWNER, OBJECT_TYPE FROM ALL_OBJECTS WHERE OBJECT_NAME = 'TEMPO2';

              post SQL & results from above
              • 4. Re: schedule/procedure
                878451
                Hi,

                Here are the results of the query:
                SELECT OWNER, OBJECT_TYPE FROM ALL_OBJECTS WHERE OBJECT_NAME = 'TEMPO2';
                OWNER OBJECT_TYPE
                --------------- -------------------
                SYSTEM PROCEDURE

                thanks

                Edited by: BrunoSales on 12/04/2012 10:00
                • 5. Re: schedule/procedure
                  sb92075
                  which schema is submitting the job?

                  what results when doing as below?

                  job_name=>'test_minuto', job_type => 'PLSQL_BLOCK', job_action => 'exec tempo2;',
                  • 6. Re: schedule/procedure
                    878451
                    Hi,


                    Running it as system´s schema.

                    The result is :

                    ORA-06550: line ORA-06550: line 1, column 475:
                    PLS-00103: Encountered the symbol "TEMPO2" when expecting one of the following:
                    := . ( @ % ;

                    thanks
                    • 7. Re: schedule/procedure
                      EdStevens
                      BrunoSales wrote:
                      Hi,

                      Here are the results of the query:
                      SELECT OWNER, OBJECT_TYPE FROM ALL_OBJECTS WHERE OBJECT_NAME = 'TEMPO2';
                      OWNER OBJECT_TYPE
                      --------------- -------------------
                      SYSTEM PROCEDURE

                      thanks

                      Edited by: BrunoSales on 12/04/2012 10:00
                      Why are you creating these things under the SYSTEM schema? That's a BIG no-no.
                      • 8. Re: schedule/procedure
                        878451
                        Just following orders, it is a development environment.
                        • 9. Re: schedule/procedure
                          878451
                          all right fellows, i've found a way to resolve the problem:

                          BEGIN
                          dbms_scheduler.create_job (
                          job_name=>'test_minuto', job_type => 'PLSQL_BLOCK', job_action => 'begin tempo2; end;', start_date=> SYSTIMESTAMP, repeat_interval=> 'freq=minutely', end_date=>null, comments => 'testando 123');
                          END;



                          in job_action i changed from 'exec tempo2' to 'begin tempo2; end;'

                          thanks anyways.

                          Regards;
                          • 10. Re: schedule/procedure
                            EdStevens
                            BrunoSales wrote:
                            Hello guys,
                            I´m having some trouble by running a job:

                            BEGIN
                            dbms_scheduler.create_job (
                            job_name=>'test_minuto', job_type => 'PLSQL_BLOCK', job_action => 'exec tempo2', start_date=> SYSTIMESTAMP, repeat_interval=> 'freq=minutely', end_date=>null, comments => 'testando 123');
                            END;

                            and the procedure tempo2 :

                            create procedure tempo2 as
                            begin
                            insert into armazena values ((select to_char(sysdate, 'fmDD/MM/YYYY - HH24:MI:SS ' ) horario from dual), system.seq_test.nextval);
                            commit;
                            end;
                            Not part of your immediate problem, but why are you converting sysdate to a character string and inserting that into your table? If you want that first column of table ARMAZENA to have a date, then it should be declared a DATE and you just insert sysdate:
                            insert into armazena values (sysdate horario, system.seq_test.nextval);
                            And it is pointless to put a column alias on that, so we can reduce it even further
                            insert into armazena values (sysdate, system.seq_test.nextval);
                            And you should NEVER put user defined objects into the SYSTEM schema, so should be
                            insert into armazena values (sysdate, appschema.seq_test.nextval);
                            >
                            I checked the table USER_SCHEDULER_JOB_RUN_DETAILS and noticed that the job is running but its status shows 'FAILED' and its ADDITIONAL_INFO shows:
                            ORA-06550: line ORA-06550: line 1, column 475:
                            PLS-00103: Encountered the symbol "TEMPO2" when expecting one of the following:
                            := . ( @ % ;


                            Does anyone knows what i am doing wrong ?

                            Thanks

                            Edited by: BrunoSales on 12/04/2012 09:23