Is it possible to call stored Procedure from .net application passing list of Array of string or integer as parameter

Question

I know this is possible from Database to create a Type and send it to Stored procedure as a parameter .

But it seems it does not work with .net application, be it ADO.net or Nhibernate.

Oracle Version

10g.

ODP.net

11.2

Assembly Version :2.112.2.0

Product Version : 2.112.2.0

DLL: Oracle.DataAccess.dll

Type Created IN ORACLE

create or replace TYPE NUMBER_ARRAY IS TABLE OF NUMBER ;

Stored Procedure
create or replace PROCEDURE odp_varray_sample_proc_test(param IN NUMBER_ARRAY) IS BEGIN insert into employee (emp_id, emp_name, emp_password, is_captain, no_of_mom) values (45455,'Adam','123545','Y',0); END odp_varray_sample_proc_test;

C# Code

ISQLQuery query = eventhistorysession.CreateSQLQuery("BEGIN odp_varray_sample_proc_test(:param); END;");

query.SetParameter("param", new Int64[] { 259688 },new Int64ArrayType());

int final = query.ExecuteUpdate();

Exception

NHibernate.Exceptions.GenericADOException was caught Message="could not execute native bulk manipulation query:BEGIN odp_varray_sample_proc_test(:param); END;[SQL: BEGIN odp_varray_sample_proc_test(:p0); END;]" Source="NHibernate" SqlString="BEGIN odp_varray_sample_proc_test(:p0); END;" StackTrace: at NHibernate.Engine.Query.NativeSQLQueryPlan.PerformExecuteUpdate(QueryParameters queryParameters, ISessionImplementor session) at NHibernate.Impl.SessionImpl.ExecuteNativeUpdate(NativeSQLQuerySpecification nativeQuerySpecification, QueryParameters queryParameters) at NHibernate.Impl.SqlQueryImpl.ExecuteUpdate() at TestDatabase.Program.InsertNhibernate() in C:\Jaroori Kachra\TestDatabase\TestDatabase\Program.cs:line 184 InnerException: Oracle.DataAccess.Client.OracleException Message="ORA-06550: line 1, column 7:\nPLS-00306: wrong number or types of arguments in call to 'ODP_VARRAY_SAMPLE_PROC_TEST'\nORA-06550: line 1, column 7:\nPL/SQL: Statement ignored" Source="Oracle Data Provider for .NET" ErrorCode=-2147467259 DataSource="RACDV" Number=6550 Procedure="" StackTrace: at Oracle.DataAccess.Client.OracleException.HandleErrorHelper(Int32 errCode, OracleConnection conn, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, String procedure, Boolean bCheck) at Oracle.DataAccess.Client.OracleException.HandleError(Int32 errCode, OracleConnection conn, String procedure, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, Boolean bCheck) at Oracle.DataAccess.Client.OracleCommand.ExecuteNonQuery() at NHibernate.AdoNet.AbstractBatcher.ExecuteNonQuery(IDbCommand cmd) at NHibernate.Engine.Query.NativeSQLQueryPlan.PerformExecuteUpdate(QueryParameters queryParameters, ISessionImplementor session) InnerException:

cremor cremor · Accepted Answer · 2011-04-18T10:01:16

Example type and procedure in Oracle:

TYPE NUMBER_ARRAY IS TABLE OF NUMBER NOT NULL INDEX BY PLS_INTEGER;
PROCEDURE procedure_name(array IN NUMBER_ARRAY);

Example call of the stored procedure with NHibernate:

ISQLQuery query = _session.CreateSQLQuery("BEGIN procedure_name(:array); END;"
query.SetParameter("array", array, new Int64ArrayType());
query.ExecuteUpdate();

You need to implement that Int64ArrayType yourself:

[Serializable]
public class Int64ArrayType : ImmutableType {
  public Int64ArrayType()
     : base(SqlTypeFactory.Int64) {
  }

  public override object Get(System.Data.IDataReader rs, string name) {
     return rs[name];
  }

  public override object Get(System.Data.IDataReader rs, int index) {
     return rs[index];
  }

  public override void Set(System.Data.IDbCommand cmd, object value, int index) {
     OracleCommand orclCmd = (OracleCommand)cmd;
     orclCmd.Parameters[index].OracleDbType = OracleDbType.Int64;
     orclCmd.Parameters[index].CollectionType = OracleCollectionType.PLSQLAssociativeArray;
     orclCmd.Parameters[index].Value = value;
  }

  public override string ToString(object val) {
     return String.Join(";", (object[])val);
  }

  public override string Name {
     get { return "Int64ArrayType"; }
  }

  public override Type ReturnedClass {
     get { return typeof(long[]); }
  }

  public override object FromStringValue(string xml) {
     string[] stringElements = xml.Split(';');
     long[] array = new long[stringElements.Length];

     for (int i = 0; i < stringElements.Length; i++) {
        array[i] = Convert.ToInt64(stringElements[i]);
     }

     return array;
  }
}

Is it possible to call stored Procedure from .net application passing list of Array of string or integer as parameter

2 Answers