Oracle update only stored procedure with NHibernate via named query

Question

I have an Oracle stored procedure that does only an update. It is a legacy stored proc and I must use it. It cannot be changed.

I can call and execute it from C# with Nhibernate fine as follows:

var query = Session.CreateSQLQuery("call myPackage.mySproc(:param1, :param2, :param3)");
query.SetInt64("param1", 123456);
query.SetInt64("param2", 654321);
query.ExecuteUpdate();

I would prefer to use a named query if possible. I've tried the following:

Session
.GetNamedQuery("myNamedQuery")
.SetInt64("param1", 123456)
.SetInt64("param2", 654321)
.ExecuteUpdate();

where my named query looks like this:

<sql-query name="myNamedQuery">

        { call myPackage.mySproc(:param1, :parm2) }
    </sql-query>

When I try this, I get the error:

could not execute native bulk manipulation query

with inner exception:

ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to 'mySproc' ORA-06550: line 1, column 7: PL/SQL: Statement ignored

Now I have of course double checked my parameter list, it matches in the code, the mapping xml and the sproc itself.

Am I way off? Should this be possible? Do I need a in the mapping even though there is no return data? it only executes a SQL update.

DNQ · Accepted Answer · 2015-01-28 13:13:35Z

0

Have you tried:

<sql-query name="myNamedQuery">
        { call myPackage.mySproc(:param1, :param2, :param3) }
</sql-query>

edited Jan 28, 2015 at 13:13

answered Jan 28, 2015 at 13:06

DNQ

579 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

DNQ · Accepted Answer · 2018-06-11 08:02:33Z

0

18.2.2.1. Rules/limitations for using stored procedures

... *For Oracle the following rules apply:

A function must return a result set. The first parameter of a procedure must be an OUT that returns a result set. This is done by using a SYS_REFCURSOR type in Oracle 9i or later. In Oracle you need to define a REF CURSOR type, see Oracle literature.*

Chapter 18. Native SQL

answered Jun 11, 2018 at 8:02

DNQ

579 bronze badges

Collectives™ on Stack Overflow

Oracle update only stored procedure with NHibernate via named query

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related