The JDBC driver enables you to use stored procedures. The ability to use the <jdbc:call-procedure> and <jdbc:call-function> elements to call stored procedures from a policy has been tested only against Oracle and is supported only on that platform.
You can call stored procedures or functions in one of two ways:
Call the procedure or function by using a Statement object.
Call the procedure by using a Callable Statement object.
<!-- call syntax is Oracle -->
<jdbc:statement>
<jdbc:sql>CALL schema.procedure-name</jdbc:sql/>
</jdbc:statement>
<!-- call syntax is vendor agnostic -->
<jdbc:statement>
<jdbc:call-procedure jdbc:name="schema.procedure-name"/>
</jdbc:statement>
<!-- call syntax is Informix -->
<jdbc:statement>
<jdbc:sql>EXECUTE FUNCTION schema.function-name</jdbc:sql/>
</jdbc:statement>
<!-- call syntax is vendor agnostic -->
<jdbc:statement>
<jdbc:call-function jdbc:name="schema.function-name"/>
</jdbc:statement>
The principle advantage of using the CallableStatement interface is that you do not need to know the proprietary call syntaxes of each database vendor or JDBC implementation. Other advantages include the following:
It's much easier to build procedure or function calls in the Policy Builder.
You can differentiate between Null and empty string parameter values.
You can call functions on all database platforms.
Oracle, for instance, doesn't support calling functions by using a statement.
You can retrieve Out parameter values from stored procedure calls.
Stored procedures do not necessarily require parameters. Only a name is required. If a database supports schemas, we recommend that you schema-qualify the name. If a schema qualifier isn't provided, how names are resolved depends upon your third-party JDBC implementation and might change, depending upon driver configuration settings.
The jdbc:call-procedure element must be wrapped in a jdbc:statement element.
<jdbc:call-procedure jdbc:name="schema.procedure-name"/>
The number of jdbc:param elements specified must match the number of param elements declared in the procedure. Only jdbc:param elements corresponding to In or In Out procedure parameters can have values. Out parameters (those that can't be passed values) must be represented by an empty jdbc:param element.
<jdbc:statement>
<jdbc:call-procedure jdbc:name="schema.procedure-name"/>
</jdbc:statement>
<jdbc:call-procedure jdbc:name="schema.procedure-name">
<!-- no value element = pass null -->
<jdbc:param/>
</jdbc:call-procedure>
<jdbc:call-procedure jdbc:name="schema.procedure-name">
<!-- empty value element = pass empty string -->
<jdbc:param>
<jdbc:value/>
</jdbc:param>
</jdbc:call-procedure>
Literals can be passed only to procedure parameters declared as In or In Out. Passed literals must be type-compatible with declared procedure parameters.
<jdbc:call-procedure jdbc:name="schema.procedure-name">
<!-- non-empty value element = pass literal -->
<jdbc:param>
<jdbc:value>literal</jdbc:value>
</jdbc:param>
<jdbc:param>
Assuming that a procedure has two parameters, the first Out and the second In, you invoke the procedure as follows:
<jdbc:call-procedure jdbc:name="schema.procedure-name">
<!-- the OUT param place -->
<jdbc:param/>
<!-- the IN param -->
<jdbc:param>
<jdbc:value>literal</jdbc:value>
</jdbc:param>
<jdbc:param>
Stored procedures with Out or In Out parameters can return values. These values are returned by the driver and are accessible to policies. Out or In Out parameters values are returned at the same position as their corresponding declared parameter.
Also, to facilitate correlation of procedure calls and output parameter values, Out parameters contain the same event-ID value as the procedure call that generated them. This is particularly useful when multiple calls are made in the same document.
Assuming that a procedure has a single Out or In parameter, the following output is generated:
<output>
<!-- no value element = OUT param returned null or IN param -->
<jdbc:out-parameters event-id="0" jdbc:number-of-params="1">
<jdbc:param/>
</jdbc:out-parameters>
<status event-id="0" level="success"/>
</output>
Assuming that a procedure has a single Out or In Out parameter, the following output is generated:
<output>
<!-- empty value element = returned empty string -->
<jdbc:out-parameters event-id="0" jdbc:number-of-params="1">
<jdbc:param>
<jdbc:value/>
</jdbc:param>
</jdbc:out-parameters>
<status event-id="0" level="success"/>
</output>
Assuming that a procedure has a single Out or In Out parameter, the following output is generated:
<output>
<!-- no-empty value element = returned literal value -->
<jdbc:out-parameters event-id="0" jdbc:number-of-params="2">
<jdbc:param>
<jdbc:value>literal<jdbc:value>
</jdbc:param>
</jdbc:out-parameters>
<status event-id="0" level="success"/>
</output>
This procedure uses Oracle PSQL syntax.
CREATE PROCEDURE indirect.p1(i1 IN VARCHAR2, io2 IN OUT VARCHAR2, o3 OUT INTEGER, i4 IN VARCHAR2)
AS
BEGIN
SELECT 'literal' INTO io2 FROM DUAL;
SELECT 1 INTO o3 FROM DUAL;
END p1;
<input>
<jdbc:statement event-id="0">
<jdbc:call-procedure jdbc:name="indirect.p1">
<!-- i1 IN VARCHAR2 -->
<jdbc:param>
<!-- pass empty string -->
<jdbc:value/>
</jdbc:param>
!-- io2 IN OUT VARCHAR2 -->
<jdbc:param>
<!-- pass literal -->
<jdbc:value>literal</jdbc:value>
</jdbc:param>
<!-- o3 OUT INTEGER -->
<!-- param placeholder -->
<jdbc:param/>
<!-- o4 IN VARCHAR2 -->
<!-- pass null -->
<jdbc:param/>
</jdbc:call-procedure>
</jdbc:statement>
</input>
<output>
<jdbc:out-parameters event-id="0" jdbc:number-of-params="2">
<jdbc:param/>
<jdbc:param jdbc:name="IO2"
jdbc:param-type="INOUT"
jdbc:position="2"
jdbc:sql-type="java.sql.Types.VARCHAR">
<jdbc:value>literal</jdbc:value>
</jdbc:param>
<jdbc:param jdbc:name="O3"
jdbc:param-type="OUT"
jdbc:position="3"
jdbc:sql-type="java.sql.Types.DECIMAL">
<jdbc:value>1</jdbc:value>
</jdbc:param>
<jdbc:param/>
</jdbc:out-parameters>
<status event-id="0" level="success"/>
</output>
Functions do not necessarily require parameters. Only a name is required. If a database supports schemas, we recommend that you schema-qualify the name. If a schema qualifier isn't provided, how names are resolved depends upon your third-party JDBC implementation and might change depending upon driver configuration settings.
The jdbc:call-function element must be wrapped in a jdbc:statement element.
<jdbc:call-function jdbc:name="schema.function-name"/>
The number of jdbc:param elements specified must match the number of params declared in the function.
<jdbc:call-function jdbc:name="schema.function-name"/>
<jdbc:call-function jdbc:name="schema.function-name">
<!-- no value element = null -->
<jdbc:param/>
</jdbc:call-procedure>
<jdbc:call-function jdbc:name="schema.function-name">
<!-- empty value element = pass empty string -->
<jdbc:param>
<jdbc:value/>
</jdbc:param>
<jdbc:param>
Literals can be passed to function parameters declared as In. Passed literals must be type-compatible with declared function parameters.
<jdbc:call-function jdbc:name="schema.function-name">
<!-- non-empty value element = pass literal -->
<jdbc:param>
<jdbc:value>literal</jdbc:value>
</jdbc:param>
<jdbc:param>
Unlike stored procedures, functions do not support Out or In Out parameters. They can, however, return a single, scalar value (such as an integer or string) or return a result set. Also, to facilitate correlation of function calls and results, results contain the same event-id value as the function call that generated them. This is particularly useful when multiple calls are made in the same document.
Scalar return values are returned by using the same syntax as stored procedure Out parameters. The scalar return value is always returned in the first parameter position.
<output>
<jdbc:out-parameters event-id="0" jdbc:number-of-params="1">
<jdbc:param jdbc:name="return value"
jdbc:param-type="OUT"
jdbc:position="1"
jdbc:sql-type="java.sql.Types.VARCHAR">
<jdbc:value>1</jdbc:value>
</jdbc:param>
</jdbc:out-parameters>
<status event-id="0" level="success"/>
</output
Assuming that a function returns no results set or an empty result set, the following output is generated:
<output>
<jdbc:result-set event-id="0" jdbc:number-of-rows="0"/>
<status event-id="0" level="success"/>
</output>
Assuming a function returns a non-empty result set, output similar to the following is generated:
<output>
<jdbc:result-set event-id="0" jdbc:number-of-rows="1">
<jdbc:row jdbc:number="1">
<jdbc:column jdbc:name="SYSDATE"
jdbc:position="1
jdbc:type="java.sql.Types.TIMESTAMP">
<jdbc:value>2007-01-03 14:52:20.0</jdbc:value>
</jdbc:column>
</jdbc:row>
</jdbc:result-set>
<status event-id="0" level="success"/>
</output>
Multiple result sets are returned in the order returned by the function. They all share a common event-id value.
<output>
<jdbc:result-set event-id="0" jdbc:number-of-rows="0"/>
<jdbc:result-set event-id="0" jdbc:number-of-rows="0"/>
<status event-id="0" level="success"/>
</output>
Oracle's JDBC implementation uses a proprietary mechanism to return a single result set from a function. To return a result set from an Oracle function, you need to explicitly set the jdbc:return-type value to OracleTypes.CURSOR on the jdbc:call-function element.
See the special attribute jdbc:return-format.
This attribute can be placed on the jdbc:call-function element to format the first row of a returned results set as stored procedure Out parameters of the result.
This works only when the jdbc:return-type attribute isn't used.
<input>
<jdbc:statement>
<jdbc:call-function jdbc:name="schema.function-name"
jdbc:return-format="return value">
</jdbc:call-function>
</jdbc:statement>
</input>
This attribute can be placed on the jdbc:call-function element to allow Oracle functions to return a result set.
<input>
<jdbc:statement>
<jdbc:call-function jdbc:name="schema.function"
jdbc:return-type="OracleTypes.CURSOR">
</jdbc:call-function>
</jdbc:statement>
</input>
This declaration is for Oracle PSQL syntax.
CREATE OR REPLACE FUNCTION indirect.f1(i1 IN VARCHAR2, i2 IN INTEGER) RETURN VARCHAR2 AS o_idu VARCHAR2(32); BEGIN SELECT 'literal' INTO o_idu FROM DUAL; RETURN o_idu; END f1;
<input>
<jdbc:statement>
<jdbc:call-function jdbc:name="indirect.f1">
<jdbc:param>
<jdbc:value>literal</jdbc:value>
</jdbc:param>
<jdbc:param>
<jdbc:value>1</jdbc:value>
</jdbc:param>
</jdbc:call-function>
</jdbc:statement>
</input>
<output>
<jdbc:out-parameters event-id="0" jdbc:number-of-params="1">
<jdbc:param jdbc:name="return value"
jdbc:param-type="OUT"
jdbc:position="1"
jdbc:sql-type="java.sql.Types.VARCHAR">
<jdbc:value>literal</jdbc:value>
</jdbc:param>
</jdbc:out-parameters>
<status event-id="0" level="success"/>
</output>