Fluent Nhibernate unable to call oracle stored procedure in package body

Question

I am in a fix. I have been trying to call a stored procedure contained within a Package Body to execute. I have tried without success. My code setup follows below;

My mapping file for the stored procedure is Mappings.hbm.xml, content below;

<?xml version="1.0" encoding="utf-8"?>
  <hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" assembly="FAMS">
    <sql-query name="GenerateSchedule">
      { call DPRCN.Generate_Schedule ( :p_asset_id) }
    </sql-query>
</hibernate-mapping>

The Fluent Nhibernate setup in the Global.asax file is below;

private void NhibernateInitiator()
    {
        string cstring = ConfigurationManager.AppSettings["Fams"];
        SessionFactory = Fluently.Configure()
             .Database(OracleClientConfiguration.Oracle10.ConnectionString(c =>
                    c.Is(cstring))
                .Driver<NHibernate.Driver.OracleClientDriver>()
                .ShowSql())
            .Mappings(m =>
                      m.FluentMappings
                          .AddFromAssemblyOf<Asset>()
                          )
            .Mappings(m=>m.HbmMappings.AddFromAssemblyOf<Asset>())
            .ExposeConfiguration(c => c.SetProperty("current_session_context_class", "web"))
            .ExposeConfiguration(ConfigureEnvers)
            .ExposeConfiguration(cfg => new SchemaUpdate(cfg).Execute(false, true))
            .BuildSessionFactory();
    }

A snippet of the Oracle package HEADER

CREATE OR REPLACE PACKAGE DPRCN AS TYPE ReferenceCursor IS REF CURSOR; 
PROCEDURE  Generate_Schedule(pCursor OUT ReferenceCursor,p_asset_id IN Asset.asset_id%TYPE, p_regenerate IN BOOLEAN DEFAULT FALSE);

BODY

CREATE OR REPLACE PACKAGE BODY DPRCN AS
PROCEDURE Generate_Schedule(pCursor OUT ReferenceCursor,p_asset_id IN Asset.Asset_ID%TYPE,
p_regenerate IN BOOLEAN DEFAULT FALSE) AS


BEGIN
OPEN pCursor FOR
.
.
.
.

END Generate_Schedule;

When i call the stored procedure with the code below,

var session = MvcApplication.SessionFactory.GetCurrentSession();
var qry = session.GetNamedQuery("DPRCN.Generate_Schedule");
                    qry.SetParameter("p_asset_id", p.assetId);
                    qry.SetParameter("p_regenerate", false);
                    qry.List();

i get an error. A snippet of the error encountered below;

NHibernate.Exceptions.GenericADOException was caught
HResult=-2146232832
Message=could not execute query
[ { call DPRCN.Generate_Schedule ( ?) } ]
Name:p_asset_id - Value:3
[SQL: { call DPRCN.Generate_Schedule ( ?) }]
Source=NHibernate
SqlString={ call DPRCN.Generate_Schedule ( ?) }
.
.
.
InnerException: System.NotImplementedException
HResult=-2147467263
Message=OracleClientDriver does not support CallableStatement syntax (stored      procedures). 
Consider using OracleDataClientDriver instead.
Source=NHibernate
StackTrace:
at NHibernate.Driver.OracleClientDriver.OnBeforePrepare(IDbCommand command)

I will be delighted if someone can enlighten me on the way forward. Am really stuck here. Thanks.

Update

I have tried the suggestion, i changed to OracleDataClientDriver and imported Oracle.DataAccess.dll but that project did not compile. Running on windows 7 x64. Error encountered was;

[BadImageFormatException: Could not load file or assembly 'Oracle.DataAccess' or one of its dependencies. An attempt was made to load a program with an incorrect format.]

Update2 @mmjak helped resolve initial error but then got an again. NHProfiler read exception trace as;

{ call DPRCN.Generate_Schedule ( 1 /* :p0 */, 0 /* :p1 */) }    Oracle.DataAccess.Client.OracleException ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'GENERATE_SCHEDULE'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored    at Oracle.DataAccess.Client.OracleException.HandleErrorHelper(Int32 errCode, OracleConnection conn, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, String procedure)
   at Oracle.DataAccess.Client.OracleException.HandleError(Int32 errCode, OracleConnection conn, String procedure, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src)
   at Oracle.DataAccess.Client.OracleCommand.ExecuteNonQuery()
   at NHibernate.AdoNet.AbstractBatcher.ExecuteNonQuery(IDbCommand cmd)
   at NHibernate.Engine.Query.NativeSQLQueryPlan.PerformExecuteUpdate(QueryParameters queryParameters, ISessionImplementor session)

Have you already tried what exception suggests: `"Consider using OracleDataClientDriver instead"`? some hint http://stackoverflow.com/questions/6141703/ — Radim Köhler, May 25 '14 at 14:24

Michał Marcinkowski · Accepted Answer · 2014-05-26T12:45:31.033

0

it is looks like confilicts between x86 and x64, one dirty but working solution when you're using odp.net:

delete reference from project
copy oci.dll, oracle.data.access.dll, oraocie11.dll, oraops11w.dll from your oracle installation folder to bin of your application
add reference > browse - select previously copied oracle.dataaccess.dll
select copy local - true and specific version = true
enable 32bit apps on you app pool
then you must change your connectionString to format

Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=[address])(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=[tns]))); User Id=[username]; Password=[password];

edited May 26 '14 at 12:45

answered May 26 '14 at 11:46

Michał Marcinkowski

612
4
20

THANKS, i followed your steps and resolved the error. My proc call now results in error; "PLS-00306: wrong number or types of arguments in call to 'GENERATE_SCHEDULE' ORA-06550: line 1, column 7: PL/SQL: Statement ignored Source=Oracle Data Provider for .NET ErrorCode=-2147467259 DataSource=(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = [address])(PORT = 1521)))(CONNECT_DATA =(SID = ORACLS))) Number=6550 Procedure=DPRCN.Generate_Schedule" – BlowMan May 28 '14 at 07:23
i suppose it's caused by your mappings in hbm.xml file, try to { call DPRCN.Generate_Schedule ( :p_asset_id, :p_regenerate) } – Michał Marcinkowski May 28 '14 at 11:34
Even after calling package like this { call DPRCN.Generate_Schedule ( :p_asset_id, :p_regenerate) }, there is an error. I have updated the question(**Update2**) with the trace – BlowMan May 28 '14 at 15:18

Fluent Nhibernate unable to call oracle stored procedure in package body

1 Answers1