`
wuhuizhong
  • 浏览: 681052 次
  • 性别: 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

 

 

 

 

分享到:
评论

相关推荐

    UNIDAC 6.4.16 XE10~10.1

    Bug with adding Calculated fields after calling Prepare is fixed Oracle data provider Bug with XML when UnicodeEnvironment=True is fixed SQLServer data provider Bug with WITH clause in TUniScript is ...

    UNDAC 6.4.16 XE10.1

    Bug with adding Calculated fields after calling Prepare is fixed Oracle data provider Bug with XML when UnicodeEnvironment=True is fixed SQLServer data provider Bug with WITH clause in TUniScript is ...

    UNIDAC 6.4.16 XE8

    Bug with adding Calculated fields after calling Prepare is fixed Oracle data provider Bug with XML when UnicodeEnvironment=True is fixed SQLServer data provider Bug with WITH clause in TUniScript is ...

    UNIDAC 6.4.16 XE10

    Bug with adding Calculated fields after calling Prepare is fixed Oracle data provider Bug with XML when UnicodeEnvironment=True is fixed SQLServer data provider Bug with WITH clause in TUniScript is ...

    MySql存储过程编程.chm

    Calling a Stored Procedure from PHP Section 2.14. Conclusion Chapter 3. Language Fundamentals Section 3.1. Variables, Literals, Parameters, and Comments Section 3.2. Operators Section 3.3. ...

    unidac_7_1_4_pro DELPHI 10 Tokyo

    Bug with the "Input parameter mismatch" error for procedure is fixed Bug with transactions when working with Firebird 2.5 is fixed 7.1.3 19-Sep-17 The performance of TVirtualQuery is significantly ...

    Unidac Pro 7.1.4 XE8

    Bug with the "Input parameter mismatch" error for procedure is fixed Bug with transactions when working with Firebird 2.5 is fixed 7.1.3 19-Sep-17 The performance of TVirtualQuery is significantly ...

    unidac_7_1_4_pro DELPHI 10 Berlin

    Bug with the "Input parameter mismatch" error for procedure is fixed Bug with transactions when working with Firebird 2.5 is fixed 7.1.3 19-Sep-17 The performance of TVirtualQuery is significantly ...

    Python库 | joint-calling-0.1.88.tar.gz

    标题中的"Python库 | joint-calling-0.1.88.tar.gz"表明这是一个与Python相关的库,且版本号为0.1.88,它被打包成一个tar.gz文件。这种格式通常用于在Unix/Linux环境中分发软件,因为它可以进行文件压缩并合并多个...

    UniDAC 7.1.4

    Bug with the "Input parameter mismatch" error for procedure is fixed Bug with transactions when working with Firebird 2.5 is fixed 7.1.3 19-Sep-17 The performance of TVirtualQuery is significantly ...

    Auto Debug Professional 5.6

    It's easy to use --- Only setting the APIs which we want to monitor with ON, once the target application running and calling these APIs, it will monitor their parameters of inputting and outputting ...

    Calling Conventions Topics

    int __cdecl add(int a, int b); ``` 调用示例: ```c++ int result = add(5, 10); ``` 在这个例子中,参数 `10` 和 `5` 将按照从右到左的顺序被压入栈中,然后调用者需要清理栈。 #### Naked Function Calls ...

    Auto Debug Professional 5.0

    It's easy to use --- Only setting the APIs which we want to monitor with ON, once the target application running and calling these APIs, it will monitor their parameters of inputting and outputting ...

    matlab example.rar

    This tool and function ... Calling 'mdfimport' with parameters reads the signals in directly. Instructions: Unzip attached files and put on your path or in the current directory. Type mdfimport to run.

    Microsoft Teams Calling Solutions Calling Solutions.pdf

    Microsoft Teams Calling Solutions 知识点 Microsoft Teams Calling Solutions 是微软公司的一项电话解决方案,旨在帮助企业和中小型企业(SMB)提高通信效率,降低成本。根据 Forrester 的研究报告,本解决方案...

    ARASHI calling 歌词

    《ARASHI calling》是日本流行音乐团体ARASHI的一首歌曲,歌词中充满了积极向上的力量和对生命的执着追求。这首歌的主题围绕着把握现在、永不放弃和自我信念展开。 歌词中的“Calling”象征着呼唤,可能是对生活、...

    2009 达内Unix学习笔记

    [] 匹配中括号里的内容[a-z][A-Z][0-9]。 ! 事件。 $ 取环境变量的值。 | 管道。把前一命令的输出作为后一命令的输入,把几个命令连接起来。 |经常跟tee连用,tee 把内容保存到文档并显示出来。 三、通用后...

    Smoking_calling_train.zip

    本篇将详细探讨一个名为"Smoking_calling_train.zip"的数据集,它是一个专门用于训练AI模型,识别人们在吸烟时打电话这一复杂行为的宝贵资源。通过对这个数据集的理解和分析,我们可以更深入地了解AI在图像识别和...

    Using LUA with Visual C++ (Introduction)

    LUA is pure ANSI C code, this means that if you build the code with a C++ compiler it will complain with "error LNK2001: unresolved external symbol" messages. Two easy ways exist to resolve this ...

Global site tag (gtag.js) - Google Analytics