`
wuhuizhong
  • 浏览: 693739 次
  • 性别: Icon_minigender_1
  • 来自: 中山
社区版块
存档分类
最新评论

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

 

 

 

 

分享到:
评论

相关推荐

    IBO v5.3.3 Build 1955(January 2, 2014) Full Source

    I improved the parsing of input parameters to consider stored procedures as well as tables when deciphering when a parameter with the prefix of OLD_ is being used. I added a compatibility unit ...

    Sakemail

    retrieved and SendMessage is a boolean function (maybe someone has pressed the cancel btn).- Fixed a bug when the subject field is too large.9/2/981.5.1- Fixed a bug with a message within a message ...

    微软内部资料-SQL性能优化2

    A stack is an area of memory in which program procedure or function call addresses and parameters are temporarily stored. In Process To run in the same address space. In-process servers are loaded ...

    VclZip pro v3.10.1

    This was due to a problem where it would be freed automatically if there was a problem with the ArchiveStream when trying to open it as a zip file (possibly corrupt). Best practice is that ...

    The Art of Assembly Language Programming

    Some Final Comments on the MOV Instructions &lt;br&gt;4.9 Laboratory ...with MASM 4.9.5 Debuggers and CodeView' 4.9.5.1 A Quick Look at CodeView 4.9.5.2 The Source Window 4.9.5.3 The Memory ...

    数位板压力测试

    This document describes a programming interface for using digitizing tablets and other advanced pointing de¬vices with Microsoft Windows Version 3.0 and above. The design presented here is based on ...

    FlexGraphics_V_1.79_D4-XE10.2_Downloadly.ir

    - FIX: The value of some string flex-properties that began with a parenthese or curly bracket had no apostrophe at the end; that caused an error when reading. (fixed TPropList.SavePropValue for the ...

    uhdd.sys源码

    XMGR RDISK and UIDE are a group of DOS device drivers for a PC system with an 80386+ CPU and using MS DOS V5 0+ or equivalent XMGR is a DOS driver which works as an &quot;XMS manager&quot; and ...

    occam一维反演

    c runocc is the calling program for OCCAM v2.0. C C REFERENCES: CONSTABLE, PARKER & CONSTABLE, 1987: GEOPHYSICS 52, 289-300. C DEGROOT-HEDLIN & CONSTABLE, 1990: GEOPHYSICS 55, 1613-1624. C CONSTABLE,...

Global site tag (gtag.js) - Google Analytics