Devart Forums

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;
/

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