-
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
-
dont use ANALYZE, that's a keyword
-
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?
-
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
-
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?
-
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 05:27 PM.
-
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
-
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;
/
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|