Procedure with custom type parameter

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
Sergey_Sch
Posts: 4
Joined: Fri 25 Jul 2014 09:03

Procedure with custom type parameter

Post by Sergey_Sch » Fri 25 Jul 2014 09:16

Hi!
In my database have the following objects:

Code: Select all

CREATE TABLE MY_TABLE
(
  ID             NUMBER(12)             NOT NULL,
  TNAME          VARCHAR(50)            NOT NULL
)

TYPE MY_TYPE IS TABLE OF MY_TABLE%ROWTYPE;

PROCEDURE SomeUpdate(in_Assists IN MY_TYPE)
   IS
   BEGIN
      ...
END SomeUpdate;
How do I call a procedure from C# with manually filled parameter? Thanks

Sergey_Sch
Posts: 4
Joined: Fri 25 Jul 2014 09:03

Re: Procedure with custom type parameter

Post by Sergey_Sch » Fri 25 Jul 2014 11:20

I found part of the answer to http://forums.devart.com/viewtopic.php?t=21825 and http://forums.devart.com/viewtopic.php?t=16935 topics, but how to get the type in TABLE%ROWTYPE?

And the second question:
Postby StanislavK » Fri 22 Oct 2010 19:26

At the moment, a user-defined type declared in a package cannot be used outside of this package. We are working on the support of such types outside of packages, but cannot provide any timeframe for this.
it is working now?

Pinturiccio
Devart Team
Posts: 2420
Joined: Wed 02 Nov 2011 09:44

Re: Procedure with custom type parameter

Post by Pinturiccio » Mon 28 Jul 2014 15:34

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();

Post Reply