CODEWITHSUNDEEP
oracle-databaseplsql
Sambath Prum
Sambath Prum

Reputation: 1888

Oracle - Using Package

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.

Upvotes: 1

Views: 2341

Answers (4)

Manuel Ferreria
Manuel Ferreria

Reputation: 1232

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.

Upvotes: 1

bart
bart

Reputation: 7767

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.

Upvotes: 2

Jim Hudson
Jim Hudson

Reputation: 8069

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.

Upvotes: 1

Eddie Awad
Eddie Awad

Reputation: 3729

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.

Upvotes: 3

Related Questions