JPA + StoredProcedureCall + object type IN parameter

Question

I'm trying to do a simple thing: call stored procedure which have a object type parameter.

This is what I have in db:

create or replace
TYPE TEST_TYPE AS OBJECT 
(
  test_field varchar(100)
)

and

CREATE OR REPLACE PROCEDURE TEST_PROC 
(
  PARAM1 IN TEST_TYPE 
) AS 
BEGIN
END TEST_PROC;

This is what I have in my java code:

@Embeddable
@Struct(name = "TEST_TYPE", fields = {"TEST_FIELD"})
public class TestStruct
{

    private String testField;

    public String getTestField() {
        return testField;
    }

    public void setTestField(String testField) {
        this.testField = testField;
    }
}

and

    @PostConstruct
    public void init()
    {

        StoredProcedureCall call = new StoredProcedureCall();
        call.setProcedureName("TEST_PROC");
        call.addNamedArgument("PARAM1", "PARAM1", Types.STRUCT, "TEST_TYPE", TestStruct.class);

        DataReadQuery dataReadQuery = new DataReadQuery(call);
        dataReadQuery.addArgument("PARAM1");

        TestStruct testStruct = new TestStruct();
        List args = new ArrayList();
        args.add(testStruct);

        Object result = ((EntityManagerImpl)em.getDelegate()).getSession().executeQuery(dataReadQuery,args);
    }

this is what I get in runtime:

Internal Exception: java.sql.SQLException: Invalid column type
Error Code: 17004
Call: BEGIN TEST_PROC(PARAM1=>?); END;
    bind => [1 parameter bound]
Query: DataReadQuery()

I think I totally don't understand the subject of usage structs with JPA

please help me, good people :)

What is the shortest way to make this working?

score 1 · Answer 1 · answered Feb 04 '12 at 08:12

1

Please send complete your code. For call stored procedures using Spring, you have to extends StoredProcedure class. If you send your complete code, I can help better. sample pseudo code:

class CustomStoredProcedure extends org.springframework.jdbc.object.StoredProcedure
{
    CustomStoredProcedure()
    {
        super([your-data-source], [package-name]);  
        declareParameter(new SqlParameter([your-struct-name]), Types.STRUCT));
        compile();
    }

    Map<String, Object> execute([your-parameter])
    {
        return super.execute(inputs);
    }
}

for better help, you have explain complete situation.

answered Feb 04 '12 at 08:12

Sam

6,770
7
50
91

this is complete situation - I'm just testing how to solve common problems with EclipseLink JPA2.0 implementation. BTW AFAIK Spring does not implement JPA - am I wrong? – czajah Feb 04 '12 at 12:10
You say correct,Spring dosn't implement JPA. I don't use EclipseLink. My solution is independent of jpa implementation, it provided by spring framework and work correct. If you want using EclipseLink only, you have to read its documents. – Sam Feb 04 '12 at 12:51

score 0 · Answer 2 · answered Nov 27 '12 at 13:45

It seems that eclipselink skips descriptors for the @Struct and @Embeddable annotated classes unless they are referenced by some other class. The shortest way to make it working is to use workaround based on this assumption. Put additional class in the jar where your META-INF/persistence.xml is located:

@Entity
public class StructEntitiesWorkaround {

  @Id
  private String id;

  private TestStruct testStruct;

}

score 0 · Answer 3 · edited Mar 01 '19 at 18:04

0

You might want to use SimpleJdbcCall with Types.STRUCT.

Here is an example: https://docs.spring.io/spring-data/jdbc/old-docs/2.0.0.M1/reference/html/orcl.datatypes.html

edited Mar 01 '19 at 18:04

double-beep

5,031
17
33
41

answered Feb 28 '19 at 16:36

Ramneek Handa

53
1
10

score 0 · Answer 4 · answered Feb 06 '12 at 16:10

0

Your code looks correct.

Ensure that the descriptor was defined for the struct. (i.e. session.getDescrptor(TestStruct.class))

Can you call stored procedures with other types?

What database are you using, have you set your platform correctly to Oracle?

answered Feb 06 '12 at 16:10

James

17,965
11
91
146

"Ensure that the descriptor was defined for the struct" does it mean that descriptor will be created automatically due to annotations? Recently I'v tried to set them manualy:` ObjectRelationalDataTypeDescriptor d = new ObjectRelationalDataTypeDescriptor(); d.setJavaClass(TestStruct.class); d.descriptorIsAggregate(); d.setStructureName("TEST_TYPE"); d.addDirectMapping("TEST_FIELD", "getTestField", "setTestField", "testField"); d.setShouldOrderMappings(false); ((EntityManagerImpl)em.getDelegate()).getServerSession().addDescriptor(d); ` – czajah Feb 06 '12 at 21:21
I can call stored procedures with simple types like varchar. I am using Oracle 10 and proper jdbc drivers. – czajah Feb 06 '12 at 21:28
The descriptor should be create from the annotations, but if you did not specific the class in your persistence.xml or had other issues it may not, so checking it is there would be a good idea. Did it work when you defined it in code? – James Feb 07 '12 at 15:52
no, it did not. My colleague tell me that he found some bugs in EclipseLink libraries which we used – czajah Feb 13 '12 at 17:17

JPA + StoredProcedureCall + object type IN parameter

4 Answers4

Linked