-
Getting PLS-00905: object APPS.APPS is Invalid
Hello friends,
I have a sample test package with one proc as below.
Package Spec
--------------
CREATE OR REPLACE PACKAGE APPS.samir_test
IS
PROCEDURE samir_test_p (p_employee_number IN VARCHAR2);
END samir_test;
/
Package Body
--------------
CREATE OR REPLACE PACKAGE BODY APPS.samir_test
IS
PROCEDURE samir_test_p (p_employee_number IN VARCHAR2)
IS
x_emp_num NUMBER;
BEGIN
SELECT employee_number into x_emp_num FROM per_all_people_f WHERE employee_number = p_employee_number;
END samir_test_p;
END samir_test;
/
Now, I call the proc samir_test_p as below.
DECLARE
p_employee_number number := 2000121;
BEGIN
apps.samir_test.samir_test_p ( p_employee_number);
END;
I get following error.
ORA-06550: line 7, column 3:
PLS-00905: object APPS.APPS is invalid
ORA-06550: line 7, column 3:
PL/SQL: Statement ignored
But, if I remove apps. qualifier and call it using samir_test.samir_test_p ( p_employee_number);
it works.
I would like to know why it does not like the qualifier here. We have a need to use qualifier in our code.
Please help.
Thanks in Advance.
Sam
------------------------
To handle yourself, use your head. To handle others, use your heart
-
You should make sure that you created the package in the right place.
try running this and posting the results.
Code:
set linesize 90
column owner format a20
column object_name format a30
column object_type format a20
show user
select owner, object_name, object_type
from dba_objects
where object_name='SAMIR_TEST'
order by 1,2,3;
-
Originally Posted by gandolf989
You should make sure that you created the package in the right place.
try running this and posting the results.
Code:
set linesize 90
column owner format a20
column object_name format a30
column object_type format a20
show user
select owner, object_name, object_type
from dba_objects
where object_name='SAMIR_TEST'
order by 1,2,3;
Here is the result.
OWNER OBJECT_NAME OBJECT_TYPE STATUS
APPS SAMIR_TEST PACKAGE VALID
APPS SAMIR_TEST PACKAGE BODY VALID
Sam
------------------------
To handle yourself, use your head. To handle others, use your heart
-
DECLARE
p_employee_number number := 2000121;
BEGIN
apps.samir_test.samir_test_p ( p_employee_number);
END;
I get following error.
ORA-06550: line 7, column 3:
PLS-00905: object APPS.APPS is invalid
ORA-06550: line 7, column 3:
PL/SQL: Statement ignored
I wonder if this related to Oracle security. You might need to manually grant apps access to the package if you specify the schema owner, even if that is you. But because users can't grant themselves anything have sys do an explicit execute grant on this package to apps and try the original test again.
-
Originally Posted by gandolf989
I wonder if this related to Oracle security. You might need to manually grant apps access to the package if you specify the schema owner, even if that is you. But because users can't grant themselves anything have sys do an explicit execute grant on this package to apps and try the original test again.
As SYS user, I did grant EXECUTE on apps.samir_test to APPS and then executed original pl/sql block using apps. and still getting the same error.
Sam
------------------------
To handle yourself, use your head. To handle others, use your heart
-
you are also passing in a number - but your package is wanting a varchar2
-
Originally Posted by davey23uk
you are also passing in a number - but your package is wanting a varchar2
I changed to varchar2 in calling pl/sql block but that did not help.
Sam
------------------------
To handle yourself, use your head. To handle others, use your heart
-
Hi,
I found that this problem is not there in other instances. But, I am not sure what is the difference between this and other instances. We have not applied any DB patches recently on any instances.
Can you think of any db parameter that can be causing this issue?
Thanks,
Sam
------------------------
To handle yourself, use your head. To handle others, use your heart
-
Originally Posted by samdba
ORA-06550: line 7, column 3:
PLS-00905: object APPS.APPS is invalid
ORA-06550: line 7, column 3:
PL/SQL: Statement ignored
May I ask what APPS.APPS is?
Could you please check dba_objects for an object called APPS owned by APPS?
Could you please check for Synonyms for SAMIR_TEST package?
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
Originally Posted by PAVB
May I ask what APPS.APPS is?
Could you please check dba_objects for an object called APPS owned by APPS?
Could you please check for Synonyms for SAMIR_TEST package?
Yeh. I found there is an INVALID object called APPS owned by APPS user. This is totally odd. This must be causing the problem. I have asked the developer to fix it and then try again.
Thanks a lot for your help.
Sam
------------------------
To handle yourself, use your head. To handle others, use your heart
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
|