DBMS_JOB failed, please help.
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: DBMS_JOB failed, please help.

Hybrid View

  1. #1
    Join Date
    Apr 2002
    Posts
    73

    DBMS_JOB failed, please help.

    SQL> variable v_jobnum number
    SQL> Begin
    2 DBMS_JOB.SUBMIT(:v_jobnum, 'analyze;', sysdate, 'sysdate +1');
    3 End;
    4 /
    Begin
    *
    ERROR at line 1:
    ORA-06550: line 1, column 93:
    PLS-00201: identifier 'ANALYZE' must be declared
    ORA-06550: line 1, column 93:
    PL/SQL: Statement ignored
    ORA-06512: at "SYS.DBMS_JOB", line 79
    ORA-06512: at "SYS.DBMS_JOB", line 131
    ORA-06512: at line 2

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    dont use ANALYZE, that's a keyword

  3. #3
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by pando
    dont use ANALYZE, that's a keyword
    Good advice, however this is not a culprit of the above problem. Oracle doesn't prevent you from having stored procedure named ANALYZE and furthermore - it doesn't prevent you from using this stored procedure.

    The real problem in the above error is that the user that tries to submit the job doesn't own that procedure and he/she apparently doesn't have a synonym for that procedure - so the full name notation is required. For example, if the owner of the procedure is SCOTT, then changing the line:
    Code:
    DBMS_JOB.SUBMIT(:v_jobnum, 'analyze;', sysdate, 'sysdate +1');
    into the following:
    Code:
    DBMS_JOB.SUBMIT(:v_jobnum, 'SCOTT.analyze;', sysdate, 'sysdate +1');
    will do the job...
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  4. #4
    Join Date
    Apr 2002
    Posts
    73
    Thanks.
    But I still cannot work through it by changing the name, any clues?


    SQL> variable v_jobnum number
    SQL> begin
    2 DBMS_JOB.SUBMIT(:v_jobnum,'test',sysdate, 'sysdate +1');
    3 end;
    4 /
    begin
    *
    ERROR at line 1:
    ORA-06550: line 1, column 98:
    PLS-00103: Encountered the symbol "END" when expecting one of the following:
    := . ( @ % ;
    The symbol ";" was substituted for "END" to continue.
    ORA-06512: at "SYS.DBMS_JOB", line 79
    ORA-06512: at "SYS.DBMS_JOB", line 131
    ORA-06512: at line 2

  5. #5
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    You are missing semicolon. Change 'test' into 'test;'.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  6. #6
    Join Date
    Dec 2002
    Location
    USA
    Posts
    53
    SQL> var jobno number;
    SQL> begin
    DBMS_JOB.SUBMIT(:jobno, 'DBMS_UTILITY.ANALYZE_SCHEMA (''SCOTT'', ''COMPUTE'');' ,SYSDATE+1/24, 'trunc(sysdate + 1) + 1/6');
    COMMIT;
    end;
    /


    ---''SCOTT'', ''COMPUTE''
    ---'' is two single quote
    Last edited by supower; 01-15-2003 at 04:27 PM.

  7. #7
    Join Date
    Apr 2002
    Posts
    73
    Thanks for all of your help, I login as b2e54_m ( has dba privilege) to run submitting dbms_job.

    SQL> show user
    USER is "B2E54_M"
    SQL> var v_jobnum number
    SQL> begin
    2 DBMS_JOB.SUBMIT(:v_jobnum,'DBMS_UTILITY.ANALYZE_SCHEMA ("B2E54_M", "COMPUTE");',
    3 SYSDATE +1/24, 'trunc(sysdate+1) +1/6');
    4 commit;
    5 end;
    6 /
    begin
    *
    ERROR at line 1:
    ORA-06550: line 1, column 122:
    PLS-00201: identifier 'B2E54_M' must be declared
    ORA-06550: line 1, column 93:
    PL/SQL: Statement ignored
    ORA-06512: at "SYS.DBMS_JOB", line 79
    ORA-06512: at "SYS.DBMS_JOB", line 131
    ORA-06512: at line 2

  8. #8
    Join Date
    Dec 2002
    Location
    USA
    Posts
    53
    you put double quote " instead of two single quote ''

    copy my following sql and try

    sql>var v_jobnum number
    sql> begin DBMS_JOB.SUBMIT(:v_jobnum,'DBMS_UTILITY.ANALYZE_SCHEMA (''B2E54_M'', ''COMPUTE'');',
    SYSDATE +1/24, 'trunc(sysdate+1) +1/6');
    commit;
    end;
    /

  9. #9
    Join Date
    Apr 2002
    Posts
    73
    Thanks so much, it works.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width