Procedure with custom type parameter

Procedure with custom type parameter

Postby 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

Postby 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?
Sergey_Sch
 
Posts: 4
Joined: Fri 25 Jul 2014 09:03

Re: Procedure with custom type parameter

Postby 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();
Pinturiccio
Devart Team
 
Posts: 1928
Joined: Wed 02 Nov 2011 09:44


Return to dotConnect for Oracle