Re: Executing stored procedures

[Clinton Begin]

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 <[EMAIL PROTECTED]> 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