问题描述
我想通过c#将整数数组传递给存储过程.该过程可通过sql developer开发,但在c#中不起作用.这是我的代码,但是我被卡住了wrong number or types of arguments in call to 'V1'
错误.请帮我C#代码:
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 thewrong number or types of arguments in call to 'V1'
error. please help mec# 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();
我的程序:
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;
和我的类型:
create or replace TYPE INNUMARRAY AS TABLE OF INTEGER;
推荐答案
您的类型:
create or replace TYPE INNUMARRAY AS TABLE OF INTEGER;
是在SQL范围内定义的集合.
is a collection defined in the SQL scope.
您传递的参数:
p_strings.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
p_strings.Value = new int[5]{1,2,3,4,5};
是一个关联数组,只能在PL/SQL范围内(即在程序包中或在PL/SQL块内)定义,而不能在SQL范围内使用.
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.
相反,您可以在包中创建关联数组类型,然后将关联数组中的每个值手动提取到可以在SQL范围内使用的集合中:
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;
/
否,但是您可以定义一个仅包含以下类型的包:
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:
Oracle 11g R2架构设置:
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;
/
这篇关于通过C#将整数数组传递给oracle过程的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!