Adding an Oracle stored procedure

Suppose that the Oracle stored procedure is defined as follows:

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;

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 Defined 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 Parameter 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,varchar,1
   • OWNER=SCOTT&PROCNAME=empquery&SQL={call empquery(?,?)}&REFCURSORINDEX=2&PARAMVALUE=@job1
6. Click OK, and the UDS class will be added into the catalog.

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

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