Hibernate ans oracle stored procedures -

i have oracle stored procedure :`

---

    create or replace procedure getclientfromauthentification(mycursorresult out sys_refcursor, login in varchar2, pwd in varchar2) num_client number(6);  compte_epargne varchar2(50);  compte_courant varchar2(50);  nom varchar2(50);  prenom varchar2(50);  adresse varchar2(100);  begin    open mycursorresult      select client.num_client, client.num_compte_epargne, client.num_compte_courant,  client.nom_client, client.prenom_client, client.adresse_client      client       client.login_client = login , client.mdp_client = pwd;    fetch mycursorresult num_client, compte_epargne, compte_courant, nom, prenom, adresse;    if mycursorresult%rowcount > 1 raise too_many_rows;    else if mycursorresult%rowcount = 0 raise no_data_found;    end if;    end if;    exception      when too_many_rows raise_application_error(-20000, 'base corrompue');      when no_data_found raise_application_error(-20001, 'pas de résultats');  end getclientfromauthentification;` 

---

i tested in oracle sqldevelopper , works fine :

---

`set serveroutput on;  declare  mycursor sys_refcursor;  begin    dbms_output.enable;    getclientfromauthentification(mycursor, 'durant.jean', 'durant.jean1234');    dbms_output.put_line('cursor = ' || mycursor%rowcount);  end;` 

---

cursor = 1 (it found :=))

now try use procedure on java side hybernate.

the mapping :

---

`<hibernate-mapping>    <class name="model.entity.client">      <id name="num_client" type="int" />      <property name="num_compte_epargne" type="string" />      <property name="num_compte_courant" type="string" />      <property name="nom" type="string" />      <property name="prenom" type="string" />      <property name="adresse" type="string" />    </class>    <sql-query name="getclientfromauthentification_sp" callable="true">      <return alias="client" class="model.entity.client" >          <return-property name="num_client" column="num_client"/>          <return-property name="num_compte_epargne" column="num_compte_epargne"/>          <return-property name="num_compte_courant" column="num_compte_client"/>          <return-property name="nom" column="nom_client"/>          <return-property name="prenom" column="prenom_client"/>          <return-property name="adresse" column="adresse_client"/>      </return>      { call getclientfromauthentification(?, :login, :mdp) }    </sql-query>  </hibernate-mapping>` 

---

when use :

---

`

arraylist<client> clients;   query q = session.getnamedquery("getclientfromauthentification_sp");   q.setstring("login", "durant.jean");   q.setstring("mdp", "durant.jean1234");    clients = (arraylist<client>) q.list();` 

i have no errors unfortunatly list empty...

help me please

in stored procedure, use %notfound instead of %rowcount:

• you should use %rowcount implicit cursors insert/update/delete.
• you should use %notfound selects see if return data or not. e.g.

edit: whole proc added answer

create or replace procedure getclientfromauthentification(mycursorresult out sys_refcursor, login in varchar2, pwd in varchar2) num_client number(6); compte_epargne varchar2(50); compte_courant varchar2(50); nom varchar2(50); prenom varchar2(50); adresse varchar2(100); begin   open mycursorresult     select client.num_client, client.num_compte_epargne, client.num_compte_courant,  client.nom_client, client.prenom_client, client.adresse_client     client      client.login_client = login , client.mdp_client = pwd;   fetch mycursorresult num_client, compte_epargne, compte_courant, nom, prenom, adresse;   if mycursorresult%notfound      raise no_data_found;   end if; exception     when no_data_found raise_application_error(-20001, 'pas de résultats'); end getclientfromauthentification; 

regarding whole design:

• why return wrapped in cursor having 1 row?

• why not have these out parameter:

  num_client, compte_epargne, compte_courant, nom, prenom, adresse