Oracle stored procedure and parameters

PaddyDwyer · **Joined:** Tue May 24, 2005 11:04 am **Posts:** 5

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.

PaddyDwyer · **Joined:** Tue May 24, 2005 11:04 am **Posts:** 5

Just to clarify I'm using Oracle 9i database, with Hibernate 3.0.2

Paddy

max · **Posted:** Tue May 24, 2005 3:02 pm

stacktrace?

max · **Posted:** Tue May 24, 2005 3:11 pm

oh sorry it actually does execute, right ?

Then the paremeter MUST have been set otherwise oracle would have complained.

PaddyDwyer · **Joined:** Tue May 24, 2005 11:04 am **Posts:** 5

It definitely executes. I do believe the parameter is being set but oracle just sees it as null. I checked this by inserting the passed variables into a table and they all just come up as null.

PaddyDwyer · **Joined:** Tue May 24, 2005 11:04 am **Posts:** 5

The value of the parameter is definitely being passed into the the stored procedure. I've checked the callable statement and the value for the parameter is definitely passed in. However the value of the parameter is not getting picked up by the stored procedure.

max · **Posted:** Wed May 25, 2005 5:41 pm

so a driver bug

PaddyDwyer · **Joined:** Tue May 24, 2005 11:04 am **Posts:** 5

It works now. I'm not sure what was going on. Thanks for your help anyway.