How to get the result of stored procedure ?

jim060 · **Joined:** Fri Sep 29, 2006 3:57 am **Posts:** 5

Hi,

I try to get a result of my stored procedure.
The stored procedure return a cursor.

In my hbm file, I map the stored procedure :

<sql-query name="CalculBdc" callable="true" >
<return class="CalculBdc" alias="calculBdc">
<return-property name="idRecherche" column="ID_RECHERCHE"/>
</return>
{ ? = call pkg_calcul_bdc.CalculBdc(?,?,?,?,?,?,?,?) }
</sql-query>

So, I manage to call my procedure, but I don't manage to get a procedure result (cursor returned).

Have you any idea ?

thanks

For more details :

I use Hibernate version: 3.0 and Oracle : 9i

I call my procedure with this code :

Code:

String nameStoreProcedure = "CalculBdc";

this.getHibernateTemplate().flush();
Object obj = thiis.getHibernateTemplate().findByNamedQuery(nameStoreProcedure, param);

So I trace my parameter :

CALL SP : CalculBdc
PARAMETRE_0 SP: [39]
PARAMETRE_1 SP: [Fri Sep 29 07:05:34 CEST 2006]
PARAMETRE_2 SP: [Thu Jan 01 00:00:00 CET 1970]
PARAMETRE_3 SP: [-1000]
PARAMETRE_4 SP: [-1000]
PARAMETRE_5 SP: [-1000]
PARAMETRE_6 SP: [-1000]
PARAMETRE_7 SP: [-1000]

In my hibernate file :

Code:

 <class name="CalculBdc" table="REF_CALCUL_BDC">
        <composite-id>
            <key-property name="idRecherche" column="ID_RECHERCHE" type="java.lang.Integer"/>
            <key-property name="dateActivite" column="DATE_ACTIVITE" type="java.util.Date" />
            <key-property name="idAffectation" column="ID_AFFECTATION" type="java.lang.Integer" />
        </composite-id>
        
        <property name="idTourneeCollecte" column="ID_TOURNEE_DEFINIE" type="java.lang.Integer"  not-null="true" access="field" />
        <property name="codeTournee" column="CODE_TOURNEE" type="java.lang.String"  not-null="false" access="field" />
          <property name="idSite" column="ID_SITE" type="int"  not-null="false" access="field" />
        <property name="operateur" column="OPERATEUR" type="java.lang.String"  not-null="false" access="field" />
        <property name="idSousTraitant" column="ID_ELISA" type="java.lang.Integer"  not-null="false" access="field" />
        <property name="chauffeur" column="CHAUFFEUR" type="java.lang.String"  not-null="false" access="field" />
        <property name="localisation" column="LOCALISATION" type="java.lang.String"  not-null="false" access="field" />
        <property name="qualification" column="QUALIFICATION" type="java.lang.String"  not-null="false" access="field" />
        <property name="secteur" column="SECTEUR" type="java.lang.String"  not-null="false" access="field" />
        <property name="imprime" column="IMPRIME" type="java.lang.String"  not-null="false" access="field" />
          <property name="dateImpression" column="DATE_IMPRESSION" type="java.util.Date"  not-null="false" access="field" />
        <property name="idPointCollecte" column="ID_POINT_COLLECTE" type="java.lang.Integer"  not-null="false" access="field" />
        <property name="idEnlevement" column="ID_ENLEVEMENT" type="java.lang.Integer"  not-null="false" access="field" />
        <property name="clientCollecte" column="CLIENT_COLLECTE" type="java.lang.String"  not-null="false" access="field" />
          <property name="heureContrat" column="HEURE_CONTRAT" type="java.util.Date"  not-null="false" access="field" />
        <property name="codeTypePoint" column="CODE_TYPE_POINT" type="java.lang.String"  not-null="false" access="field" />
        <property name="lundi" column="LUNDI" type="java.lang.String"  not-null="false" access="field" />
        <property name="mardi" column="MARDI" type="java.lang.String"  not-null="false" access="field" />
        <property name="mercredi" column="MERCREDI" type="java.lang.String"  not-null="false" access="field" />
        <property name="jeudi" column="JEUDI" type="java.lang.String"  not-null="false" access="field" />
        <property name="vendredi" column="VENDREDI" type="java.lang.String"  not-null="false" access="field" />
        <property name="samedi" column="SAMEDI" type="java.lang.String"  not-null="false" access="field" />
          <property name="dateDebutAffectation" column="DATE_DEBUT_AFFECTATION" type="java.util.Date"  not-null="false" access="field" />
          <property name="dateFinAffectation" column="DATE_FIN_AFFECTATION" type="java.util.Date"  not-null="false" access="field" />
    </class>    
    <sql-query name="CalculBdc" callable="true" >       
   <return class="CalculBdc" alias="calculBdc">
       <return-property name="idRecherche" column="ID_RECHERCHE"/>
   </return>
   { ? = call pkg_calcul_bdc.CalculBdc(?,?,?,?,?,?,?,?) }
</sql-query>

My stored procedure :

Code:

  FUNCTION CalculBdc (
       an_id_site     IN     ref_calcul_bdc.id_site%type,
     ad_debut_activite  IN  ref_calcul_bdc.date_activite%type,
     ad_fin_activite   IN  ref_calcul_bdc.date_activite%type,
     as_code_tournee       IN     ref_calcul_bdc.code_tournee%type,
     an_id_secteur      IN     ref_tournee.id_secteur%type,
     as_operateur    IN     ref_calcul_bdc.operateur%type,
     an_id_moyen_propre  IN  ref_tournee.id_moyen_propre%type,
     an_id_elisa    IN  ref_tournee.id_elisa%type
  )
  RETURN SYS_REFCURSOR
  IS
      -- Déclaration des variables
   vn_id_recherche  ref_calcul_bdc.id_recherche%type;
   vd_fin_activite  ref_calcul_bdc.date_activite%type;
   vd_date_activite  ref_calcul_bdc.date_activite%type;
   
   cur_id_recherche  sys_refcursor ;
   
  BEGIN
      -- Purge des données dont la date d'activité est inférieure à la date du jour
   DELETE FROM ref_calcul_bdc
   WHERE     date_activite < trunc (sysdate, 'dd');
   
   COMMIT;
   
      OPEN cur_id_recherche FOR
   SELECT s_ref_calcul_bdc.nextval
   FROM DUAL ;

   FETCH cur_id_recherche
   INTO vn_id_recherche;
   
   -- Calcul de la boucle
   IF to_char (ad_fin_activite, 'dd/mm/yyyy') = '01/01/1970'
   THEN
       -- Pas de période ==> Calcul uniquement sur la date du jour
    vd_fin_activite := ad_debut_activite;
   ELSE
       vd_fin_activite := ad_fin_activite;
   END IF;

   vd_date_activite := ad_debut_activite;
   
   WHILE vd_date_activite <= vd_fin_activite
   LOOP
       
    -- Remplissage de la table de calcul pour la journée
    INSERT INTO  ref_calcul_bdc
          (id_recherche, date_activite, id_affectation,
        id_tournee_definie, code_tournee, id_site, operateur,
        id_elisa, chauffeur, localisation, qualification,
        secteur, imprime, date_impression, id_point_collecte,
        id_enlevement, client_collecte, heure_contrat,
        code_type_point, lundi, mardi, mercredi, jeudi, vendredi,
        samedi, date_debut_affectation, date_fin_affectation)
    SELECT vn_id_recherche, vd_date_activite, a.id_affectation,
       t.id_tournee_definie, t.code_tournee, t.id_site, t.operateur,
     t.id_elisa, m.nom || ' ' || m.prenom, t.localisation, t.qualification,
     s.libelle, nvl2(i.date_impression, 'Imprimé', 'A imprimer'),
     i.date_impression, p.id_point_collecte, e.id_enlevement,
     p.client_collecte, e.heure_contrat, p.code_type_point,
     decode (a.passage_lundi, 1, e.code_type_lundi, null), 
     decode (a.passage_mardi, 1, e.code_type_mardi, null), 
           decode (a.passage_mercredi, 1, e.code_type_mercredi, null), 
           decode (a.passage_jeudi, 1, e.code_type_jeudi, null), 
           decode (a.passage_vendredi, 1, e.code_type_vendredi, null), 
           decode (a.passage_samedi, 1, e.code_type_samedi, null),
     a.date_affectation, a.date_fin_affectation
    FROM  ref_tournee t,
      ref_affectation a,
      ref_enlevement e,
     ref_point_collecte p,
      ref_impression_bdc i,
     ref_moyen_propre m,
     ref_secteur s,
      (SELECT f.id_point_collecte,
         count(f.id_fermeture)
       FROM  ref_periode_fermeture f
       WHERE ((f.date_fin_fermeture is null
          AND f.date_debut_fermeture = vd_date_activite) 
        OR (f.date_fin_fermeture is not null
         AND f.date_debut_fermeture <= vd_date_activite
        AND f.date_fin_fermeture >= vd_date_activite)
      )
       GROUP BY f.id_point_collecte) fermeture
    WHERE t.type_tournee = 'COLLECTE'
    AND t.id_site = an_id_site
    AND (t.id_secteur = an_id_secteur or an_id_secteur = -1000)
    AND (t.id_moyen_propre = an_id_moyen_propre or an_id_moyen_propre = -1000)
    AND (t.id_elisa = an_id_elisa or an_id_elisa = -1000)
    AND (t.code_tournee = as_code_tournee or as_code_tournee = '-1000')
    AND (t.operateur = as_operateur or as_operateur = '-1000')
    AND a.id_tournee_definie = t.id_tournee_definie
    AND e.id_enlevement = a.id_enlevement
    AND i.id_tournee_definie (+) = t.id_tournee_definie
    AND i.date_activite (+) = vd_date_activite
    AND m.id_moyen_propre (+) = t.id_moyen_propre
    AND s.id_secteur (+) = t.id_secteur
    AND p.id_point_collecte = e.id_point_collecte
    AND fermeture.id_point_collecte (+) = p.id_point_collecte
    AND fermeture.id_point_collecte is null
    AND ((to_char (vd_date_activite, 'd') = '1' and a.passage_lundi = 1)
    or (to_char (vd_date_activite, 'd') = '2' and a.passage_mardi = 1)
    or (to_char (vd_date_activite, 'd') = '3' and a.passage_mercredi = 1)
    or (to_char (vd_date_activite, 'd') = '4' and a.passage_jeudi = 1)
    or (to_char (vd_date_activite, 'd') = '5' and a.passage_vendredi = 1)
    or (to_char (vd_date_activite, 'd') = '6' and a.passage_samedi = 1)
    )
    AND a.date_affectation <= vd_date_activite
    AND (a.date_fin_affectation >= vd_date_activite
       or a.date_fin_affectation is null);
    
    vd_date_activite := vd_date_activite + 1;
   END LOOP;
   
   COMMIT;
   
   -- Renvoi de l'identifiant de la recherche
   RETURN cur_id_recherche ;
  EXCEPTION
      WHEN OTHERS
      THEN
         DBMS_OUTPUT.PUT_LINE ('L''erreur suivante est survenue lors du calcul de la table temporaire ');
   DBMS_OUTPUT.put_line(substr(SQLERRM, 1, 255));
   DBMS_OUTPUT.put_line(substr(SQLERRM, 256, 255));

   -- Ouverture du fichier log
   m_FileTrace:= utl_file.fopen('DIR_LOG', m_FichierTrace,'a');
     utl_file.put_line (m_FileTrace, TO_CHAR(SYSDATE,'DD/MM/YYYY HH24:MI:SS') ||
            ' CalculBdc : Exception Code erreur : ' || TO_CHAR (sqlcode) || ', ' || sqlerrm);
   utl_file.Fclose(m_FileTrace);
   
   ROLLBACK;

   RAISE ;
  END;

max · **Posted:** Fri Sep 29, 2006 5:22 am

you never get a cursor back, you should get a list with rows of the entity CalculBdc.

What do you actually get into the obj ?

jim060 · **Joined:** Fri Sep 29, 2006 3:57 am **Posts:** 5

Actually I get a null List into obj...

max · **Posted:** Fri Sep 29, 2006 6:27 am

great....that *cannot* happen in the hibernate API.

Either you get a List or an Exception is thrown.

Could you please try this without using the Spring HibernateTemplate wrapper object ? Or step throguh with a debugger to tell me what is actually going on ?

/max

jim060 · **Joined:** Fri Sep 29, 2006 3:57 am **Posts:** 5

Yes, when I use the debugger, I get a List obj without element (size = 0).

When I try to call my stored procedure without using the Spring HibernateTemplate wrapper object, this exception is thrown :

Code:

java.sql.SQLException: ORA-06550: line 1, column 7:
PLS-00221: 'CALCULBDC' is not a procedure or is undefined
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

My code to call the stored procedure :

Code:

String sql = "{ call pkg_calcul_bdc.CalculBdc(?,?,?,?,?,?,?,?) }";

CallableStatement call = getSession().connection().prepareCall(sql);

call.setInt(1,39);
call.setString(2,"03/03/2006");
Calendar cal = Calendar.getInstance();
cal.set(1970,0,1,0,0,0);
call.setString(3,"01/01/1970");
call.setString(4,"-1000");
call.setInt(5,-1000);
call.setString(6,"-1000");
call.setInt(7,-1000);
call.setInt(8,-1000);
      
call.execute();

max · **Posted:** Fri Sep 29, 2006 8:22 am

Ok, i'm confused now.

Are you saying that you are getting a null returned from the spring hibernate template even though Hibernate is returning a List obj without elements or are you saying that an exception is thrown and swallowed ?

So with respect to spring hibernate template they are either:

1) swallowing an exception and returning null (freaking bad!)

2) returning null when the list is empty (I guess ok if they want that, but weird!)

Plus the code you show in the last ocmment is jdbc code and I assume this is not how you are calling it when you say "not using hibernate template", right ?! What I want you to test is when using session directly.

And if that result in a function not found, well then it is a db/jdbc driver level problem

/max

max · **Posted:** Fri Sep 29, 2006 8:29 am

and I don't get how you can get a SQLException since that is an checked exception so you should at least get an HibernateException with the SQLException as its cause....

jim060 · **Joined:** Fri Sep 29, 2006 3:57 am **Posts:** 5

When I call the stored procedure :

Code:

Object obj = this.getHibernateTemplate().findByNamedQuery(nameStoreProcedure, param);

I get into obj an empty List.

So, when you say :

Quote:

What I want you to test is when using session directly.

How can I use session directly ?

thanks

max · **Posted:** Fri Sep 29, 2006 9:12 am

jim060 wrote:

When I call the stored procedure :

Code:

Object obj = this.getHibernateTemplate().findByNamedQuery(nameStoreProcedure, param);

I get into obj an empty List.

so null is not returned as you initially said ?

Quote:

So, when you say :

Quote:

What I want you to test is when using session directly.

How can I use session directly ?

Well, just don't use Spring Hibernate Template at get the session from your sessionfactory.

But now how did you the exception with "procedure/fucntion not found" ?

/max

jim060 · **Joined:** Fri Sep 29, 2006 3:57 am **Posts:** 5

No, sorry, is return an empty List.

I saw the SQLException with a debugger.

This SQLException is due to I wrote :

Code:

String sql = "{ call pkg_calcul_bdc.CalculBdc(?,?,?,?,?,?,?,?) }";

But I have to write (to a function) :

Code:

String sql = "{ ? = call pkg_calcul_bdc.CalculBdc(?,?,?,?,?,?,?,?) }";

But, I don't know how I must define the first parameter (retrun parameter).

CallableStatement call = getSessionFactory().openSession().connection().prepareCall(sql);

call.registerOutParameter(1,Types.INTEGER);
call.setInt(2,39);
call.setString(3,"03/03/2006");
Calendar cal = Calendar.getInstance();
cal.set(1970,0,1,0,0,0);
call.setString(4,"01/01/1970");
call.setString(5,"-1000");
call.setInt(6,-1000);
call.setString(7,"-1000");
call.setInt(8,-1000);
call.setInt(9,-1000);

call.execute();

max · **Posted:** Fri Sep 29, 2006 9:58 am

well so when you call via hibernate the result is actually being called - but I guess nothing is really returned.

if you want to test in raw jdbc then see what the OracleDialect does to get the resultset.