Invalid Procedures......
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 17

Thread: Invalid Procedures......

  1. #1
    Join Date
    Nov 2002
    Posts
    115

    Invalid Procedures......

    Hi all,

    I am getting INVALID PROCEDURES suddenly in my database. I think
    RPC execution has been terminated. What I have to do get the valid
    procedures in my database ?


    Thanx in Adv
    prasad.
    Prasad

  2. #2
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938

    Re: Invalid Procedures......

    @$ORACLE_HOME/rdbms/admin/utlrp.sql
    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g,12c
    email: ocp_9i@yahoo.com

  3. #3
    Join Date
    Nov 2002
    Posts
    115
    Hi

    When i run Utlrp.sql then some of the procedures get valid and others are not. What I have to do to compile invalid procedures ?
    Can i run CATPROC.SQL when the users are connected ? if u have any
    other script ,pls.send it.



    thanx in adv
    prasad.
    Prasad

  4. #4
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by prasadvd
    Hi

    When i run Utlrp.sql then some of the procedures get valid and others are not. What I have to do to compile invalid procedures ?

    prasad.
    This is wat oracle does when u run Utlrp.sql...

    Rem NAME
    Rem utlrp.sql - UTiLity script Recompile invalid Pl/sql modules
    Rem
    Rem DESCRIPTION
    Rem This is a fairly general script that can be used at any time to
    Rem recompile all existing invalid PL/SQL modules in a database.
    Rem

    I dont think recompling aging would bring them to valid state...
    find the root cause of procs being in invalid state...

    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  5. #5
    Join Date
    Nov 2002
    Location
    New Delhi, INDIA
    Posts
    1,796
    Originally posted by prasadvd
    Hi

    When i run Utlrp.sql then some of the procedures get valid and others are not. What I have to do to compile invalid procedures ?
    Can i run CATPROC.SQL when the users are connected ? if u have any
    other script ,pls.send it.



    thanx in adv
    prasad.
    No need to run catproc unless the invalid objects belong to sys...

    try compiling individual objects and see what error it shows after compiling...
    use show error...

    most possible reasons (other than logical error and syntax error) are lack of grants like select,insert,update,delete or execute.

    HTH
    Amar
    "There is a difference between knowing the path and walking the path."

    Amar's Blog  Get Firefox!

  6. #6
    Join Date
    Nov 2002
    Posts
    115
    Amar,

    I am getting following error when i try to execute invalid proc individually.

    Error at line 1:
    ora-01436:Connect by loop in user data
    ora-06512:at "SYS.DBMS_UTILITY" LINE 195
    ORA-06512:AT LINE 1

    Processing invalid object
    ===========================
    sqlplus kesav/kesav< prompt Compiling kesav Objects....
    execute dbms_utility.compile_schema('KESAV');
    show err;
    quit;
    eof;

    What is the solution u suggest ?

    prasad
    Prasad

  7. #7
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Originally posted by prasadvd
    Amar,

    I am getting following error when i try to execute invalid proc individually.

    Error at line 1:
    ora-01436:Connect by loop in user data
    ora-06512:at "SYS.DBMS_UTILITY" LINE 195
    ORA-06512:AT LINE 1

    Processing invalid object
    ===========================
    sqlplus kesav/kesav< prompt Compiling kesav Objects....
    execute dbms_utility.compile_schema('KESAV');
    show err;
    quit;
    eof;

    What is the solution u suggest ?

    prasad
    Run it as SYS (as sysdba)

    And if some objects cannot be compiled with UTLRP.SQL, then they have errors that need to be fixed.
    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g,12c
    email: ocp_9i@yahoo.com

  8. #8
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    sometimes you have to run utlrp several times to compile all invalid objects

  9. #9
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Originally posted by pando
    sometimes you have to run utlrp several times to compile all invalid objects
    Why?
    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g,12c
    email: ocp_9i@yahoo.com

  10. #10
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    Well what it happens to me many times is once I had 200 invalid objects in Orace Financials, after first utlrp it left with around 100 invalid objects, after second around 30 were left it was in the third utlrp which compilaed all succesfully.

    In fact when we release new procedures, functions and packages to production it is not unuusal es we have to run our own compile several times. So in our home-made compile script it cycles untl all objects are compiled

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