Error Displaying Sched. Jobs -> Scheduled Run Times

Toad for Oracle & DevOps Toolkit
1

Using 12.5.1.1 Oracle Professional Edition on Windows 7 64 bit

When accessing a schema’s Scheduled Jobs then selecting Scheduled Run Times, nothing is displayed and the following error is shown in the status line:

ORA-06550: line 13, column 64:

PLS-00201: identifier ‘DBMS_LOB’ must be declared

ORA-06550: line 13, column 3:

PL/SQL: Statement ignored

ORA-06550: line 14, column 27:

PLS-00201: identifier ‘DBMS_LOB’ must be declared

ORA-06550: line 14, column 3:

PL/SQL: Statement ignored

ORA-06550: line 21, column 7:

PLS-00201: identifier ‘DBMS_LOB’ must be declared

ORA-06550: line 21, column 7:

PL/SQL: Statement ignored

ORA-06550: line 27, column 5:

PLS-00201: identifier ‘DBMS_LOB’ must be declared

ORA-06550: line 27, column 5:

PL/SQL: Statement ignored

ORA-06550: line 30, column 3:

PLS-00201: identifier ‘DBMS_LOB’ must be declared

ORA-06550: line 30, column 3:

PL/SQL: Statement ignored

This information comes from DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING,

which sometimes fails in evaluating valid calendar strings,

especially when referencing other schedules. When doing so,

it helps to include the schema name.

2

Looks like the toad code is trying to call the Oracle DBMS_LOB package and that your user and/or database does not have that available. Could be you need granted execute on it, or it could be that your DBA has removed it from public as part of security control.

3

Ahh, makes sense. Will contact our DBA and work out the grants. Thank you!

4

I found some online references about DBMS_LOB and security concerns - so others may run into this as well.

www.sans.org/…/SCORE_Oracle_v3.1.xls

Which says the following:

Revoke the public execute privilege on dbms_lob

DBMS_LOB can used to access files on the OS due to a bug in the way it handles relative paths. EXECUTE should be revoked from PUBLIC anyway but this is even better reason.

REVOKE EXECUTE ON dbms_lob FROM PUBLIC;