Object Types in Oracle Stored Procedure.

Object Types in Oracle Stored Procedure.

Postby spinechiller » Sun 09 Nov 2014 00:12

Hi,
I am playing with HR database in Oracle and doing some POCs.I have a question regarding Calling a stored INSERT stored procedure with an Object Type. Following is structure of my stored procedure.

create or replace PROCEDURE ADD_EMPLOYEE_WITH_JBHIST
(
FIRST_NAME EMPLOYEES.FIRST_NAME%type
, SALARY EMPLOYEES.SALARY%type
, EMAIL EMPLOYEES.EMAIL%type
, COMMISSION_PCT EMPLOYEES.COMMISSION_PCT%type
, PHONE_NUMBER EMPLOYEES.PHONE_NUMBER%type
, DEPARTMENT_ID EMPLOYEES.DEPARTMENT_ID%type
, LAST_NAME EMPLOYEES.LAST_NAME%type
, MANAGER_ID EMPLOYEES.MANAGER_ID%type
, HIRE_DATE EMPLOYEES.HIRE_DATE%type
, JOB_ID EMPLOYEES.JOB_ID%type
, JOB_HIS IN EMP_JOB_HISTORY_OBJ
, EMP_ID OUT EMPLOYEES.EMPLOYEE_ID%type
)

How do i create and pass an object Type to my stored procedure using dotconnect for oracle. Here is the definition of my object type used above. This object type basically consist a list of dates that i need to insert.

create or replace type emp_job_history_obj as object
( /* TODO enter attribute and method declarations here */
Startdate date,
EndDate date
);

Any help with some sample code provided is highly appreciated.

Thanks in advance.
spinechiller
 
Posts: 2
Joined: Sun 09 Nov 2014 00:04

Re: Object Types in Oracle Stored Procedure.

Postby Pinturiccio » Mon 10 Nov 2014 16:05

You need to set the OracleDbType property to Object in order to pass a value of emp_job_history_obj type. You need also to create an object of OracleObject type and assign it to the Value property. Here is an example of using a stored procedure with the JOB_HIS parameter:
Code: Select all
OracleConnection conn = new OracleConnection("your connection string");
conn.Open();
OracleCommand comm = new OracleCommand("ADD_EMPLOYEE_WITH_JBHIST", conn);
comm.CommandType = System.Data.CommandType.StoredProcedure;
OracleObject obj = new OracleObject("EMP_JOB_HISTORY_OBJ", conn);
obj["Startdate"] = DateTime.Now;
obj["EndDate"] = DateTime.Now;
comm.Parameters.Add("JOB_HIS", OracleDbType.Object).Value = obj;
// Add other stored procedure parameters here
comm.ExecuteNonQuery();


For more information, please refer to http://www.devart.com/dotconnect/oracle/docs/?Devart.Data.Oracle~Devart.Data.Oracle.OracleObject.html
Pinturiccio
Devart Team
 
Posts: 1928
Joined: Wed 02 Nov 2011 09:44

Re: Object Types in Oracle Stored Procedure.

Postby spinechiller » Thu 13 Nov 2014 04:11

Thank you for the response.

But with the current approach i can only send one object at a time to the procedure. However i need to send a list of objects containing those fields not just one object.

Any idea how could i proceed with that .

Thanks again for the response.
spinechiller
 
Posts: 2
Joined: Sun 09 Nov 2014 00:04

Re: Object Types in Oracle Stored Procedure.

Postby Pinturiccio » Fri 14 Nov 2014 15:34

You can use the array binding feature for sending a list of objects to stored procedure. For more information, please refer to http://www.devart.com/dotconnect/oracle/docs/?ArrayBind.html

Here is the previous example, modified for using the array binding feature:
Code: Select all
OracleConnection conn = new OracleConnection("your connection string");
conn.Open();
OracleCommand comm = new OracleCommand("ADD_EMPLOYEE_WITH_JBHIST", conn);
comm.CommandType = System.Data.CommandType.StoredProcedure;
           
OracleObject obj1 = new OracleObject("EMP_JOB_HISTORY_OBJ", conn);
obj1["Startdate"] = DateTime.Now;
obj1["EndDate"] = DateTime.Now;

OracleObject obj2 = new OracleObject("EMP_JOB_HISTORY_OBJ", conn);
obj2["Startdate"] = DateTime.Today;
obj2["EndDate"] = DateTime.Today;

object[] arr = new object[2];
arr[0] = obj1;
arr[1] = obj2;
comm.Parameters.Add("JOB_HIS", OracleDbType.Object).Value = arr;
// Add other stored procedure parameters here

comm.ExecuteArray(2);
Pinturiccio
Devart Team
 
Posts: 1928
Joined: Wed 02 Nov 2011 09:44


Return to dotConnect for Oracle