### Oracle stored procedure UDS

Due to the unique nature of Oracle stored procedures, you cannot add them directly into a JReport catalog. In this sense, the usage of JReport Designer is limited when you use an Oracle database. As a substitute, JReport has developed the user data source API which can use stored procedures in Oracle. The class is named OracleProcedureUDS, in the package jet.datasource.oracle.

To add a stored procedure into a JReport catalog:

1. Start JReport Designer and open the catalog to which you want to add it.
2. In the Catalog Browser, right-click the catalog data source the stored procedure is to be added, then 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 the parameter as required.
6. Click OK, and the UDS class will be added into the catalog.

#### Formats of the parameter string

In the Add User Defined Data Source dialog, the format of the parameter string is in either of the following. You can choose one according to your requirement. When the stored procedures used in Oracle have different names, either of the following two formats can be used. However, when the stored procedures used in Oracle have the same names, you can only use the second parameter string. In addition, when you use the second parameter string, the parameter value only contains value. You need to add the data type and index of the parameter value in the user defined data source class.

- `DRIVER=drivername&URL=url&USER=user&PSWD=password&OWNER=owner&PROCNAME=procname&SQL=sql&REFCURSORINDEX=index&PARAMVALUE=value1,value2,value3&OUTPARAMINDEX=index1,index2`
- `DRIVER=drivername&URL=url&USER=user&PSWD=password&OWNER=owner&PROCNAME=procname&SQL=sql&REFCURSORINDEX=index&INPARAMVALUE=value1,datatype1,index1;value2,datatye2,index2;…Valuen,datatypen,indexn&OUTPARAM=datatype1,index1;… datatypen,indexn`

Substitute the words in lower case according to your requirements.

Where:

- drivername - The name of JDBC driver, for instance: oracle.jdbc.driver.OracleDriver.
- url - A database URL of the form jdbc:subprotocol:subname, for instance: jdbc:oracle:thin:@dbserver:1521:orcl.
- user - The database user on whose behalf the Connection is being made, for instance: scott.
- password - The user's password, for instance: tiger.
- owner - The owner of the procedure, for instance: scott. It can also be left empty.
- procname - The name of the Stored Procedure, for instance: getAuthor.
- sql - The SQL statement used to call the procedure or function. For instance, with regards to procedure: {call getAuthor(?, ?)} and for function: {? = getAuthor(?, ?)}.
- index - The index of the output parameter with data type equal to Ref Cursor. The start index is 1.
- valueX - The value of the parameters separated by ',' and the data types must correspond to the ones defined in the DBMS. If valueX should contain a set of constant string values, for example, 10, 20, and 30 together serve as valueX, you will need to use '\\' to escape the comma.

  For example, if the PARAMVALUE part of the parameter string is as follows:
  ...&PARAMVALUE=10\\,20\\,30,30\\,10\\,23,,a,

  Then the parameter values will be parsed as follows:
  ParaValue1 =10,20,30
  ParaValue2 =30,10,23
  ParaValue3 =null
  ParaValue4 =a
  ParaValue5 =null

  Or, instead of typing the parameter values one by one in the above parameter string, you can also use the parameters and constant level formulas in the current catalog data source to substitute the parameter values, then '\\' will automatically be added to escape the comma. And it is recommended to use the parameters and constant level formulas to control one input parameter value of the oracle stored procedure. For example,
  ...&PARAMVALUE=@PARAM1,30\\,10\\,23,,a,

  In this case, in the Create/Edit Parameter dialog, the prompt value of parameter PARAM1 must be typed as follows:
  10,20,30

- datatypeX - The name of the data type mapping to JDBC. The name is the same as the static variable name in java.sql.types.
- IndexX - The index of the input parameter. It is natural number and integer.

Below is an example of the parameter string:

`DRIVER=oracle.jdbc.driver.OracleDriver&URL="jdbc:oracle:thin:@dbserver:1521:orcl" &USER=scott&PSWD=tiger&OWNER=SCOTT&PROCNAME=getAuthor&SQL={call getAuthor(?, ?, ?}&REFCURSORINDEX=2&PARAMVALUE=0.5,1999-7-10`

If you want to change the connection dynamically, you can define the parameters in this way:

`oracle.jdbc.driver.OracleDriver=@driver, jdbc:oracle:thin:@dbserver:1521:orcl=@url, scott=@user,`

The parameter string will then be either of the following:

- `DRIVER=@driver&URL=@url&USER=@user&PSWD=tiger&OWNER=SCOTT&PROCNAME=getAuthor&SQL={call getAuthor(?, ?,?}&REFCURSORINDEX=1&PARAMVALUE=0.5,1999-7-10`
- `DRIVER=@driver&URL=@url&USER=@user&PSWD=tiger&OWNER=SCOTT&PROCNAME=getAuthor&SQL={call getAuthor(?, ?,?}&REFCURSORINDEX=1&INPARAMVALUE=0.5,number,2;1999-7-10,date,3`

You can change the parameter dynamically to suit your requirements.

#### Troubleshooting

If you get an "ArrayOutOfBound" error or a "Not all variables are bound" error while importing a stored procedure, you can check the below points:

- Make sure the OWNER is correct.
- If your stored procedure is defined in Package/Package Body and the PROCNAME argument does not contain package name, but the call argument contains.
- Make sure that your REFCURSORINDEX has been set correctly.

  For example, if you have defined that the Out cursor is the third argument in your stored procedure, then REFCURSORINDEX should be 3.

When you get a NegativeArraySizeException error while running a report designed with a stored procedure, you can check whether some UDS columns return a negative scale (right-click the UDS column, select Properties, and then check Scale). If yes, it means that your original table column called by the stored procedure is likely to be of a Float data type. This returns the wrong Scale for the UDS column. Follow the steps below to modify the UDS column's scale:

1. Open the Catalog Browser and click the Options button on the toolbar.
2. In the Category box of the Options dialog, select Catalog, uncheck the option Forbid editing data object properties, and then click the OK button.
3. Highlight the UDS column name that you are having the problem with and click the Expand button on the toolbar to show the Properties sheet. Now you can change the property values, such as SQL type, precision, and scale to the correct values. For instance, you can change the negative scale of the problematic column to the correct value.

Notes:

- Your oracle.jdbc.driver should use at least ojdbc14.jar.
- 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 Server.

- When your parameter string contains characters such as: At sign(@), ':', double quotation mark('"'), or other strings that do not need to be parsed by JReport, you can use a pair of double quotation marks to quote them. For example, your parameter string may be:

  `jdbc:oracle:thin:@204.177.148.30:1521:orcl`

  Here, @ is a character used by the URL. If you add this parameter string into a catalog, JReport will regard 204 as the name of a parameter in the catalog. The correct form is:

  `"jdbc:oracle:thin:@204.177.148.30:1521:orcl"`

- The procedure must contain one and only 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. If it contains additional OUT parameters they will be ignored.
- The default format of Date and 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 Date Format tab of the Get JDBC Connection Information dialog.

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