DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: Getting PLS-00905: object APPS.APPS is Invalid

Hybrid View

  1. #1
    Join Date
    Dec 2001
    Location
    USA
    Posts
    620

    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

  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    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;

  3. #3
    Join Date
    Dec 2001
    Location
    USA
    Posts
    620
    Quote Originally Posted by gandolf989 View Post
    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

  4. #4
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    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.

  5. #5
    Join Date
    Dec 2001
    Location
    USA
    Posts
    620

    Angry

    Quote Originally Posted by gandolf989 View Post
    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

  6. #6
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    you are also passing in a number - but your package is wanting a varchar2

  7. #7
    Join Date
    Dec 2001
    Location
    USA
    Posts
    620
    Quote Originally Posted by davey23uk View Post
    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

  8. #8
    Join Date
    Dec 2001
    Location
    USA
    Posts
    620
    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

  9. #9
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    Quote Originally Posted by samdba View Post
    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.

  10. #10
    Join Date
    Dec 2001
    Location
    USA
    Posts
    620
    Quote Originally Posted by PAVB View Post
    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
  •  


Click Here to Expand Forum to Full Width