Oracle Stored Procedure for passing user defined class objects

Question

I need to pass class object which in turn has list of items in them to oracle db. Can you please let me know how to create an oracle stored procedure where i can pass all the item values at the same time.

Eg:- I can have an class object "Customer" where the values are customer details and the Items he has purchased. So i have an object and within the object a list of items. What would be the best way to execute these details via Oracle stored procedure.

CustomerName , ID --->Item Purchased 1 details --> Item Purchased 2 details : : ---> Item Purchased 50 details

score 2 · Accepted Answer · edited May 23 '17 at 11:58

For your requirement, you need to create Object (Type in Oracle), you can also create array of that object and use it as a parameter to pass the values into your stored procedure.

CREATE TYPE customer_type AS OBJECT ( 
   customer_name          VARCHAR2(30),
   item_purchased         VARCHAR2(20),
   .
   .
   )
/

CREATE TYPE customer_arr as TABLE OF customer_type INDEX BY PLS_INTEGER;

Create a variable of this object type in the calling program, fill its values and pass it to the program to be called.

Refer this link for more details.

Oracle Stored Procedure for passing user defined class objects

1 Answers1