I don’t understand when I can simply create multiple procedures in my DB. Why do I need to create a package?
Why do we use packages in Oracle when we can use procedures?
Share
Sign Up to our social questions and Answers Engine to ask questions, answer people's questions, and connect with other people.
Lost your password? Please enter your email address. You will receive a link and will create a new password via email.
I don’t understand when I can simply create multiple procedures in my DB. Why do I need to create a package?
Sweta
Packages provide the following advantages:
Cohesion: all the procedures and functions relating to a specfic sub-system are in one program unit. This is just good design practice but it’s also easier to manage, e.g. in source control.
Constants, sub-types and other useful things: there’s more to PL/SQL than stored procedures. Anything we can define in a package spec can be shared with other programs, for instance user-defined exceptions.
Overloading: the ability to define a procedure or function with the same name but different signatures.
Security: defining private procedures in the package body which can only be used by the package because they aren’t exposed in the specification.
Sharing common code: another benefit of private procedures.
We only need to grant EXECUTE on a package rather than on several procedures.
As described in Oracle docs, packages are good because of:
Details on each reason are explained in docs.
Hope this helps.
Urmila Pal
Whenever you call a procedure or any subprogram in your code it will be loaded into the cache memory after the compilation process. And only after that, it can result in your desired output.
There are some advantages of packages over procedures:
Whereas if you write multiple procedures they will take their separate compilation time which can reduce your work performance and can increase the wait time.
2. Re-compilation: There is no need to re-compile the package unless the specification of the package is also changed along with the body of the package.
3. I/O parameters: In case of packages, once the package has been loaded in the memory there is no need to specify the input-output parameters.
On the other hand, procedures will need their separate input-output parameters.
I hope I was able to explain the differences. If you like this answer, please like and share my answer.