Creating PL/SQL user-defined functions in a PL/SQL package

You can create PL/SQL user-defined functions in a PL/SQL package using the New PL/SQL Package wizard.

Procedure

To create a PL/SQL user-defined function from a data development project:

  1. In the Data Project Explorer, right-click the PL/SQL Packages folder schema, and click New > PL/SQL Package. The New PL/SQL Package wizard opens.
  2. Complete the steps of the wizard.

    The wizard creates the PL/SQL package and adds it to the PL/SQL Packages folder; and the PL/SQL package specification opens in the routine editor.

  3. In the specification, add the function name and variables. For example:
    CREATE OR REPLACE PACKAGE emp_admin
    IS
    
       ...
       FUNCTION SampleFunction RETURN integer;
       ...
    END emp_admin;
  4. Click the Body tab and edit the PL/SQL package body, adding the function. For example
    --
    --  Package body for the 'emp_admin' package.
    --
    CREATE OR REPLACE PACKAGE BODY emp_admin AS
       FUNCTION hire_emp (name VARCHAR2, job VARCHAR2,
          mgr NUMBER, hiredate DATE, sal NUMBER, comm NUMBER,
          deptno NUMBER) RETURN NUMBER IS
    END;
  5. Save the package.

Results

By default, creating the package does not register it on the database. To register the package on the database, you must deploy it.

A PL/SQL UDF can be overloaded only if all routines with the same name and type are in the same PL/SQL package. In a PL/SQL package, a procedure or function can be overloaded by another procedure or function of the same type and name, but with a different number of parameters. An overloaded procedure or function in a PL/SQL package, has the number of its parameters in parentheses:
Function(1)
Function(2)

After you have created the PL/SQL package, you can deploy and run the package.


Feedback