Calling a PROCEDURE with output parameters

Question

I've got an ORACLE package which receives few parameters and returns - with some other (output) parameters - a unique value (number).
Here's is the package code:

create or replace
PACKAGE BODY           "USP_SHIPMENTS" AS

    PROCEDURE  usp_GetNewShipmentNumber
    (
    pErrorCode OUT NUMBER,
    pMessage OUT VARCHAR2,      
    pCompanyCode IN CHAR,
    pNumber OUT VARCHAR2
    )

    IS

      BEGIN

    pErrorCode := 0;

       UPDATE 
        UTSASHN
       SET 
        UTSASHN.UTSHNCOR = UTSASHN.UTSHNCOR + 1
       WHERE 
        UTSASHN.UTSHCOSC = pCompanyCode AND UTSASHN.UTSHTIPO = 'S***'
    RETURNING 
        CONCAT(TRIM(UTSASHN.UTSHDESC) , TRIM(to_char(UTSASHN.UTSHNCOR, '000000'))) INTO pNumber;

    EXCEPTION
        WHEN OTHERS THEN 
              pErrorCode := SQLCODE;
              ROLLBACK;

    END usp_GetNewShipmentNumber;

END USP_SHIPMENTS;

I've been using this package for a long time using ODP.NET and everything has always worked properly.
Now I am developing a new App with nHibernate 3.1.0.4000. So far I've been able to map all my entities and execute regular queries. Everything works fine.
I was trying to call this package but I keep on getting errors.

This is the mapping for the PROCEDURE:

<?xml version="1.0" encoding="utf-8"?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" assembly="BpSpedizioni" namespace="BpSpedizioni.Domain">
    <sql-query name="GetNewShipmentNumber">
        { call USP_SHIPMENTS.usp_GetNewShipmentNumber ( :pErrorCode, :pMessage, :pCompanyCode, :pNumber) }
    </sql-query>
</hibernate-mapping>

and this is the call:

Session.GetNamedQuery("GetNewShipmentNumber")
      .SetParameter("pErrorCode", "")
      .SetParameter("pMessage", "")
      .SetParameter<string>("pCompanyCode", "HBP00")
      .SetParameter("pNumber", 0)
      .UniqueResult();

I've tried with .UniqueResult() or .ExecuteUpdate() or .List() but I can only get exceptions:

could not execute query
[ USP_SHIPMENTS.usp_GetNewShipmentNumber ]
  Name:pErrorCode - Value:  Name:pMessage - Value:  Name:pCompanyCode - Value:HBP00  Name:pNumber - Value:0
[SQL: USP_SHIPMENTS.usp_GetNewShipmentNumber]

and this is the InnerException:

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

I can't figure out what I am doing wrong! Is there anybody who can help me?

Well you can't do it directly with SQL server No reason why oracle would be different, but I could be wrong. — Conrad Frix
– Conrad Frix, Commented Feb 22, 2012 at 19:25
I know nothing about nhibernate and odp.net, but out parameters might be a problem. Maybe if you rewrite the procedure to a function that will return a table type of errorcode, message and number? — winkbrace
– winkbrace, Commented Feb 22, 2012 at 20:54
@alfasin: of course I have the HEAD of my package. I haven't put it here cause there's not need to show the signature. Who says who can't have a procedure in your package? docs.oracle.com/cd/B10501_01/appdev.920/a96624/09_packs.htm . Anyway, you're missing the point. That package works and it is in use, as I said. You should read the question properly. Unless you want to go on checking "errors" in my package. — LeftyX
– LeftyX, Commented Feb 23, 2012 at 8:54
@BazzPsychoNut: thanks for your reply. It seems a good idea. As a matter of fact that package has been written long time ago and it doesn't do anything particular so I can easily upgrade to better solutions. Thanks. — LeftyX
– LeftyX, Commented Feb 23, 2012 at 9:07
@ConradFrix: thanks for your help. I am sure your solution would have worked but I wanted to dig a little bit into this. Thanks anyway. — LeftyX
– LeftyX, Commented Feb 23, 2012 at 15:49

LeftyX · Accepted Answer · 2017-08-19 18:13:13Z

I managed to make it work. It might not be the best solution but it works.

This is my mapping for the ORACLE PROCEDURE:

<?xml version="1.0" encoding="utf-8"?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" assembly="MyAssembly">
    <sql-query name="GetNewShipmentNumber">
        { call MY_PACKAGE.usp_GetNewShipmentNumber ( :pCompanyCode ) }
    </sql-query>
</hibernate-mapping>

and this the ORACLE PACKAGE:

HEADER:

create or replace
PACKAGE           "MY_PACKAGE" AS

    TYPE ReferenceCursor IS REF CURSOR;

    PROCEDURE  usp_GetNewShipmentNumber
        (
        pCursor OUT ReferenceCursor,
        pCompanyCode IN CHAR
        );

END MY_PACKAGE;

BODY:

create or replace
PACKAGE BODY           "MY_PACKAGE" AS

PROCEDURE  usp_GetNewShipmentNumber
    (
        pCursor OUT ReferenceCursor,
        pCompanyCode IN CHAR
    )

IS

    err_code NUMBER := 0;
    err_msg VARCHAR2(200) := '';
    ShipmentNumber VARCHAR2(10);

  BEGIN

   UPDATE 
        UTSASHN
   SET 
        UTSASHN.UTSHNCOR = UTSASHN.UTSHNCOR + 1
   WHERE 
        UTSASHN.UTSHCOSC = pCompanyCode AND UTSASHN.UTSHTIPO = 'S***'
    RETURNING 
        CONCAT(TRIM(UTSASHN.UTSHDESC) , TRIM(to_char(UTSASHN.UTSHNCOR, '000000'))) INTO ShipmentNumber;

    OPEN pCursor FOR
          SELECT ShipmentNumber AS DeliveryNoteNumber, err_code AS ErrorCode, err_msg AS ErrorMessage FROM DUAL;

    EXCEPTION
        WHEN OTHERS THEN 
          err_code := SQLCODE;
          err_msg := substr(SQLERRM, 1, 200);
          ROLLBACK;

    OPEN pCursor FOR
          SELECT '' AS DeliveryNoteNumber, err_code AS ErrorCode, err_msg AS ErrorMessage FROM DUAL;

END usp_GetNewShipmentNumber;

END MY_PACKAGE;

As you can see I got rid of the return parameters which, apparently, do not work with nHibernate.
I am returning a REF CURSOR instead.

A REF CURSOR must always be the first parameter in a package (documentation (17.2.2.1))

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 9 or 10. In Oracle you need to define a REF CURSOR type, see Oracle literature.

Since I want to return a unique result and I am managing a complex type I've created a class:

public class NewDeliveryNoteNumber
{
    public string DELIVERYNOTENUMBER { get; set; }
    public decimal ERRORCODE { get; set; }
    public string ERRORMESSAGE { get; set; }
}

which will be populated easily like this:

using (var tx = Session.BeginTransaction())
    {
    var x = Session.GetNamedQuery("GetNewShipmentNumber")
        .SetParameter<string>("pCompanyCode", "ABC")
        .SetResultTransformer(Transformers.AliasToBean<NewDeliveryNoteNumber>())
        .UniqueResult<NewDeliveryNoteNumber>();

    tx.Commit();
    }

If someone is interested I've tried to answer another question with some more infos.

Collectives™ on Stack Overflow

Calling a PROCEDURE with output parameters

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related