What is the difference between stored procedure and standalone procedure in Oracle?

Question

standlone procedure

create procedure proc1
(
begin

end;
)

stored procedure

create package pkg1
(
procedure proc2
begin

end;
)

Answer 1

These are both stored procedures, as they are both stored in the database and can be called later.

Putting procedures in a package is just a neat way to organize them. It helps remembering to update all the relevant procedures together, keep your creation scripts tidy, etc. The main functional difference is the ability to grant and revoke privileges on an entire package, instead of having to manage a dozen "stand-alone" procedures independently.

Answer 2

From the oracle documentation for CREATE PROCEDURE

A standalone procedure is a procedure (a subprogram that performs a specific action) that is stored in the database.

A nested procedure is a procedure that is in a PL/SQL block or a package.

From the CREATE PACKAGE documentation:

The CREATE PACKAGE statement creates or replaces the specification for a stored package, which is an encapsulated collection of related procedures, functions, and other program objects stored as a unit in the database. The package specification declares these objects. The package body, specified subsequently, defines these objects.

Standalone procedures and procedures nested in a package are both stored (compiled) within the database - so are "stored" procedures. Procedures defined in an anonymous PL/SQL block are not "stored" procedures.

This is not a stored procedure:

DECLARE
  n NUMBER := 1;

  PROCEDURE incr( a IN OUT NUMBER ) IS
  BEGIN
    a := a + 1;
  END;
BEGIN
  incr(n);
  DBMS_OUTPUT.PUT_LINE(n);
END;
/

There is not a huge difference between nested procedures in packages and standalone procedures:

• A standalone procedure is defined using CREATE PROCEDURE ... whereas a nested procedure is defined within the PL/SQL block using PROCEDURE ....
• A standalone procedure always requires a procedure definition (the AS BEGIN ... END; part) but a (public) nested procedure in a package only declares the procedure heading (the PROCEDURE NAME(...) part) and then in the package body will restate the heading and define the procedure definition.
• Nesting a procedure in a package allows it to be grouped with similar functionality and allows it to access functions, procedures and data which is private to the package (i.e. defined in the package body but not in the public package specification).