This discussion is archived
10 Replies Latest reply: Apr 12, 2012 10:25 AM by 878451 RSS

schedule/procedure

878451 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Just following orders, it is a development environment.
  • 9. Re: schedule/procedure
    878451 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points