Oracle - Using Package

Question

I have read some PL SQL programming books, and they recommend me to group procedures, functions, cursors, and so on in a package. Packages provide modularity and information hiding, which is the OO design. However, I'm just familiar with stand alone procedures. Would anyone kindly provide some examples in code and how to call package from the client? I'm currently using ODP.NET as a data access in client application. Thank you.

Answer 1

If you're familiar with calling stand alone procedures, calling procedures in packages is not very different. Just prefix the procedure name with the package name like this: package_name.procedure_name.

Answer 2

To follow up, you might have a standalone like

create or replace procedure foo (i_something in varchar2) as
begin
   -- do some stuff;
end foo;

which you call with "foo('bar');"

That would become a package and a package body as

create or replace package my_package as
   procedure foo (i_something in varchar2);
end;

create or replace package body my_package as
   procedure foo (i_something in varchar2);
   begin
      -- do some stuff;
   end foo;
end my_package;

which you call with "my_package.foo('bar');"

For a single procedure, using a package might not add much clarity. But if you're dealing with lots of functions and procedures, it's much cleaner.

Answer 3

If there is only one procedure in a package, it seems like no need to have package.

I'm still not clear what type of procedures or functions that I have to group in a package. Would you mind telling me what procedures should I group into a package?

Answer 4

You put code for the procedures, functions etc in the package body, together with declarations for private variables. You put the declarations of those procedures and functions, as well as public variables, in the package. Only the latter are accessible from outside the package, and for that you need to prefix their names with the package name and a dot.

You can define data stucture types for this package too: if public procedures use them as input or output, then they must be made public; if not, then you're free to choose.

Example code, from memory and not tested, so there could be bugs in it:

create or replace package foo as
  a number;         
  function test1(s1 in varchar2) return varchar2;
  procedure test2(i1 in integer);
end;
/

create or replace package body foo as
  b number;  -- internal only
  function internalfunc(s in varchar2) return varchar2;

  function test1(s1 in varchar2) return varchar2 is
    s varchar2(32000);
    -- variables ...
  begin
    -- code ...
    return internalfunc(s);
  end;

  procedure test2(i1 in integer) is
    -- variables ...
  begin
    -- code ...     
  end;

  function internalfunc(s in varchar2) return varchar2 is
  begin
    return INITCAP(LOWER(s));    
  end;

end;
/

Use foo.a, foo.test1 etc to use these functions and variables. You can not access internal functions from outside the package. They only need to be declared early in the package body if they're being called higher up than their implementation.

Packages have an extra advantage over plain functions and procedures: you can overload their names, have several different procedures with the same name but with different parameter types in the same package, and the proper function will be called depending on what parameter types you use. By contrast, the name for a standalone function or procedure must be unique.

HTH.

Answer 5

There is still the good practice of using packages instead of just declaring functions with global scope.

If you have packages, it can provide extensibility, if you happen to want later to add functions with the same context, and it can make your procedures more legible, as it states from which library (package) is the function called from.