#### The UDS class

To use stored procedures in Oracle with JReport Designer, JReport developed a user data source class. The class is named OracleProcedureUDS, and can be found in the package jet.datasource.oracle.

In the Add User Data Source dialog, the format of the PARAMETER string is in either of the following:

- `DRIVER=drivername&URL=url&USER=user&PSWD=password&OWNER=owner&PROCNAME=procname&SQL=
  sql&REFCURSORINDEX=index&PARAMVALUE=value1,value2,value3`
- `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 parameter separated by ',' and the data types must correspond to the ones defined in the DBMS. If valueX should contain a set of 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 in the catalog to substitute the parameter values. For example,
  ...&PARAMVALUE=@PARAM1,30\\,10\\,23,,a,

  In this case, in the Create/Edit Parameter dialog, the prompting 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.

##### 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=2&PARAMVALUE=0.5,1999-7-10`
- `DRIVER=@driver&URL=@url&USER=@user&PSWD=tiger&OWNER=SCOTT&PROCNAME=getAuthor&SQL={call getAuthor(?, ?,?}&
  REFCURSORINDEX=2&INPARAMVALUE=0.5,datatype,index;1999-7-10,datatype,index`

You can change the parameter dynamically to suit your requirements.

Note: 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"`