Comments: Calling Oracle Procedure inside package

Here is how i have succeeded for a project that we had to migrate to Oracle.

1. Create CustomOracleDataClientDriver.

public class CustomOracleDataClientDriver : OracleDataClientDriver

   {

       private readonly PropertyInfo oracleDbType;

       private readonly object oracleDbTypeRefCursor;

       public CustomOracleDataClientDriver()

       {

           oracleDbType = ReflectHelper.TypeFromAssembly("Oracle.DataAccess.Client.OracleParameter", "Oracle.DataAccess", false).GetProperty("OracleDbType");

           Type enumType = ReflectHelper.TypeFromAssembly("Oracle.DataAccess.Client.OracleDbType", "Oracle.DataAccess", false);

           oracleDbTypeRefCursor = Enum.Parse(enumType, "RefCursor");

       }

       protected override void OnBeforePrepare(IDbCommand command)

       {

           CallableParser.Detail detail = CallableParser.Parse(command.CommandText);

           if (detail.IsCallable)

           {

               command.CommandType = CommandType.StoredProcedure;

               command.CommandText = detail.FunctionName;

               IDbDataParameter parameter = command.CreateParameter();

               oracleDbType.SetValue(parameter, oracleDbTypeRefCursor, null);

               parameter.Direction = detail.HasReturn ? ParameterDirection.ReturnValue : ParameterDirection.Output;

               command.Parameters.Insert(0, parameter);

           }

           else

           {

               base.OnBeforePrepare(command);

           }

       }

   }

2. Create CallableParser(meaning copy CallableParser from reflector and changed the regex)

   public static class CallableParser

   {

       private static readonly Regex functionNameFinder;

       static CallableParser()

       {

          // functionNameFinder = new Regex(@"\{[\S\s]*call[\s]+([\w]+)[^\w]");

           functionNameFinder = new Regex(@"\{[\S\s]*call[\s]+([\.\w]+)[^\w]");

       }

       public static Detail Parse(string sqlString)

       {

           Detail detail = new Detail();

           detail.IsCallable = ((sqlString.IndexOf("{") == 0) && (sqlString.IndexOf("}") == (sqlString.Length - 1))) && (sqlString.IndexOf("call") > 0);

           if (detail.IsCallable)

           {

               Match match = functionNameFinder.Match(sqlString);

               if (!match.Success || (match.Groups.Count < 2))

               {

                   throw new HibernateException("Could not determine function name for callable SQL: " + sqlString);

               }

               detail.FunctionName = match.Groups[1].Value;

               detail.HasReturn = (((sqlString.IndexOf("call") > 0) && (sqlString.IndexOf("?") > 0)) && ((sqlString.IndexOf("=") > 0) && (sqlString.IndexOf("?") < sqlString.IndexOf("call")))) && (sqlString.IndexOf("=") < sqlString.IndexOf("call"));

           }

           return detail;

       }

       public class Detail

       {

           public string FunctionName;

           public bool HasReturn;

           public bool IsCallable;

       }

   }

3. Change in the hibernate-configuration the connection.driver_class to:

<property name="connection.driver_class">{your namespace}.CustomOracleDataClientDriver,{your assembly}</property>

4. In the mapping file

<sql-query name="TestQuery" callable="true">

       <return class="NameOfTheClass">

           <return-property name="Id" column="ID"></return-property>

           ...

       </return>

<!-- Call procedure like procedureName(myCursor OUT SYS_REFCURSOR)-->

       {call mypackage.procedurename}

<!-- Call function like functionName RETURN SYS_REFCURSOR-->

{? = mypackage.functionName}

   </sql-query>

Hi Paul,

Thanks for the reply.Your solution works gr8.

Glad that you could find it usefull.

Here is another way that i discover to call a function that return a scalar value.

Supposing that we have:

create or replace package body ScalarTest as

function GetNumberOfRows return int is

numberOfRows int;

begin

--Publication is a table

select count(*) as TotalRows from Publication;

end;

end;

<sql-query name="ScalarTest" callable="true">

<return-scalar column="TotalRows" type="System.Int32"/>

 select scalartest.getnumberofrows as TotalRows from dual

</sql-query>

When you call session.GetNamedQuery("ScalarTest").UniqueResult<int>() it will return the number of rows.

Hope you find this usefull too.

Well i made some syntax error sorry for that(I was in a hurry)

The correction(better later than never:))

1.

create or replace package body ScalarTest as

function GetNumberOfRows return integer is

numberOfRows int;

begin

select count(*) into numberOfRows from publication;

return numberOfRows;

end;

2. A function should be call as {? = call mypackage.functionName}

All the best!

I think you could make or replace the package body Scalar test so as to get the result. Anyway, the method by which one has to create the Custom Oracle data client driver is really tasking and one has to go a long way before getting it done. So, I would not suggest anyone to go forward with that technique. If you are looking to call procedure with the package name, the best bet for you would be to move on with the first method!

http://raidious.com