I am trying to insert a record in oracle database , where i have written a stored procedure.
i am calling this procedure using hibernate. here is my code.

mapping file....
<sql-query name="insertCategory" callable="true">
    <return class="CategoryBO">
    <return-property name="categoryID" column="categoryid" />
    </return>
    { ? = call insertcategory(?, ?, ?, ?, ?, ?, ?) }
</sql-query>

java code.....
     Query query = session.getNamedQuery("test.CategoryBO.insertCateg ory");
        query.setInteger(0,78); // parent cat id
        query.setString(1, "cat name"); // cat name
        query.setString(2,"cat desc"); // cat desc

        query.setInteger(3, 1); // active
        query.setDate(4, new Date());
        query.setString(5, "admin");
        query.setDate(6, new Date());

List list = query.list();

since i am not aware how to invoke <sql-insert> , i have used <sql-query>.
the table contains following fields
CATEGORYID, PARENTCATEGORYID, CATEGORYNAME, CATEGORYDESCRIPTION, ACTIVE, LASTMODIFIED, MODIFIEDBY, CREATIONDATE

where categoryid is auto generated(hence i m not passing it)
my stored procedure has no problem

here is the exception
11:52:27,780 DEBUG JDBCExceptionReporter:63 - could not execute query [{ ? = call insertcategory(?, ?, ?, ?, ?, ?, ?) }]
java.sql.SQLException: ORA-06550: line 1, column 13:
PLS-00382: expression is of wrong type
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

    at oracle.jdbc.dbaccess.DBError.throwSqlException(DBE rror.java:134)
    at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:2 89)
    at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:573)
    at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol .java:1891)
    at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteFetch(TT C7Protocol.java:1093)
    at oracle.jdbc.driver.OracleStatement.executeNonQuery (OracleStatement.java:2047)
    at oracle.jdbc.driver.OracleStatement.doExecuteOther( OracleStatement.java:1940)
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTi meout(OracleStatement.java:2709)
    at oracle.jdbc.driver.OraclePreparedStatement.execute Update(OraclePreparedStatement.java:589)
    at oracle.jdbc.driver.OraclePreparedStatement.execute (OraclePreparedStatement.java:656)
    at org.hibernate.dialect.Oracle9Dialect.getResultSet( Oracle9Dialect.java:278)
    at org.hibernate.jdbc.AbstractBatcher.getResultSet(Ab stractBatcher.java:146)
    at org.hibernate.loader.Loader.getResultSet(Loader.ja va:1666)
    at org.hibernate.loader.Loader.doQuery(Loader.java:66 2)
    at org.hibernate.loader.Loader.doQueryAndInitializeNo nLazyCollections(Loader.java:224)
    at org.hibernate.loader.Loader.doList(Loader.java:214 5)
    at org.hibernate.loader.Loader.listIgnoreQueryCache(L oader.java:2029)
    at org.hibernate.loader.Loader.list(Loader.java:2024)
    at org.hibernate.loader.custom.CustomLoader.list(Cust omLoader.java:111)
    at org.hibernate.impl.SessionImpl.listCustomQuery(Ses sionImpl.java:1655)
    at org.hibernate.impl.AbstractSessionImpl.list(Abstra ctSessionImpl.java:142)
    at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl. java:164)
    at test.sample.main(sample.java:62)
11:52:27,780 WARN JDBCExceptionReporter:71 - SQL Error: 6550, SQLState: 65000
11:52:27,780 ERROR JDBCExceptionReporter:72 - ORA-06550: line 1, column 13:
PLS-00382: expression is of wrong type
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored


can anybody help me....?

HI,
I am also facing same problem. i am getting following error .
Stored Procedure Executed Started
Hibernate: {call selectPlayerMaster_SP2(?)}
org.hibernate.exception.GenericJDBCException: could not execute query
        at org.hibernate.exception.ErrorCodeConverter.handled NonSpecificExceptio
n(ErrorCodeConverter.java:92)
        at org.hibernate.exception.ErrorCodeConverter.convert (ErrorCodeConverter
.java:80)
        at org.hibernate.exception.JDBCExceptionHelper.conver t(JDBCExceptionHelp
er.java:43)
        at org.hibernate.loader.Loader.doList(Loader.java:159 6)
        at org.hibernate.loader.Loader.list(Loader.java:1577)
        at org.hibernate.loader.custom.CustomLoader.list(Cust omLoader.java:112)
        at org.hibernate.impl.SessionImpl.listCustomQuery(Ses sionImpl.java:1414)

        at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl. java:153)
        at hiberservlet.doPost(hiberservlet.java:157)
        at javax.servlet.http.HttpServlet.service(HttpServlet .java:760)
        at javax.servlet.http.HttpServlet.service(HttpServlet .java:853)
        at org.apache.catalina.core.ApplicationFilterChain.in ternalDoFilter(Appl
icationFilterChain.java:247)
        at org.apache.catalina.core.ApplicationFilterChain.do Filter(ApplicationF
ilterChain.java:193)
        at org.apache.catalina.core.StandardWrapperValve.invo ke(StandardWrapperV
alve.java:256)
        at org.apache.catalina.core.StandardPipeline$Standard PipelineValveContex
t.invokeNext(StandardPipeline.java:643)
        at org.apache.catalina.core.StandardPipeline.invoke(S tandardPipeline.jav
a:480)
        at org.apache.catalina.core.ContainerBase.invoke(Cont ainerBase.java:995)

        at org.apache.catalina.core.StandardContextValve.invo ke(StandardContextV
alve.java:191)
        at org.apache.catalina.core.StandardPipeline$Standard PipelineValveContex
t.invokeNext(StandardPipeline.java:643)
        at org.apache.catalina.core.StandardPipeline.invoke(S tandardPipeline.jav
a:480)
        at org.apache.catalina.core.ContainerBase.invoke(Cont ainerBase.java:995)

        at org.apache.catalina.core.StandardContext.invoke(St andardContext.java:
2422)
        at org.apache.catalina.core.StandardHostValve.invoke( StandardHostValve.j
ava:180)
        at org.apache.catalina.core.StandardPipeline$Standard PipelineValveContex
t.invokeNext(StandardPipeline.java:643)
        at org.apache.catalina.valves.ErrorDispatcherValve.in voke(ErrorDispatche
rValve.java:171)
        at org.apache.catalina.core.StandardPipeline$Standard PipelineValveContex
t.invokeNext(StandardPipeline.java:641)
        at org.apache.catalina.valves.ErrorReportValve.invoke (ErrorReportValve.j
ava:163)
        at org.apache.catalina.core.StandardPipeline$Standard PipelineValveContex
t.invokeNext(StandardPipeline.java:641)
        at org.apache.catalina.core.StandardPipeline.invoke(S tandardPipeline.jav
a:480)
        at org.apache.catalina.core.ContainerBase.invoke(Cont ainerBase.java:995)

        at org.apache.catalina.core.StandardEngineValve.invok e(StandardEngineVal
ve.java:174)
        at org.apache.catalina.core.StandardPipeline$Standard PipelineValveContex
t.invokeNext(StandardPipeline.java:643)
        at org.apache.catalina.core.StandardPipeline.invoke(S tandardPipeline.jav
a:480)
        at org.apache.catalina.core.ContainerBase.invoke(Cont ainerBase.java:995)

        at org.apache.coyote.tomcat4.CoyoteAdapter.service(Co yoteAdapter.java:19
9)
        at org.apache.coyote.http11.Http11Processor.process(H ttp11Processor.java
:828)
        at org.apache.coyote.http11.Http11Protocol$Http11Conn ectionHandler.proce
ssConnection(Http11Protocol.java:700)
        at org.apache.tomcat.util.net.TcpWorkerThread.runIt(P oolTcpEndpoint.java
:584)
        at org.apache.tomcat.util.threads.ThreadPool$ControlR unnable.run(ThreadP
ool.java:683)
        at java.lang.Thread.run(Thread.java:536)
Caused by: java.sql.SQLException: ORA-06550: line 1, column 7:
PLS-00905: object SCOTT.SELECTPLAYERMASTER_SP2 is invalid
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

        at oracle.jdbc.dbaccess.DBError.throwSqlException(DBE rror.java:134)
        at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:2 89)
        at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:582)
        at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol .java:1983)
        at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteFetch(TT C7Protocol.java:114
1)
        at oracle.jdbc.driver.OracleStatement.executeNonQuery (OracleStatement.ja
va:2149)
        at oracle.jdbc.driver.OracleStatement.doExecuteOther( OracleStatement.jav
a:2032)
        at oracle.jdbc.driver.OracleStatement.doExecuteWithTi meout(OracleStateme
nt.java:2894)
        at oracle.jdbc.driver.OraclePreparedStatement.execute Update(OraclePrepar
edStatement.java:608)
        at oracle.jdbc.driver.OraclePreparedStatement.execute (OraclePreparedStat
ement.java:684)
        at org.hibernate.dialect.Oracle9Dialect.getResultSet( Oracle9Dialect.java
:309)
        at org.hibernate.jdbc.AbstractBatcher.getResultSet(Ab stractBatcher.java:
127)
        at org.hibernate.loader.Loader.getResultSet(Loader.ja va:1269)
        at org.hibernate.loader.Loader.doQuery(Loader.java:39 1)
        at org.hibernate.loader.Loader.doQueryAndInitializeNo nLazyCollections(Lo
ader.java:218)
        at org.hibernate.loader.Loader.doList(Loader.java:159 3)
        ... 36 more
Can some one help to resolve me out .

can you tell me , how did u invoke your stored procedure, what are the fields in you table? (make sure that you are not inserting null values in non-nullable fields.

Hi All,

   Can AnyOne tell me how to make pojo, hbm and main method for a stored procedure. If AnyOne have its complete example please send it to me at my mailing id [email protected]

Hi,

what i understood is the paramters are not matching you've passed 7 paramters but the query expects 8 you might be forgetting the first one which is before "=" operator. try changing it.

Regards,
Rakesh

Hi,

i think the sql syntax is not correct if see the query "SELECTPLAYERMASTER_SP2" there is no space between SELECT AND table name.

Regards,
Rakesh