#### Adding an Oracle stored procedure

Suppose that the Oracle stored procedure is defined as below:

    Package SHDEMO as type curtype is ref cursor;
    end SHDemo;
    Procedure empquery (param1 in varchar2, cur OUT SHdemo.curtype)
    as
    lcur shdemo.curtype;
    begin
    open lcur for select * from EMP WHERE JOB = param1;
    cur:=lcur;
    end empquery;

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

1. Start JReport Designer and open the catalog to which you want to add the stored procedure.
2. In the Catalog Browser, expand the data source 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 any of the following three:
   - `DRIVER=oracle.jdbc.driver.OracleDriver&URL="jdbc:oracle:thin:@222.222.222.45:1521:orcl815"
     &USER=scott&PSWD=tiger&OWNER=SCOTT&PROCNAME=empquery&SQL={call empquery(?,?)}&REFCURSORINDEX=2&PARAMVALUE=@job1`
   - `DRIVER=oracle.jdbc.driver.OracleDriver&URL="jdbc:oracle:thin:@222.222.222.45:1521:orcl815"
     &USER=scott&PSWD=tiger&OWNER=SCOTT&PROCNAME=empquery&SQL={call empquery(?,?)}&REFCURSORINDEX=2&INPARAMVALUE=@job1`
   - `OWNER=SCOTT&PROCNAME=empquery&SQL={call empquery(?,?)}&REFCURSORINDEX=2&PARAMVALUE=@job1`
6. Click OK, and the UDS class will be added into the catalog.

Notes:

- The UDS class is in a package named jet.datasource.oracle. When it is being added into the Catalog Browser, it requires classes in package oracle.jdbc.driver. So before adding it, make sure the class path contains the package oracle.jdbc.driver.
- In the PARAMETER string,
  - If you do not specify the connection information, the connection defined on the data source, to which the stored procedure UDS is added will be used as the default connection. In this scenario, use the third parameter string listed above, and the connection can be changed at runtime via datasource.xml, Engine API or Server API in JReport Enterprise Serve.
  - If you use the second PARAMETER string listed above, make sure the parameter value @job1 contain value, datatype, and index.
- The procedure must and can only contain one OUT parameter of the REF CURSOR type. If the procedure doesn't have such a parameter, it cannot be added into the JReport catalog since JReport cannot create a report without a ResultSet.
- The default format of Date or Time data type is:

  Date: yyyy-MM-dd
  Time: hh:MM:ss
  Timestamp: yyyy-MM-dd hh:MM:ss.SSS

  You can modify the format in the Get JDBC Connection Information dialog.

The following are some specific examples of adding Oracle stored procedure UDS to a catalog:

- Example 1: Stored procedures defined outside of a package
- Example 2: Stored procedures declared & defined in a package & package body
- Example 3: Functions defined outside of a package
- Example 4: Functions declared & defined in a package & package body