4

As far as I know a package body can be replaced and recompiled without affecting the specification. A package specification declares procedures and functions, not defines them, so they can not reference objects, that can make the package specification INVALID.

I know that a package specification can reference objects when it uses stand-alone subprograms and other packages to define it's variables. In this case changing referenced objects may cause the specification invalidation.

Is there any other way how an Oracle package specification can depend on (reference) objects and become INVALID whether when referenced objects chаnge or another way?

chumakoff
  • 6,807
  • 2
  • 23
  • 45

1 Answers1

2

In specification there can be defined variable or type. If variable is table.column%type package specification can be affected by any ddl operation on the table used for defining variable. The same situation is when in package header we define cursor.

I would also be careful with synonyms swapping both in case of table referenced by variable definition and type used in header.

Next scenario are privileges. If owner of package will loose some grants (lets say due to table recreating) package spec can also go invalid.

I hope what I'm writing make sense.

Kacper
  • 4,798
  • 2
  • 19
  • 34
  • Thank you for your answer. When table.column%type is used as a type of an argument of a subprogram declared in the spec, will a DDL operation on the table affect the spec. If so, mention this in your answer, please. – chumakoff Oct 28 '16 at 09:15
  • @chumakoff I'm not sure what do you mean by subprogram in package specification. But generally if you have such reference %type (or rowtype) in package declarations answer is yes it will be affected. – Kacper Oct 28 '16 at 09:22
  • Subprogram is a procedure or a function. When you declare it in a package spec, you should describe its signature, that is types of arguments and type of returning value. For exampe: PROCEDURE my_proc(id employees.employee_id%TYPE) – chumakoff Oct 28 '16 at 09:33
  • @chumakoff yes if you have parameter col%type in header of procedure package spec will get invalid after alter on the table. – Kacper Oct 28 '16 at 10:10