| DBMS_LOCK must be declared [message #534795] |
Fri, 09 December 2011 04:33  |
 |
rishwinger
Messages: 132
Registered: November 2011
|
Senior Member |
|
|
please explain me y i m getting this DBMS_LOCK error, i have DBA privilege
Declare
l_count integer := 0;
l_count_t integer := 0;
error_msg varchar2(4000);
begin
select count(*) into l_count from user_objects where object_name='PAKAGE_NAME' and object_type='PACKAGE';
if l_count=0 then
DBMS_OUTPUT.PUT_LINE('ERROR: Package PAKAGE_NAME does not exist in the database');
else
select count(*) into l_count_t from user_objects where object_name='PAKAGE_NAME' and status='VALID' and object_type in ('PACKAGE','PACKAGE BODY');
if l_count_t<>2 then
DBMS_OUTPUT.PUT_LINE('ERROR: Package PAKAGE_NAME or its body is in INVALID state');
begin
select text into error_msg from user_errors where name='PAKAGE_NAME' and rownum=1;
DBMS_OUTPUT.PUT_LINE(substr(error_msg,1,250));
exception
when no_data_found then
null;
end;
end if;
end if;
end;
/
ERROR: Package PAKAGE_NAME or its body is in INVALID state
PLS-00201: identifier 'DBMS_LOCK' must be declared
PL/SQL procedure successfully completed.
|
|
|
|
|
|
|
|
| Re: DBMS_LOCK must be declared [message #534840 is a reply to message #534803] |
Fri, 09 December 2011 09:47  |
 |
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
The following demonstrates reproduction of the error, then corrects it, by granting execute on dbms_lock explicitly, not through a role.
-- reproduction:
SCOTT@orcl_11gR2> create or replace package pakage_name
2 as
3 procedure test;
4 end pakage_name;
5 /
Package created.
SCOTT@orcl_11gR2> show errors
No errors.
SCOTT@orcl_11gR2> create or replace package body pakage_name
2 as
3 procedure test
4 is
5 begin
6 dbms_lock.sleep (1);
7 end;
8 end pakage_name;
9 /
Warning: Package Body created with compilation errors.
SCOTT@orcl_11gR2> show errors
Errors for PACKAGE BODY PAKAGE_NAME:
LINE/COL ERROR
-------- -----------------------------------------------------------------
6/5 PL/SQL: Statement ignored
6/5 PLS-00201: identifier 'DBMS_LOCK' must be declared
SCOTT@orcl_11gR2> Declare
2 l_count integer := 0;
3 l_count_t integer := 0;
4 error_msg varchar2(4000);
5 begin
6 select count(*)
7 into l_count
8 from user_objects
9 where object_name = 'PAKAGE_NAME'
10 and object_type = 'PACKAGE';
11 if l_count = 0 then
12 DBMS_OUTPUT.PUT_LINE
13 ('ERROR: Package PAKAGE_NAME does not exist in the database');
14 else
15 select count(*)
16 into l_count_t
17 from user_objects
18 where object_name = 'PAKAGE_NAME'
19 and status='VALID'
20 and object_type in ('PACKAGE','PACKAGE BODY');
21 if l_count_t <> 2 then
22 DBMS_OUTPUT.PUT_LINE
23 ('ERROR: Package PAKAGE_NAME or its body is in INVALID state');
24 begin
25 select text
26 into error_msg
27 from user_errors
28 where name= 'PAKAGE_NAME'
29 and rownum = 1;
30 DBMS_OUTPUT.PUT_LINE (substr (error_msg,1,250));
31 exception
32 when no_data_found then
33 null;
34 end;
35 end if;
36 end if;
37 end;
38 /
ERROR: Package PAKAGE_NAME or its body is in INVALID state
PLS-00201: identifier 'DBMS_LOCK' must be declared
PL/SQL procedure successfully completed.
-- correction:
SYS@orcl_11gR2> grant execute on dbms_lock to scott;
Grant succeeded.
SYS@orcl_11gR2> connect scott/tiger
Connected.
SCOTT@orcl_11gR2> create or replace package pakage_name
2 as
3 procedure test;
4 end pakage_name;
5 /
Package created.
SCOTT@orcl_11gR2> show errors
No errors.
SCOTT@orcl_11gR2> create or replace package body pakage_name
2 as
3 procedure test
4 is
5 begin
6 dbms_lock.sleep (1);
7 end;
8 end pakage_name;
9 /
Package body created.
SCOTT@orcl_11gR2> show errors
No errors.
SCOTT@orcl_11gR2> Declare
2 l_count integer := 0;
3 l_count_t integer := 0;
4 error_msg varchar2(4000);
5 begin
6 select count(*)
7 into l_count
8 from user_objects
9 where object_name = 'PAKAGE_NAME'
10 and object_type = 'PACKAGE';
11 if l_count = 0 then
12 DBMS_OUTPUT.PUT_LINE
13 ('ERROR: Package PAKAGE_NAME does not exist in the database');
14 else
15 select count(*)
16 into l_count_t
17 from user_objects
18 where object_name = 'PAKAGE_NAME'
19 and status='VALID'
20 and object_type in ('PACKAGE','PACKAGE BODY');
21 if l_count_t <> 2 then
22 DBMS_OUTPUT.PUT_LINE
23 ('ERROR: Package PAKAGE_NAME or its body is in INVALID state');
24 begin
25 select text
26 into error_msg
27 from user_errors
28 where name= 'PAKAGE_NAME'
29 and rownum = 1;
30 DBMS_OUTPUT.PUT_LINE (substr (error_msg,1,250));
31 exception
32 when no_data_found then
33 null;
34 end;
35 end if;
36 end if;
37 end;
38 /
PL/SQL procedure successfully completed.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
