CODEWITHSUNDEEP
oracle-databasestored-proceduresplsqloracle11g
radu florescu
radu florescu

Reputation: 4343

Alter Procedure Package and Package Body PL/SQL

I want to make an incremetal script for my oracle database. I want to modify only one procedure signature and it's body and the rest should remain the same.

How can I accomplish this without recreating the hole package with create or replace?

Example

Old package

PROCEDURE LOAD_ITEMS(OUTCURSOR OUT SYS_REFCURSOR);

New package

PROCEDURE LOAD_ITEMS(P_ID IN CHAR, OUTCURSOR OUT SYS_REFCURSOR);

Edit:
By incremental script, I mean upgrade script from previous version to this one. And I want to modify only the current procedure, NOT ALL OF THEM.

Upvotes: 6

Views: 23897

Answers (1)

Peter Lang
Peter Lang

Reputation: 55514

If you want to change a procedure in the package specification, then you need to
CREATE OR REPLACE
the whole package specification and body with the changed code.

While the ALTER PACKAGE statement can be used to recompile the whole package, it

[...] does not change the declaration or definition of an existing package. To redeclare or redefine a package, use the CREATE PACKAGE or the CREATE PACKAGE BODY statement with the OR REPLACE clause.

Upvotes: 22

Related Questions