Calling Oracle stored procedures

gibiman · **Joined:** Mon Jul 30, 2007 5:03 am **Posts:** 8

Need help with Hibernate? Read this first:
http://www.hibernate.org/ForumMailingli ... AskForHelp

Hibernate version: 3.0

Mapping documents: Hibernate.cfg.xml , NamedQueries.hbm.xml
Name and version of the database you are using: Oracle 10g Express Edition

Read this: http://hibernate.org/42.html

Hello ,

I have been having quite some trouble now with some oracle stored procedures . I know there have been a lot talks about this subject but it seems none of them were helpful .

Here is my code :

Code:

      Session session = HibernateUtil.getCurrentSession();
      
      logger.debug("Running stored procedure REFRESH_KPIS()");
      
       Query exit_status_query = session.getNamedQuery("refresh_kpis_table");
       
       exit_status_query.setString("fromdate", fromdate);
       exit_status_query.setString("todate", todate);
       logger.debug("query :"+exit_status_query.toString());
       logger.debug("Getting list() ;");
       
       List exit_status = exit_status_query.list();

My NamedQueries.hbm.xml mapping file contains the following :

Code:

   </sql-query>
    <sql-query name="refresh_kpis_table" callable="true">
   { call REFRESH_KPIS( ? , :fromdate , :todate ) }
  </sql-query>

My stored procedure looks like this :

Code:

 

create or replace
PROCEDURE REFRESH_KPIS ( exit_status out sys_refcursor, from_date in varchar2, to_date in varchar2 ) 
AS
BEGIN
  DECLARE
  -- Declaration of variables ,...
  BEGIN
  open exit_status for
  select 1 from dual;
  --dbms_output.put_line('from_date '||from_date||' to_date: '||to_date);
  END;
END REFRESH_KPIS;

The exception is quite a general one :

Caused by: java.sql.SQLException: ORA-06550: line 1, column 13:
PLS-00306: wrong number or types of arguments in call to 'REFRESH_KPIS'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

If anyone has tried executing procedures or has any sugestion please help . I've been trying to get those to work for quite some time .

gibiman · **Joined:** Mon Jul 30, 2007 5:03 am **Posts:** 8

If anyone has problems using a procedure follow the above code with this slight modification in your named query :

Code:

    <sql-query name="refresh_kpis_table" callable="true">
    <return-scalar column="status" type="integer"></return-scalar>
   { call REFRESH_KPIS( ? , :fromdate , :todate) }
  </sql-query>

The thing is i did not set up a return type for the parameter returned by the procedure ( in my case an exit status ... integer ) .

minol01 · **Joined:** Thu Aug 30, 2007 2:10 pm **Posts:** 6

As a SP may have a select statement with joins to multiple tables, how would we do the mapping for the same?

gibiman · **Joined:** Mon Jul 30, 2007 5:03 am **Posts:** 8

just substitute the return types for the columns :

Code:

    <sql-query name="refresh_kpis_table" callable="true"> 
    <return-scalar column="status" type="integer"></return-scalar> 
    <!-- EXAMPLE 
    <return-scalar column="column_name1" type="string"/>
     <return-scalar column="column_name2" type="integer"/>
     .

   { call REFRESH_KPIS( ? , :fromdate , :todate) } 
  </sql-query>

or check this out :
http://forum.hibernate.org/viewtopic.ph ... =procedure[/code]

dharmendra.pandey · **Posted:** Mon Sep 10, 2007 2:17 am

Get the connection from session and use that connection as you were using earlier