Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: publish a pl/sql pkg

Re: publish a pl/sql pkg

From: Remigiusz Sokolowski <>
Date: Mon, 17 Sep 2007 14:12:11 +0200
Message-id: <>

Alberto Dell'Era wrote:
> I would (as per standard practice in many shops)
> a) create a user for each application (or group of applications)
> that needs to use the package - that way you will always be able
> to trace who is using your package. Say users APP_1, APP_2,..
> b) grant execute on your package to APP_X
> c) [maybe] create a private synonym on APP_X that points to
> the package - just to make life easier for them and hiding
> the schema name in which the package resides.
> I would create a web service only if required by the apps,
> no need to introduce yet another non-performant layer (and frankly,
> code that calls a package directly is much easier to write
> and maintain than the equivalent using web services).
> Maybe if some applications are J2EE, I might consider creating
> a mapping EJB (one method for each procedure/function) -
> but I would still prefer direct JDBC calling (for simplicity and performance).
> "simplicity" for both you and the applications developers.
> Al

I would add to that (as options) few more steps: - creating role for all needed privileges (assuming Your pkgs are not used in some PL/SQL code in other schemas of course), which first of all will ease Your mind as to privileges management issue (and has some positive side effects)
- adding short prefix before Your packages (especially if You need to use many different apps within one schema) - and as an option to the option above using public synonyms, which is sometimes comfortable, but should be considered with more caution

and as a warning
- using only packages even for code which does not seem to be needed in such form - which in turn pays off at upgrade time (in case of changing API You may need and use overloading)


Remigiusz Sokolowski <>
WP/PTI/DIP/ZAB (+04858) 52 15 770
MySQL v04.x,05.x; Oracle v10.x

Niniejsza wiadomosc stanowi jedynie wyraz prywatnych pogladow autora 
i nie jest w zadnym wypadku zwiazana ze stanowiskiem przedsiebiorstwa 
Wirtualna Polska S.A.

WIRTUALNA  POLSKA  SA, ul. Traugutta 115c, 80-226 Gdansk; NIP: 957-07-51-216; 
Sad Rejonowy Gdansk-Polnoc KRS 0000068548, kapital zakladowy 62.880.024 zlotych (w calosci wplacony)
Received on Mon Sep 17 2007 - 07:12:11 CDT

Original text of this message