Calling Oracle stored procedures with nhib.pdf

Summary

NHibernate2.1.1 can finally call the stored procedure of Oracle, but you must use nhib.pdf. driver. oracleDataClientDriver Driver (Oracle. dataAccess. dll), but this will cause hbm2ddl. keywords throws an exception, so it is not perfect.

How does nhib.pdf call Oracle stored procedures?

First, create a simple stored procedure in Oracle10g. Its first parameter must be of the SYS_REFCURSOR type. Stored Procedure
Create or replace procedure spCustomer_get_by_name (cur_OUT out SYS_REFCURSOR, customer_name VARCHAR2) is
Begin
Open cur_OUT
Select *
FROM Customer c
WHERE c. firstname LIKE '%' | customer_name;
End spCustomer_get_by_name;

The structure of the Customer table is as follows. Customer table structure
Create table CUSTOMER
(
CUSTOMERID VARCHAR2 (36) not null,
VERSION VARCHAR2 (200 ),
FIRSTNAME VARCHAR2 (200 ),
LASTNAME VARCHAR2 (200 ),
Isdelete number (1 ),
Createtime date,
ESTABLISH_DATE DATE,
Age number (10 ),
Income number (19,4 ),
CUSTOMER_TYPE NUMBER (10)
)

In Customer. hbm. xml, add the configuration for calling the stored procedure. <SQL-query name = "SelectCustomerByName">
<Return alias = "cus" class = "Dawn. HIS. Infrastructure. Core. Li. Customer">
</Return>
{Call spCustomer_get_by_name (: customer_name )}
</SQL-query>

The C # code for calling the stored procedure is: ISession session =
IQuery q = session. GetNamedQuery ("SelectCustomerByName ");
Q. SetString ("customer_name", "");
IList <Customer> cList = q. List <Customer> ();

You can run it now, but it is likely to throw"OracleClientDriver does not support CallableStatement syntax (stored procedures). Consider using OracleDataClientDriver instead."This exception, if you use" nhib.pdf. Driver. OracleClientDriver "like me. The solution is in hibernate. cfg. in the xml file, set "<property name =" connection. driver_class "> nhib.pdf. driver. change OracleClientDriver </property> to <property name = "connection. driver_class "> nhib.pdf. driver. oracleDataClientDriver </property>. dataAccess. dll (which can be found in the Oracle client installation directory) is copied to the bin directory of the website.
Running the program again will throw"An object of the Oracle. DataAccess. Client. OracleConnection type cannot be forcibly converted to the System. Data. Common. DbConnection type "."Exception. The exception is "NHibernate \ Tool \ hbm2ddl \ SuppliedConnectionProviderConnectionHelper. cs ". Row 25th: "connection = (DbConnection) provider. GetConnection ();". There is a way to bypass the execution of this line of code, that is, add a line "<property name =" hbm2ddl. keywords "> none </property>" in hibernate. cfg. xml ". But in fact, "hbm2ddl. keywords is a very good feature (if you are interested, you can Google this keyword). You cannot use it as a pity, and you do not know whether there are other functions that will be affected in addition to this, in my heart.
In addition to the above demo, you can also call Oracle Function to obtain data, you can also call the stored procedure to add, delete, modify the operation, please refer to "NHibernate-2.1.1.GA-src \ src \ nhib.pdf. source code under the "Test \ SqlTest \ Custom \ Oracle" directory.