Need to Create a Package from a Procedure

[LeonelSanchezJr]

I have a procedure in Oracle which I need to place in a package.

I have never done that, can someone please explain the steps involved in this please?

Thanks,

Leo ;-)

 

[SantaMufasa]

Leo,

There are two major pieces to an Oracle package:

1) The package header and
2) The package body

I shall give you a working example of both, below.

You can define in the package header the following types of PL/SQL objects:

1) variables
2) cursors
3) procedures
4) functions
5) probably other stuff that I've forgotten, but these are enough to get us started/going.

Anything that you define in the package header is visible/ referencable/usable by any users that have permission (GRANT) to use that package. Such objects are called global objects.

You can also define the above object types in the package body (but not in the package header), which means that the body-only objects are visible only within the package body (local objects). (See function named "cut", below. It is a local function, visible only to code within the package body.)

Here is a contrived example of a package that exemplifies a global variable, function, and two procedures; along with a local function. (BTW, I widened the thread display slightly so that there is in unsightly line wrapping.) :

Section 1 -- Package Header and package body

create or replace package leonel is
    x number;
    function junior (l varchar2) return varchar2;
    procedure sanchez1 (r varchar2);
    procedure sanchez2 (r varchar2);
end;
/

Package created.

create or replace package body leonel is
  function junior (l varchar2) return varchar2 is
      begin
          return lower(l);
      end;
  function cut(z varchar2) return varchar2 is
      begin
         return substr(z,1,leonel.x);
      end;
  procedure sanchez1 (r varchar2) is
      begin
          dbms_output.put_line(r);
      end;
  procedure sanchez2 (r varchar2) is
      begin
          dbms_output.put_line(cut(r));
      end;
end;
/

Package body created.

Section 2 -- Uses of the above package and its objects:

set serveroutput on
SQL> exec leonel.x := 10

PL/SQL procedure successfully completed.

SQL> exec leonel.sanchez1('THIS DISPLAYS THE VALUE OF "LEONEL.X": '||leonel.x)
THIS DISPLAYS THE VALUE OF "LEONEL.X": 10

PL/SQL procedure successfully completed.

SQL> exec leonel.sanchez1(leonel.junior('THIS DISPLAYS THE VALUE OF "LEONEL.X": '||leonel.x))
this displays the value of "leonel.x": 10

PL/SQL procedure successfully completed.

SQL> select leonel.junior('THIS IS UPPER-CASE, TURNING LOWER-CASE VIA THE FUNCTION.') SAMPLE
  2  from dual;

SAMPLE
-------------------------------------------------------------------------------------------
this is upper-case, turning lower-case via the function.

SQL> exec dbms_output.put_line('This is the still-visible value of LEONEL.X: '||leonel.x);
This is the still-visible value of LEONEL.X: 10

PL/SQL procedure successfully completed.

SQL> exec leonel.sanchez2('FirstXchrs only of this longer string.')
FirstXchrs

PL/SQL procedure successfully completed.

SQL> select leonel.cut('FirstXchrs only of this longer string.') from dual;
select leonel.cut('FirstXchrs only of this longer string.') from dual
       *
ERROR at line 1:
ORA-00904: "LEONEL"."CUT": invalid identifier
*********************************************************************************************

Notice these "features" of the above packaged code:

1) global, packaged functions are accessible via an external SQL statement.
2) the value of "LEONEL.X" (10), persists throughout the session.
3) "CUT", since it does not appear in the package header, is a locally-visible-only function; it works locally, but we cannot access it directly from outside the package.

Let us know if you have questions that persist about packages.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.