Sending list of POCO objects to Oracle Stored Procedure

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
inspectorDev
Posts: 19
Joined: Mon 04 Jan 2016 15:04

Sending list of POCO objects to Oracle Stored Procedure

Post by inspectorDev » Wed 13 Apr 2016 21:40

I would like to know how it's possible to send a list of a complex type as an input parameter to an Oracle Stored Procedure.. eg.

Code: Select all

var employees = new List<Employee>();
employees.add (new Employee {id=1, name="joe", HireDate=xxx});
employees.add (new Employee {id=2, name="mike", HireDate=xxx});
employees.add (new Employee {id=3, name="scott", HireDate=xxx});
Now I would like to send this list as a parameter on a command object:

Code: Select all

OracleParameter op = new OracleParameter("InputParamNameInProc", OracleDbType.Table);
op.Direction = System.Data.ParameterDirection.Input;
OracleTable table = new OracleTable(OracleType.GetObjectType("tableTypeName", _connection));

table.Add(employees);
op.Value = table;
cmd.Parameters.Add(op);
cmd.ExecuteNonQuery();
My question is, am I on the right track here? Also, how would I create the type ("tableTypeName") in pl/sql, so I can do an insert statement for each employee? That's my end goal - to execute all employees in the employees table. I tried doing a single insert per employee from .NET, using mutiple command parameters, but the performance was really bad as there are thousands of employees. I was told trying something like this would significantly improve performance.

Thanks for any guidance. This forum has been very helpful! :D

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

Re: Sending list of POCO objects to Oracle Stored Procedure

Post by Pinturiccio » Thu 14 Apr 2016 13:42

inspectorDev wrote:so I can do an insert statement for each employee? That's my end goal - to execute all employees in the employees table.
If you want to quickly insert lots of data to an Oracle table, you can use OracleLoader. For example, you have a table using a user defined type:

Code: Select all

CREATE TYPE Employee AS OBJECT (
  id NUMBER,
  name VARCHAR2(30),
  HireDate Date
);

CREATE TABLE EMPLOYEE_TABLE (
  ID  NUMBER(10, 0),
  VAL EMPLOYEE
)
You can execute the following code:

Code: Select all

OracleConnection conn = new OracleConnection("host=orcl1120;uid=semenb;pwd=tiger;");
conn.Open();

OracleType employeeType = OracleType.GetObjectType("EMPLOYEE", conn);
List<OracleObject> employees = new List<OracleObject>();
            
OracleObject employee = new OracleObject(employeeType);
employee["id"] = 1;
employee["name"] = "joe";
employee["HireDate"] = DateTime.Today;
employees.Add(employee);

employee = new OracleObject(employeeType);
employee["id"] = 2;
employee["name"] = "mike";
employee["HireDate"] = DateTime.Today;
employees.Add(employee);

employee = new OracleObject(employeeType);
employee["id"] = 3;
employee["name"] = "scott";
employee["HireDate"] = DateTime.Today;
employees.Add(employee);

OracleLoader loader = new OracleLoader();
loader.Connection = conn;
loader.TableName = "EMPLOYEE_TABLE";
loader.CreateColumns();
loader.Open();

for (int i = 0; i < employees.Count; i++)
{
    loader.SetValue("id", i);
    loader.SetValue("val", employees[i]);
    loader.NextRow();
}
loader.Close();
For more information, please refer to
https://www.devart.com/dotconnect/oracl ... oader.html
https://www.devart.com/dotconnect/oracl ... jects.html

If your scenario is different, please describe it in more details. Please also provide the DDL scripts of the user defined type and table.

inspectorDev
Posts: 19
Joined: Mon 04 Jan 2016 15:04

Re: Sending list of POCO objects to Oracle Stored Procedure

Post by inspectorDev » Thu 14 Apr 2016 14:52

--deleted by user --
Last edited by inspectorDev on Fri 15 Apr 2016 19:00, edited 1 time in total.

inspectorDev
Posts: 19
Joined: Mon 04 Jan 2016 15:04

Re: Sending list of POCO objects to Oracle Stored Procedure

Post by inspectorDev » Thu 14 Apr 2016 16:01

--deleted--
Last edited by inspectorDev on Thu 14 Apr 2016 19:54, edited 1 time in total.

inspectorDev
Posts: 19
Joined: Mon 04 Jan 2016 15:04

Re: Sending list of POCO objects to Oracle Stored Procedure

Post by inspectorDev » Thu 14 Apr 2016 19:17

--deleted--

inspectorDev
Posts: 19
Joined: Mon 04 Jan 2016 15:04

Re: Sending list of POCO objects to Oracle Stored Procedure

Post by inspectorDev » Thu 14 Apr 2016 20:14

I've done this, which was 1000x faster, but it resulted in a problem on my table (see below):

Code: Select all

                    OracleLoader loader = new OracleLoader();
                    loader.Connection = _connection;
                    loader.TableName = "Employees";
                    loader.CreateColumns();
                    loader.Open();

                    for (int i = 0; i < employees.Count; i++)
                    {
                        loader.SetValue("Id", employees[i].Id);
                        loader.SetValue("Name", employees[i].Name);
                        loader.SetValue("HireDate", employees[i].HireDate);

                        loader.NextRow();
                    }

                    loader.Close();
I was so happy, but then I noticed this in the database when I tried to delete data from this table:
Error starting at line : 1 in command -
delete FROM EMPLOYEE
Error report -
SQL Error: ORA-01502: index 'MySchema.EMPLOYEE_PK' or partition of such index is in unusable state
01502. 00000 - "index '%s.%s' or partition of such index is in unusable state"
*Cause: An attempt has been made to access an index or index partition
that has been marked unusable by a direct load or by a DDL
operation
*Action: DROP the specified index, or REBUILD the specified index, or
REBUILD the unusable index partition
committed.
EDIT- this was fixed by using Direct Mode on my connection (Direct=true)

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: Sending list of POCO objects to Oracle Stored Procedure

Post by Shalex » Mon 18 Apr 2016 14:34

Refer to https://www.devart.com/dotconnect/oracl ... oader.html:
In OCI mode OracleLoader uses direct path load interface to speed up loading. In Direct mode it uses array binding feature. [...]
  • UNIQUE constraints are verified when indexes are rebuilt on OracleLoader.Close(). If UNIQUE constraint is violated, the index state becomes Unusable
  • The PRIMARY KEY constraint means that column is UNIQUE and NOT NULL simultaneously.
Please make sure that data inserted with OracleLoader do not violate existing constraints. If this is not the case, send us a small test project with the corresponding DDL script of the Employees table for reproducing.

Post Reply