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. A function in a package can be overloaded by another function with same number of parameters if the data type of one of the parameters is different. An overloaded procedure or function in a PL/SQL package, has the number of its parameters in parentheses:
For procedure, the engine just validate the parameter number and procedure name, does not care parameter data type whatever it is IN, IN OUT and OUT.

For function, the engine validate both the parameters data type, number and function name. 
Function(1)
Function(2)

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


Feedback