##### Example 4: Functions declared & defined in a package & package body

Suppose that the Oracle stored procedure is defined as:

    CREATE OR REPLACE PACKAGE "SCOTT"."SHDEMO_PKG" AS
    type curtype is ref cursor;
    Procedure empquery_proc (p_job in varchar2, cur OUT SHDEMO_PKG.curtype);
    Function deptquery_func (p_dname in varchar2) return SHDEMO_PKG.curtype;
    end SHDEMO_PKG;
    CREATE OR REPLACE PACKAGE BODY "SCOTT"."SHDEMO_PKG" AS
    Procedure empquery_proc (p_job in varchar2, cur OUT SHDEMO_PKG.curtype)
    as
    lcur SHDEMO_PKG.curtype;
    begin
    open lcur for
    select * from EMP WHERE JOB = p_job;
    cur:=lcur;
    end empquery_proc;
    Function deptquery_func (p_dname in varchar2) return SHDEMO_PKG.curtype
    as
    lcur SHDEMO_PKG.curtype;
    begin
    open lcur for
    select * from dept where dname = p_dname;
    return lcur;
    end deptquery_func;
    end SHDEMO_PKG;

To add the stored procedure into a JReport catalog, follow the steps below:

1. Start JReport Designer and open an existing catalog.
2. In the Data tab of the Catalog Browser, expand the data source to which the stored procedure is to be added, then right-click the User Defined node and select Add User Defined Data Source.
3. In the Name field of the Add User Data Source dialog, specify a name for the UDS.
4. In the Class Name field, input the UDS class jet.datasource.oracle.OracleProcedureUDS.
5. In the Parameters box, enter one of the following:
   - `DRIVER=oracle.jdbc.driver.OracleDriver&URL="jdbc:oracle:thin:@192.168.126.38:1521:oracle8"&USER=scott&PSWD=tiger&
     OWNER=SCOTT&PROCNAME=deptquery_func&SQL={? = call shdemo_pkg.deptquery_func(?)}&REFCURSORINDEX=1&PARAMVALUE="SALES"`
   - `DRIVER=oracle.jdbc.driver.OracleDriver&URL="jdbc:oracle:thin:@192.168.126.38:1521:oracle8"&USER=scott&PSWD=tiger&
     OWNER=SCOTT&PROCNAME=deptquery_func&SQL={? = call shdemo_pkg.deptquery_func(?)}&REFCURSORINDEX=1&INPARAMVALUE="SALES",varchar,2`
   - `OWNER=SCOTT&PROCNAME=deptquery_func&SQL={? = call shdemo_pkg.deptquery_func(?)}&REFCURSORINDEX=1&PARAMVALUE="SALES"`
6. Click the OK button. The UDS class will then be added into the catalog.