If we understood you correctly, you are using a package, containing a type and a procedure. In such case you cannot use the procedure with a parameter of the custom type. This is an OCI limitation.
You could use such logics if you move the definitions of the procedure and the type out of the package. However, another Oracle limitation applies to such case. %RowType is a PL/SQL data type, аnd the SQL engine does not support PL/SQL data types. You will need to replace the definition of this type with the following two definitions:
Code: Select all
CREATE OR REPLACE TYPE type1 AS OBJECT(/*MY_TABLE columns*/ID NUMBER(12), TNAME VARCHAR(50));
CREATE OR REPLACE TYPE MY_TYPE AS TABLE OF type1;
the procedure definition will be the following:
Code: Select all
CREATE OR REPLACE PROCEDURE SomeUpdate(in_Assists IN MY_TYPE)
IS
BEGIN
-- Body of your procedure or function
DBMS_OUTPUT.PUT_LINE('work');
END;
C# code:
Code: Select all
OracleConnection conn = new OracleConnection("your connection string");
conn.Open();
OracleCommand comm = new OracleCommand("SomeUpdate", conn);
comm.CommandType = System.Data.CommandType.StoredProcedure;
OracleParameter par2 = comm.Parameters.Add("in_Assists", OracleDbType.Table);
par2.ObjectTypeName = "MY_TYPE";
par2.Value = new OracleTable(OracleType.GetObjectType("MY_TYPE", conn));
comm.ExecuteNonQuery();
conn.Close();