EclipseLink: call oracle StoredFunc with IN table type

Question

I'm trying to call an oracle stored function using eclipselink 2.6.5, the storedfunction has an oracle type input.

This is te stored func:

create or replace function TEST_FUNC(PAR1 IN VARCHAR2, PAR2 IN MY_TYPE_T)
return varchar2 is 
 begin
  if PAR2 is null then
     return (PAR1 || ' 0');  
  else 
     return(PAR1 || ' ' || PAR2.count);    
  end if;
 end TEST_FUNC;

This is type declaration:

CREATE OR REPLACE MY_TYPE_T as table of MY_TYPE_R

CREATE OR REPLACE TYPE MY_TYPE_R as object
(
  COD_P VARCHAR2(3),
  COD_S VARCHAR2(10)
)

This is the type mapping:

@Embeddable
@Struct(name="MY_TYPE_R", fields= {"COD_P", "COD_S"})
@PLSQLTable(
    name="MY_TYPE_R",
    compatibleType="MY_TYPE_T",
    nestedType="MY_TYPE_R"  
)
public class MyRecordType {

  @Column(name="COD_P")
  private String codP;

  @Column(name="COD_S")
  private String codS;

  //costructors, getter and setter...
}

This is the stored mapping:

@NamedPLSQLStoredFunctionQuery(
    name="testFunc",
    functionName="TEST_FUNC",
    returnParameter=@PLSQLParameter(
            name="RESULT",
            databaseType = "VARCHAR_TYPE"),
    parameters = {
            @PLSQLParameter(name = "firstParam", queryParameter="PAR1", databaseType = "VARCHAR_TYPE"),
            @PLSQLParameter(name = "secondParam", queryParameter="PAR2", databaseType = "MY_TYPE_T"),
    }
)

This is the call:

@Test
public void testFuncTest() {    
  List<MyRecordType> recTypeList = new ArrayList<MyRecordType>();
  MyRecordType rec = new MyRecordType();
  rec.setCodP("PValue");
  rec.setCodS("SValue");
  recTypeList.add(rec);

  Query query = getEM().createNamedQuery("testFunc");
  query.setParameter("firstParam", "FOO"); 
  query.setParameter("secondParam", recTypeList); 

  Assert.assertEquals("FOO 1", query.getSingleResult());
}

When I execute the test I get: PLS-00306: wrong number or types of arguments in call to 'TEST_FUNC'

Actually reading the documentation on @PLSQTable I'm a bit confused on the tree parameters:

• name
• compatibleType
• nestedType

Anyone has some suggestion?

Answer 1

First you'll want to use the OracleObject annotation to define your fields into an Oracle object. It's somewhat duplicate to Struct but required anyways.

@OracleObject(
        name = "MY_TYPE_R"
        , javaType = MyRecordType.class
        , fields = {
        @PLSQLParameter(name = "COD_P")
        ,@PLSQLParameter(name = "COD_S")
        }
)

For the PLSQLTable you want

@NamedPLSQLStoredFunctionQuery(
    name="testFunc",
    functionName="TEST_FUNC",
    returnParameter=@PLSQLParameter(
            name="RESULT",
            databaseType = "VARCHAR_TYPE"),
    parameters = {
            @PLSQLParameter(name = "PAR1", queryParameter="PAR1", databaseType = "VARCHAR_TYPE"),
            @PLSQLParameter(name = "PAR2", queryParameter="PAR2", databaseType = "MY_TYPE_T"),
    }
)

so the name needs to match what's in your function. The PLSQL table piece looks right, I believe the OracleObject part was your missing component and causing your error.