CODEWITHSUNDEEP
javaoracle-databaseobjectstored-procedures
Iwaneez
Iwaneez

Reputation: 87

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

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.

Upvotes: 2

Views: 4739

Answers (1)

Ravi
Ravi

Reputation: 31417

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();

Upvotes: 1

Related Questions