How to call Oracle stored procedure with custom object as input parameter

Question

In Oracle database I have defined type:

create or replace TYPE person_type
    AS OBJECT (id NUMBER(10), name VARCHAR2(50), age NUMBER);

and a stored procedure

create or replace PROCEDURE add_person (in_person IN person_type)
AS
BEGIN
  INSERT into person (id, name, age) VALUES(in_person.id, in_person.name, person.age);
END;

I'm using Spring Boot with Hibernate and I want to call the procedure with some equivalent java bean as input parameter. I have seen many examples but they were using only basic types, not composed objects. There are also some examples with Table annotations, but I'm not guaranteed to have such table in database, I have only guaranteed the stored procedure type.

Read this..technology.amis.nl/2008/07/16/…

XING
– XING

2018-01-25 10:51:06 +00:00
Commented Jan 25, 2018 at 10:51 — XING
– XING, Commented Jan 25, 2018 at 10:51

Ravi · Accepted Answer · 2018-01-25 18:51:52Z

1

You need StructDescriptor to get you SQL structured object, which then need to pass to STRUCT to create type.

In StructDescriptor, you need to pass the SQL object name i.e. PERSON_TYPE and the connection object i.e. con.

StructDescriptor StructDesc = StructDescriptor.createDescriptor("PERSON_TYPE", con);

Then, pass the structure descriptor and object array to struct. Please, note, the array index represent its sequence/place, the way it (SQL object) was declared on SQL object. As id was the first, then name and then age.

Object[] ObjArray = new Object[3];
ObjArray[0] = 1;
ObjArray[1] = "My Name";
ObjArray[2] = 23;

STRUCT structObj = new STRUCT(StructDesc, con, ObjArray);

Now, using struct object, you call your procedure

stmt = (OracleCallableStatement) con.prepareCall("{call add_person(?)}");
stmt.setSTRUCT(1, structObj);
stmt.executeUpdate();

edited Jan 25, 2018 at 18:51

answered Jan 25, 2018 at 18:46

Ravi

31.6k44 gold badges124 silver badges180 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

212 Over a year ago

How did you get the connection "con"? Hibernate session won't allow access to it.

Collectives™ on Stack Overflow

How to call Oracle stored procedure with custom object as input parameter

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related