Eclipse Community Forums: EclipseLink » Calling stored procedure in Oracle with complex object type parameters

Home » Eclipse Projects » EclipseLink » Calling stored procedure in Oracle with complex object type parameters(How to correctly annotate complex entity classes so that they map to the Object types in Oracle.)

Calling stored procedure in Oracle with complex object type parameters [message #1728338]

Sat, 02 April 2016 05:04

Eclipse User

Following the examples on EclipseLink JPA Extensions website I'd be able to call procedures with simple types as arguments. But I wasn't able to annotate my entity classes so that they would map correctly to the PL/SQL Object types in the database when the objects are a bit more complex.

For example we have a stored procedure that has one input and one output parameter of the same Object type A. The type structure is following:

Object type A contains <- Table type B which is table of <- Object type C which contains <- Table type D which is table of <- Object type E.

I figured I shall use the @OracleObject for the Object types and @PLSQLTable for the Table types but I haven't found a way how to correctly combine them together.

I've tried the following:

// All annotations with the required elements

@Embeddable
@Struct
@OracleObject
public class A {
    @Column(name = "B")
    private B b;

    // Getter and setter
}

@Embeddable
@Struct
@PLSQLTable (.., nestedType = "C")
public class B { }

@Embeddable
@Struct
@OracleObject
public class C {}

I've tried to add the @PLSQLTable with the proper annotation elements to class A and/or to class C but the Java and DB objects do not map to each other correctly and I'm getting the

java.sql.SQLException: Invalid column type

during the call.

I'd greatly appreciate if someone could explain to me how to use the annotations to tell the EclipseLink that Object A contains Table B of Object C.

Thank you very much.

Re: Calling stored procedure in Oracle with complex object type parameters [message #1744981 is a reply to message #1728338]

Mon, 03 October 2016 13:52

Eclipse User

Did you ever find out how? Do you have any example code you are willing to share please?

Re: Calling stored procedure in Oracle with complex object type parameters [message #1745231 is a reply to message #1744981]

Thu, 06 October 2016 13:58

Eclipse User

Unfortunately I have not found out how to do that using EclipseLink. We finally decided to use JDBC. I wrote a simple generator for type classes that implement SQLData interaface as described in JDBC Guide. We then use OracleCallableStatement together with the generated classes to call the stored procedure. For the collection type attributes you can use OracleArray within the Java class that implements SQLData.

Previous Topic:	Criteria Distinct on (column)
Next Topic:	Native Queries and ResultSetMapping - caching problem

Goto Forum:

-=] Back to Top [=-

Current Time: Sat Jul 19 16:40:02 EDT 2025

.:: Contact :: Home ::.

Breadcrumbs

Sign up to our Newsletter