Guys,
Can anyone help me to solve the problem i am getting?

I am trying to call a stored procedure from Hibernate.

Stored Proc:
---------------

CREATE OR REPLACE PROCEDURE test_APS_payment_report

(o_cur_test_payment_rpt OUT sys_refcursor, i_dt_from_date IN DATE, i_dt_to_date IN DATE) IS
.
.
.


hbm.xml file:
---------------

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
<sql-query name="getAPSForPaymentMode" callable="true">
{ ? = call test_APS_payment_report(?, ?)}
</sql-query>
</hibernate-mapping>

Java Code:
-------------
Session session = currentSession();
query = session.getNamedQuery("getAPSForPaymentMode");
query.setString(0, "12/12/2007");
query.setString(1, "13/12/2007");
List apsList = query.list();

While executing the procedure through query.list(), following exception is thrown.

org.hibernate.exception.SQLGrammarException: could not execute query

Try or


http://www.hibernate.org/hib_docs/v3/ap ... .util.Date)

Here you are setting the date as String type. But it should be of Date or Calendar type.

Hi sjhyam,
I used setDate(int position, Date date) method in following 2 ways. It gives same exception again.

1.
query = session.getNamedQuery("getAPSForPaymentMode");
query.setDate(0, java.sql.Date.valueOf("2007-12-12"));
query.setDate(1, java.sql.Date.valueOf("2007-12-13"));

2.
SimpleDateFormat sdf = new SimpleDateFormat ("dd/MM/yyyy");
java.util.Date fromDate = sdf.parse("12/12/2007");
java.util.Date toDate = sdf.parse("13/12/2007");
query.setDate(0, fromDate);
query.setDate(1, toDate);

org.hibernate.exception.SQLGrammarException: could not execute query

Query.setDate() expects java.util.Date. And you have given java.sql.Date.

Still if you get errors, post the complete exception trace. I am looking for SQL Codes.

I used both util and sql dates. Same exception is coming

Procedure:
------------


CREATE OR REPLACE PROCEDURE test_APS_payment_report
(
o_cur_test_payment_rpt OUT sys_refcursor,
i_dt_from_date IN DATE,
i_dt_to_date IN DATE
) IS


l_cursor VARCHAR2 (4000);
l_str_where VARCHAR2 (2500);
l_str_select VARCHAR2 (2500);
l_str_from VARCHAR2 (500);
l_dt_from_date DATE;
l_dt_to_date DATE;

BEGIN

l_dt_from_date := i_dt_from_date;
l_dt_to_date := i_dt_to_date;

----------------------Select Statements----------------------------------
l_str_select := ' SELECT pmt_reference_id AS transactionid,';
l_str_select := l_str_select ||' Source_system_id as SourceSystemID,';
l_str_select := l_str_select ||' depacctfrom_account_id AS debitaccount,';
l_str_select := l_str_select ||' depacctfrom_branch_id AS OrderingInstitutionBIC,';
l_str_select := l_str_select ||' depacctidto_account_id AS creditaccount,';
l_str_select := l_str_select ||' payee_branch_id AS AccountInstitutionBIC,';
l_str_select := l_str_select ||' pmtaddrq_currency_cd AS currency,prcdate_tm AS valuedate,';
l_str_select := l_str_select ||' pmtstatus_effective_date_tm AS executiondate,';
l_str_select := l_str_select ||' pmtstatus_payment_status_cd AS PostingStatus';

----------------------From Statement----------------------------------
l_str_from := ' FROM ctsi_req_res_table';

----------------------Where Statements----------------------------------

/* If both the dates are NOT NULL then this is executed */
l_str_where := ' WHERE (TO_DATE(invoice_effective_date_tm,'||chr(39);
l_str_where := l_str_where || 'DD/MM/YYYY'||chr(39)||')';
l_str_where := l_str_where || ' BETWEEN TO_DATE('||chr(39)||l_dt_from_date||chr(39);
l_str_where := l_str_where || ',' ||chr(39)||'DD/MM/YYYY'||chr(39)||')';
l_str_where := l_str_where || ' AND TO_DATE('||chr(39)||l_dt_to_date||chr(39);
l_str_where := l_str_where || ',' ||chr(39)||'DD/MM/YYYY'||chr(39)||'))';

----------------Cursor Statement----------------------
l_cursor := l_str_select ||l_str_from||l_str_where;

p_clu_log(51,'test_APS_payment_report',l_cursor);

OPEN o_cur_test_payment_rpt FOR l_cursor;

-----------------When Exception Occurs------------------------
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(SQLERRM);

/* END of PROCEDURE */
END test_APS_payment_report;
/




Exception:
-----------[2/21/08 20:37:39:342 IST] 00000025 SystemErr R org.hibernate.exception.SQLGrammarException: could not execute query
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.loader.Loader.doList(Loader.java:2223)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2104)
at org.hibernate.loader.Loader.list(Loader.java:2099)
at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:289)
at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1695)
at org.hibernate.impl.AbstractSessionImpl.list(AbstractSessionImpl.java:142)
at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:152)
at com.citi.aps.db.daoimpl.APSReportDAOImpl.getAPSReport(APSReportDAOImpl.java:93)
at com.citi.aps.pojo.APSReportPOJO.getAPSReport(APSReportPOJO.java:42)
at com.citi.aps.pojo.APSReportPOJOService.getAPSReport(APSReportPOJOService.java:43)
at com.citi.aps.ejbs.APSReportSessionBean.getAPSReportForPayments(APSReportSessionBean.java:195)
at com.citi.aps.ejbs.EJSRemoteStatelessAPSReportSession_228c153f.getAPSReportForPayments(EJSRemoteStatelessAPSReportSession_228c153f.java:128)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:64)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:615)
at com.ibm.rmi.util.ProxyUtil$4.run(ProxyUtil.java:727)
at java.security.AccessController.doPrivileged(AccessController.java:241)
at com.ibm.rmi.util.ProxyUtil.invokeWithClassLoaders(ProxyUtil.java:725)
at com.ibm.CORBA.iiop.ClientDelegate.invoke(ClientDelegate.java:1123)
at $Proxy2.getAPSReportForPayments(Unknown Source)
at com.citi.aps.ejbs._APSReportSession_Stub.getAPSReportForPayments(_APSReportSession_Stub.java:386)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:64)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:615)
at com.ibm.etools.utc.model.ReflectionMethodModel.invoke(ReflectionMethodModel.java:65)
at com.ibm.etools.utc.servlet.InvokeServlet.invoke(InvokeServlet.java:113)
at com.ibm.etools.utc.servlet.InvokeServlet.doPost(InvokeServlet.java:369)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:763)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:856)
at com.ibm.ws.webcontainer.servlet.ServletWrapper.service(ServletWrapper.java:966)
at com.ibm.ws.webcontainer.servlet.ServletWrapper.handleRequest(ServletWrapper.java:478)
at com.ibm.ws.wswebcontainer.servlet.ServletWrapper.handleRequest(ServletWrapper.java:463)
at com.ibm.ws.webcontainer.servlet.CacheServletWrapper.handleRequest(CacheServletWrapper.java:92)
at com.ibm.ws.webcontainer.WebContainer.handleRequest(WebContainer.java:744)
at com.ibm.ws.wswebcontainer.WebContainer.handleRequest(WebContainer.java:1433)
at com.ibm.ws.webcontainer.channel.WCChannelLink.ready(WCChannelLink.java:93)
at com.ibm.ws.http.channel.inbound.impl.HttpInboundLink.handleDiscrimination(HttpInboundLink.java:465)
at com.ibm.ws.http.channel.inbound.impl.HttpInboundLink.handleNewInformation(HttpInboundLink.java:394)
at com.ibm.ws.http.channel.inbound.impl.HttpICLReadCallback.complete(HttpICLReadCallback.java:102)
at com.ibm.ws.tcp.channel.impl.AioReadCompletionListener.futureCompleted(AioReadCompletionListener.java:152)
at com.ibm.io.async.AbstractAsyncFuture.invokeCallback(AbstractAsyncFuture.java:213)
at com.ibm.io.async.AbstractAsyncFuture.fireCompletionActions(AbstractAsyncFuture.java:195)
at com.ibm.io.async.AsyncFuture.completed(AsyncFuture.java:136)
at com.ibm.io.async.ResultHandler.complete(ResultHandler.java:194)
at com.ibm.io.async.ResultHandler.runEventProcessingLoop(ResultHandler.java:741)
at com.ibm.io.async.ResultHandler$2.run(ResultHandler.java:863)
at com.ibm.ws.util.ThreadPool$Worker.run(ThreadPool.java:1510)
Caused by: java.sql.SQLException: ORA-06550: line 1, column 13:
PLS-00306: wrong number or types of arguments in call to 'TEST_APS_PAYMENT_REPORT'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

I am counting this exception message:



As your stored proc has 3 parameters, can you change the index of the parametes in query.setXXX() as

It gives different exception

java.lang.IllegalArgumentException: Positional parameter does not exist: 2 in query: { ? = call test_APS_payment_report(?, ?)}