Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2002
    Location
    London, UK
    Posts
    14

    Unanswered: invalid packages

    What could be the possible reasons why my oracle packages become invalid every so often? I end up compiling them everyday first thing in the morning, only to find them invalidated by the end of the day?

    Any explanations would be appreciated, since I am totally intrigued by this!!!

    Thanks in advance

    A DBA in distress!!!

  2. #2
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    This means someone is altering (or recompiling) objects which the oracle packages depend upon. I doubt anyone is altering oracles internal tables but someone might recompile an oracle package causing other packages to become invalid.

    Alan

  3. #3
    Join Date
    Jul 2003
    Posts
    2,296
    Originally posted by AlanP
    This means someone is altering (or recompiling) objects which the oracle packages depend upon. I doubt anyone is altering oracles internal tables but someone might recompile an oracle package causing other packages to become invalid.

    Alan
    I thought the whole point of packages (instead of Procedures alone) was that packages are not dependent upon other packages while PROCEDURES are dependent upon each other.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  4. #4
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Packages just contain a whole load of procedures. They can still go invalid. As you rightly say there is some amount of work Oracle can do to distinguish the header from the body to decide when and what becomes invalid but this is mostly down to package body recompilation, not alteration of objects on which a package relies.

    I would say though that this benefit is outweighed by the disadvantage of the invalidation caused by package state (package variables) which are session dependant. Package variables are a wonderful tool, but have these drawbacks.

    It's six of one and half a dozen of another.

    Cheers
    Bill

  5. #5
    Join Date
    Apr 2003
    Location
    Washington DC area
    Posts
    1,770

    Unhappy invalid packages

    Hey Gang,
    has anyone use the alter_compile procedure to turn packages
    back
    to valid status?

  6. #6
    Join Date
    Dec 2002
    Location
    London, UK
    Posts
    14
    My packages were getting invalidated because they were created by copying the vendor supplied wrapped PL-SQL scripts from another schema.

    I haven't had any trouble ever since dropped the packages and recreated them using Exp/Imp.

    Thanks guys.... for your inputs, anyway!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •