Hi,
I am having trouble with an oracle stored procedure. For some reason whenever I try to add something as a parameter it doesn't get as far as the stored procedure.
For example I have the oracle function below, (l_cur is of type REF CUR):
Code:
FUNCTION search( v_name in varchar) RETURN search_cur_type is
l_cur search_cur_type;
l_query varchar(512) default 'SELECT * FROM dbtest_user WHERE 1 = 1';
BEGIN
l_query := 'select * from dbtest_user where user_id = :v_name';
OPEN l_cur FOR l_query
using v_name;
RETURN l_cur;
END search;
I have the following in my mapping file:
Code:
<sql-query name="search_sp" callable="true">
<return alias="item" class="com.fmr.dbtest.persistance.ItemVO">
<return-property name="userID" column="user_id"/>
<return-property name="name" column="user_name"/>
<return-property name="dob" column="dob"/>
<return-property name="address" column="address"/>
<return-property name="fruit" column="fav_fruit"/>
</return>
{ ? = call pack1.search(:name) }
</sql-query>
And I call it using the following:
Code:
Query query = session.getNamedQuery("search_sp");
query.setString("name", name);
return query.list();
When I check the size of the list it is 0. However when I change the function and hardcode in a value for name I get back results as I would expect to. Also I added an insert to see if a value for parameter was getting passed in. It wasn't.
Can any one tell me where the value for the parameter is getting lost.
Login
FAQ
Search
