Sobes.tech
Middle

Tell about packages in Oracle: package specification and body, differences, can one be without the other?

sobes.tech AI

Answer from AI

In Oracle, a package is a database object that groups related procedures, functions, variables, and cursors into a single unit.

Package specification — this is the package interface. It declares all public elements: procedures, functions, types, constants, variables, which will be accessible from other parts of the program. The specification defines what the package provides but does not contain the implementation.

Package body — this is the implementation, describing the logic of procedures and functions declared in the specification. The body can also contain private (local) procedures and functions, inaccessible from outside.

Differences:

  • The specification declares the interface, the body — the implementation.
  • The specification is mandatory for creating a package, the body — optional.

Can one exist without the other?

  • The specification can exist without a body if the package contains only declarations of constants, types, variables, or if procedures and functions are declared but not implemented (for example, for calling external procedures).
  • The package body cannot exist without the specification, as it implements what is declared in the specification.

Example:

CREATE OR REPLACE PACKAGE my_package IS
  PROCEDURE proc1;
  FUNCTION func1 RETURN NUMBER;
END my_package;
/

CREATE OR REPLACE PACKAGE BODY my_package IS
  PROCEDURE proc1 IS
  BEGIN
    NULL;
  END proc1;

  FUNCTION func1 RETURN NUMBER IS
  BEGIN
    RETURN 42;
  END func1;
END my_package;
/