CODEWITHSUNDEEP
oracle-databasecopyschemaplsqldeveloper
Maija
Maija

Reputation: 1

Oracle SQL Developer package and package body copy and compile from one schema to another

I have several Oracle schemas including the same PL/SQL packages and package bodies.

If I make changes in one package in one schema, what is the best way to move these same changes to all other schemas as well ?

I can copy the PL/SQL code from one schema and then paste it to another schema and compile there, but if this has to be done tens of times it doesn't seem appropriate to me.

Upvotes: 0

Views: 2288

Answers (1)

MT0
MT0

Reputation: 167962

Don't have multiple copies of the same code.

Use:

GRANT EXECUTE ON USER_NAME.PACKAGE_NAME TO OTHER_USER;

To allow one user to own the code but allow other users to execute it.

If you need to run procedures as the calling user then set the invoker's rights to AUTHID CURRENT_USER rather than the default of AUTHID DEFINER in the procedures within the package.

Then you only need to maintain a single copy of the package.

Upvotes: 1

Related Questions