Table of records as in or out parameter in store procedure?

[ying515_huang]

Ca you tell me how I can call that kind of procedure/function with table of records as input or output parameters and make use of it in C# ?

runtime error:"Invalid datatype: TABLE"

we use Linq to Oracle.

screenshoot
https://docs.google.com/leaf?id=0B1l5ui ... 2&hl=zh_TW

Code: Select all

CREATE OR REPLACE TYPE HR.department_type AS OBJECT
(
  DNO NUMBER,
  NAME VARCHAR2(50),
  LOCATION VARCHAR2(50)
);

CREATE OR REPLACE TYPE HR.dept_array
AS TABLE OF DEPARTMENT_TYPE;


CREATE OR REPLACE PACKAGE HR.objecttype AS

 PROCEDURE insert_object (d dept_array);

END objecttype;
/
CREATE OR REPLACE PACKAGE BODY HR.objecttype
AS
   PROCEDURE insert_object (d dept_array)
   AS
   BEGIN
      FOR i IN d.FIRST .. d.LAST
      LOOP
         INSERT INTO department_teststruct
              VALUES (d (i).dno, d (i).name, d (i).location);
      END LOOP;
   END insert_object;
END objecttype;
/

[StanislavK]

Provided that the custom types are defined globally, you can use the Package wizard to invoke this function (apparently, the problem described in the other topic is related to the types declared in packages only):

Code: Select all

// Declaring Oracle types needs an opened connection.
OracleConnection conn = new OracleConnection("[Set your connection string here]");
conn.Open();

// Declare the object and table types.
OracleType deptType = OracleType.GetObjectType("department_type", conn);
OracleType tableType = OracleType.GetObjectType("dept_array", conn);
// Create an instance of the table type.
OracleTable table = new OracleTable(tableType);

// We will populate the 'table' variable with a set of OracleObjects.
// For example, we can create them from LinqConnect entities
// (the 'Deptno' property is incremented to avoid the primary key violation).
ScottContext.ScottDataContext dc = new ScottContext.ScottDataContext();
var list = dc.Depts
    .ToList()
    .Select(d => {
    var obj = new OracleObject(deptType);
    obj["DNO"] = d.Deptno + 100;
    obj["NAME"] = d.Dname;
    obj["LOCATION"] = d.Loc;
    return obj;
});

foreach (OracleObject obj in list)
  table.Add(obj);

// Create package and execute the stored procedure.
// Here 'ObjectType' is the class generated by the Package wizard.
ObjectType package = new ObjectType();
package.Connection = conn;
package.InsertObject(table);

Please tell us if you encounter any problems with this.

As for the error you are getting, LinqConnect does not support user-defined types, including table types.

Also, you can use the Oracle Object wizard to create a class representing the department_type custom type (so that you don't need to use strings to access object's fields):
http://www.devart.com/dotconnect/oracle ... izard.html