Menu
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.
750
Because all objects in a package are stored as a unit, the ALTER PACKAGE statement recompiles all package objects. You cannot use the ALTER PROCEDURE statement or ALTER FUNCTION statement to recompile individually a procedure or function that is part of a package.
The specification is the interface to the package. It declares the types, variables, constants, exceptions, cursors, and subprograms that can be referenced from outside the package. The body defines the queries for the cursors and the code for the subprograms.
To create or replace a package in your schema, you must have the CREATE PROCEDURE system privilege. To create or replace a package in another user's schema, you must have the CREATE ANY PROCEDURE system privilege. In both cases, the package body must be created in the same schema as the package.
If you want to change a procedure in the package specification, then you need toCREATE 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 PACKAGEor theCREATE PACKAGE BODYstatement with theOR REPLACEclause.
60
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
