pass integer array to oracle procedure by c#

Question

I want to pass an integer array to a stored procedure via c#. the procedure works via sql developer but in c# it doesn't work. this is my code but i got stuck by the wrong number or types of arguments in call to 'V1' error. please help me c# code:

DBEngine oracleEngine = new OracleEngine(connectionString);

DbCommand cmd = oracleEngine.MakeTextCmd("v1");
cmd.CommandType = CommandType.StoredProcedure;

OracleParameter param1 = new OracleParameter();

List<int> values = new List<int>() { 1, 2, 3, 4, 5 };

OracleParameter p_strings = new OracleParameter();
p_strings.ParameterName = "VehicleGroupID_Array";
p_strings.OracleDbType = OracleDbType.Int32;
p_strings.Direction = ParameterDirection.Input;
p_strings.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
p_strings.Value = new int[5]{1,2,3,4,5};
cmd.Parameters.Add(p_strings);

//DbDataReader reader = oracleEngine.ExecuteReader(cmd);
cmd.ExecuteNonQuery();

my procedure:

create or replace PROCEDURE v1
(
  VehicleGroupID_Array IN INNUMARRAY --  List
)
IS
  p_recordset SYS_REFCURSOR;
BEGIN
  OPEN p_recordset FOR
  SELECT DISTINCT
         "vUserVehicles"."UserID",
         "vUserVehicles"."VehicleID",
         "vUserVehicles"."VehicleName",
         "vUserVehicles"."VehicleSerialNo",
         "vUserVehicles"."Description",
         "vUserVehicles"."VehicleNo",
         "vUserVehicles"."VehicleShahrbaniNo",
         "vUserVehicles"."GSMWirelessDialNo",
         "vUserVehicles"."Status",
         "vUserVehicles"."ThurayaDialNo",
         "vUserVehicles"."Company",
         "vUserVehicles"."MachineModelId",
         "vUserVehicles"."VehicleTypeID",
         "vUserVehicles"."Consumption",
         "vUserVehicles"."RegistrationCode",
         "vUserVehicles"."VehicleKindId"
  FROM   "vUserVehicles"
         INNER JOIN "VehicleGroupDetail"
         ON "vUserVehicles"."VehicleID" = "VehicleGroupDetail"."VehicleID"
  WHERE  "VehicleGroupDetail"."VehicleGroupID" IN (
           select column_value from table(VehicleGroupID_Array))
         )
  ORDER BY "vUserVehicles"."Description" ASC;

  DBMS_SQL.RETURN_RESULT(p_recordset);
END;

and my type:

create or replace TYPE INNUMARRAY AS TABLE OF INTEGER;

MT0 · Accepted Answer · 2018-05-23 11:39:51Z

4

Your type:

create or replace TYPE INNUMARRAY AS TABLE OF INTEGER;

is a collection defined in the SQL scope.

Your passed argument:

p_strings.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
p_strings.Value = new int[5]{1,2,3,4,5};

is an associative array which can only be defined in a PL/SQL scope (i.e. in a package or within a PL/SQL block) and cannot be used in an SQL scope.

They are two different and incompatible data types.

Instead, you can create an associative array type in a package and then manually extract each value from the associative array into a collection that can be used in the SQL scope:

CREATE PACKAGE vehicles_pkg IS
  TYPE INNUMASSOCARRAY IS TABLE OF INTEGER INDEX BY BINARY_INTEGER;

  PROCEDURE v1
  (
    VehicleGroupID_Array IN INNUMASSOCARRAY
  );
END;
/

CREATE PACKAGE BODY vehicles_pkg IS
  PROCEDURE v1
  (
    VehicleGroupID_Array IN INNUMASSOCARRAY
  )
  IS
    p_recordset SYS_REFCURSOR;
    p_array     INNUMARRAY := INNUMARRAY();
    i           BINARY_INTEGER;
  BEGIN
    i := VehicleGroupID_Array.FIRST;
    WHILE i IS NOT NULL LOOP
      p_array.EXTEND;
      p_array( p_array.COUNT ) := VehicleGroupID_Array(i);
      i := VehicleGroupID_Array.NEXT(i);
    END LOOP;

    -- Rest of your procedure using p_array instead of the associative array.
  END;
END;
/

can I define the associative array type outside of package? I want them to be standalone.

No, but you can define a package just containing the type:

CREATE PACKAGE vehicles_pkg IS
  TYPE INNUMASSOCARRAY IS TABLE OF INTEGER INDEX BY BINARY_INTEGER;
END;
/

CREATE PROCEDURE v1
(
  VehicleGroupID_Array IN vehicles_pkg.INNUMASSOCARRAY
)
IS
  p_recordset SYS_REFCURSOR;
  p_array     INNUMARRAY := INNUMARRAY();
  i           BINARY_INTEGER;
BEGIN
  i := VehicleGroupID_Array.FIRST;
  WHILE i IS NOT NULL LOOP
    p_array.EXTEND;
    p_array( p_array.COUNT ) := VehicleGroupID_Array(i);
    i := VehicleGroupID_Array.NEXT(i);
  END LOOP;

  -- Rest of your procedure using p_array instead of the associative array.
END;
/

Or, better, create some generically named types and a function in the package to translate from an associative array to a collection and then reuse them in your procedures:

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TYPE IntList AS TABLE OF INTEGER
/

CREATE PACKAGE tools IS
  TYPE IntMap IS TABLE OF INTEGER INDEX BY BINARY_INTEGER;

  FUNCTION IntMapToList(
    i_map IntMap
  ) RETURN IntList;
END;
/

CREATE PACKAGE BODY tools IS
  FUNCTION IntMapToList(
    i_map IntMap
  ) RETURN IntList
  IS
    o_list IntList := IntList();
    i      BINARY_INTEGER;
  BEGIN
    IF i_map IS NOT NULL THEN
      i := o_list.FIRST;
      WHILE i IS NOT NULL LOOP
        o_list.EXTEND;
        o_list( o_list.COUNT ) := i_map( i );
        i := i_map.NEXT( i );
      END LOOP;
    END IF;
    RETURN o_list;
  END;
END;
/

CREATE PROCEDURE v1
(
  VehicleGroupID_Array IN tools.IntMap
)
IS
  p_recordset SYS_REFCURSOR;
  p_array     IntList := tools.IntMapToList( VehicleGroupID_Array );
  i           BINARY_INTEGER;
BEGIN
  -- Rest of your procedure using p_array instead of the associative array.
  NULL;
END;
/

edited May 23, 2018 at 11:39

answered May 23, 2018 at 10:20

MT0

173k12 gold badges70 silver badges136 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

masoud vali Over a year ago

I don't want to create associative array type. Can I pass collection to procedure?

MT0 Over a year ago

@masoudvali Yes.... but not using C# as, for some reason, C# only supports passing associative arrays.

masoud vali Over a year ago

can I define the associative array type outside of package? I want them to be standalone. @MT0

MT0 Over a year ago

@masoudvali Not in any useful way (you can define it in an anonymous PL/SQL block but then you can't reference it in the procedure or from C#). If you want it to be standalone then define the type in a package and the procedure in the SQL scope - I will add an update shortly.

Kaushik Nayak Over a year ago

Some folks can't be convinced, although it could never be more succinct than this :-)

Wernfried Domscheit · Accepted Answer · 2018-05-23 10:25:11Z

0

An Associative Array is defined by create or replace TYPE INNUMARRAY AS TABLE OF INTEGER INDEX BY PLS_INTEGER;

However, you cannot use Associative Arrays in TABLE(...) expression. For this you must convert the Associative Array into a Nested Table (without INDEX BY PLS_INTEGER), for example like this:

create or replace TYPE VehicleGroupID_TableType AS TABLE OF INTEGER;

...

VehicleGroupID_Table VehicleGroupID_TableType  := VehicleGroupID_TableType();

BEGIN

    FOR i IN VehicleGroupID_Array.FIRST..VehicleGroupID_Array.LAST LOOP
        VehicleGroupID_Table.EXTEND;
        VehicleGroupID_Table(VehicleGroupID_Table.LAST) := VehicleGroupID_Array(i);
    END LOOP;

    ... 
    WHERE  "VehicleGroupDetail"."VehicleGroupID" IN (
       select column_value from table(VehicleGroupID_Table))
     )

or shorter version:

WHERE  "VehicleGroupDetail"."VehicleGroupID" MEMBER OF VehicleGroupID_Table

edited May 23, 2018 at 10:25

answered May 23, 2018 at 10:17

Wernfried Domscheit

60.4k10 gold badges92 silver badges132 bronze badges

3 Comments

MT0 Over a year ago

You cannot create an associative array using CREATE OR REPLACE TYPE in the SQL scope as it can only be defined in the PL/SQL scope. Also, FOR i IN array.FIRST .. array.LAST LOOP may not always work if the array is sparse (which should not happen from C# using the OPs method but may happen if the procedure is called from another source).

Wernfried Domscheit Over a year ago

I assume MEMBER OF works also with local types - but I did not test

MT0 Over a year ago

Collections defined in the PL/SQL scope cannot be used in the SQL scope in Oracle 11 and earlier (regardless of whether you use the MEMBER OF operator or a table collection expression). They may work with types defined locally in a PL/SQL scope in Oracle 12 (however associative arrays still won't work).

Collectives™ on Stack Overflow

pass integer array to oracle procedure by c#

2 Answers 2

5 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related