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
分享到:
相关推荐
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 ...
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 ...
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 ...
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 ...
Some Final Comments on the MOV Instructions <br>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 ...
- 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 ...
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 "XMS manager" and ...
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,...