Sunday, 6 April 2014

Packages in PL/SQL

Brief :
       "Packages" word only come up with Oracle PL/SQL.

As Microsoft fan , googling about " Packages in SQL Server "..
Actually their will be no dependency on that ..(LOLzz)

We are working on huge database , or rapid increases of Stored procedures , functions etc (proliferation) .
It will be difficult to manage it.
Packages can save time to describe procedure / function schema.

What is Packages in Oracle ?

If you observed any document related to Packages , let you know about "Interface in OOP" and if you search packages in SQL Server,you will get references related to SSIS , Data flow , control flow  etc.
(I am also beginner to all this..)
Some advantages tells that , why you need it ?

I found packages to be really useful for a number of reasons:
  1. 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.(We can said it to be Cohesion)
  2. 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.
  3. Overloading: the ability to define a procedure or function with the same name but different signatures.
  4. Security: defining private procedures in the package body which can only be used by the package because they aren't exposed in the specification.
  5. Sharing common code: another benefit of private procedures.
  6. We only need to grant EXECUTE on a package rather than on several procedures.

While SQL Server has nothing to offer by way of the "cool features" of encapsulation and package state like you are used to, you can organize your stored procedures into schemas.

In enterprise manager, these procs are still all listed together which makes for a HUGE treelist if you have hundreds of procs. I too miss the organization and cool features of Oracle packages. However, all platforms have their strengths.if we 

[note : Please correct us, if wrong somewhere]
Reference link for Oracle Packages