passing complex java class to pl/sql function

Question

oracle developers created their API - PL/SQL function that takes RECORD as argument. The issue is that it consists of other records that reference table of records ..

create or replace TYPE QR_OS_REC AS OBJECT (
ICO VARCHAR2(8),
OS05 DATE,
OS09 QR_TAB,
CONSTRUCTOR FUNCTION QR_OS_REC RETURN SELF AS RESULT,
MEMBER FUNCTION validuj RETURN PLS_INTEGER
);

I need to call a function having such IN parameter from EJB. I read many tutorials but they all address beans having scalar properties. What shall I do with OS09 of QR_TAB type?

Thank you

Edit1:

I tried nested STRUCT with StructDescriptor, but it fails during second STRUCT initialization (sorry for different types than above sample)

StructDescriptor keywordStructDesc = StructDescriptor.createDescriptor("SCHEMA.KEYWORD_REC", conn);
Object[] keywordAttribs = new Object[1];
keywordAttribs[0] = record.getKeyword();

StructDescriptor osobaStructDesc = StructDescriptor.createDescriptor("SCHEMA.PERSON_REC", conn);
Object[] osobaAttribs = new Object[5];
osobaAttribs[0] = record.getName();
osobaAttribs[1] = record.getSurname();
osobaAttribs[2] = record.getAge();
osobaAttribs[3] = record.isVip();
osobaAttribs[4] = new STRUCT(keywordStructDesc, conn, keywordAttribs);
STRUCT struct = new STRUCT(osobaStructDesc, conn, osobaAttribs);

java.sql.SQLException: Inconsistent java and sql object types
    at oracle.sql.StructDescriptor.toOracleArray(StructDescriptor.java:711)
    at oracle.sql.StructDescriptor.toArray(StructDescriptor.java:1298)
    at oracle.sql.STRUCT.<init>(STRUCT.java:167)

EDIT 2:

I should have passed the content of the object instead the object itself.

>> keywordAttribs[0] = record.getKeyword();
<< keywordAttribs[0] = record.getKeyword().getValue();

It works now.

possible duplicate of pass object from Java to Oracle procedure — Bob Jarvis - Слава Україні
– Bob Jarvis - Слава Україні, Commented Aug 3, 2013 at 18:44

Leos Literak · Accepted Answer · 2013-08-18 08:47:10Z

1

Ok, the problem is solved. I described it with source code samples in my blog: http://www.literak.cz/2013/08/working-with-complex-database-types-in-weblogic/

answered Aug 18, 2013 at 8:47

Leos Literak

9,46022 gold badges97 silver badges174 bronze badges

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

Collectives™ on Stack Overflow

passing complex java class to pl/sql function

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related