Executing stored procedures

["Rao, Satish" <sa...@fmr.com>]

I have a query with 3 input parameters to the WHERE clause and the query
returns a list. Some of the input parameters are optional (i.e. they can
be NULL).

The <select> entry is shown below

	<select id="getList" resultMap="list-result">	
		SELECT 
			col1,
			col2,
			col3
		FROM 
			table
		WHERE
			col4 = #input1#
	    	<isNotNull prepend="AND" property="type">
	    		col5 = #input2#
	    	</isNotNull>
	    	<isNotNull prepend="AND" property="state">
	    		col6 = #input3#
	    	</isNotNull>
	    	ORDER BY col3
	</select>

In my DAO I am using the following method to execute the SQL:
		
		queryForList("getList", filter, start, range)

The filter object contains the input1, input2 and input3.

The above <select> works great. Now I want to the same thing using a
stored procedure. I tried a few options but that did not work.

I have a stored procedure with 3 IN parameters and 1 OUT parameter. The
OUT parameter is a REF CURSOR.

I tried calling the store and it did not work

<procedure id="getList" resultMap="list-result">
	{call my_stored_proc(#input1#, #input2#, #input3#)}
</procedure>

Please suggest.

Thanks,
Satish

Re: Executing stored procedures

[Clinton Begin <cl...@gmail.com>]

Two things:

1) What does "did not work" mean? Was there an exception etc?

2) When working with nullable parameters and Oracle, you must specify the 
data type (an Oracle/JDBC requirement). So use #input1:VARCHAR#, or whatever 
the type may be.

Cheers,
Clinton

On 8/2/05, Rao, Satish <sa...@fmr.com> wrote:
> 
> I have a query with 3 input parameters to the WHERE clause and the query 
> returns a list. Some of the input parameters are optional (i.e. they can 
> be NULL).
> 
> The <select> entry is shown below 
> 
> <select id="getList" resultMap="list-result"> 
> SELECT 
> col1, 
> col2, 
> col3 
> FROM 
> table 
> WHERE 
> col4 = #input1# 
>  <isNotNull prepend="AND" property="type"> 
>  col5 = #input2# 
>  </isNotNull> 
>  <isNotNull prepend="AND" property="state"> 
>  col6 = #input3# 
>  </isNotNull> 
>  ORDER BY col3 
> </select> 
> 
> In my DAO I am using the following method to execute the SQL: 
> 
> queryForList("getList", filter, start, range) 
> 
> The filter object contains the input1, input2 and input3. 
> 
> The above <select> works great. Now I want to the same thing using a 
> stored procedure. I tried a few options but that did not work.
> 
> I have a stored procedure with 3 IN parameters and 1 OUT parameter. The 
> OUT parameter is a REF CURSOR. 
> 
> I tried calling the store and it did not work 
> 
> <procedure id="getList" resultMap="list-result"> 
> {call my_stored_proc(#input1#, #input2#, #input3#)} 
> </procedure> 
> 
> Please suggest. 
> 
> Thanks, 
> Satish 
>