Calling a PROCEDURE with output parameters

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've 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();
    }

 

http://topic.csdn.net/u/20110706/08/7699c270-3b69-4312-9408-404c945f894a.html