Object Types in Oracle Stored Procedure.

[spinechiller]

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.

[Pinturiccio]

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 ... bject.html

[spinechiller]

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.

[Pinturiccio]

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

[tale07]

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 ... bject.html

Maybe you can help me,
I have same situation, but the problem is with not nullable properties.
Example:
ADD_EMPLOYEE_WITH_JBHIST
has ProviderId integer property

obj["ProviderId"] = 13;
and after this if I do this

and when I send obj as parameter, this 13 value for ProviderId is not passed.

Is there any tool to generate c# class from DB oracle types?

[Pinturiccio]

We have answered you via e-mail.