Home » Eclipse Projects » EclipseLink » NamedPLSQLStoredProcedureQuery with multiple out parameters(how to get multiple out parameters, using annotation NamedPLSQLStoredProcedureQuery and Query.)
 NamedPLSQLStoredProcedureQuery with multiple out parameters [message #1187464] |
Fri, 15 November 2013 05:51  |
 Mindaugas Urbontaitis
Messages: 2
Registered: November 2013 |
Junior Member |
|
|
EclipseLink version: 2.5.1.
I followed PLSQLStoredFunction example, where is one OUT parameter. I created in database record types and theirs object mirrors, like in example. Created in java class with named plsql stored procedure query with one IN and OUT parameter, which worked:
@NamedPLSQLStoredProcedureQuery(
name = "getNamedProcedureTmp",
procedureName = "PS_PACKAGE$.SEL_TMP",
parameters = {
@PLSQLParameter(name = "P_VAL", queryParameter = "P_VAL", databaseType = "PS_PACKAGE$.PS_P_VAL", direction = Direction.IN),
@PLSQLParameter(name = "P_RESULT", queryParameter = "P_RESULT", databaseType = "SYS_REFCURSOR", direction = Direction.OUT_CURSOR)
})
Query query = entityManager.createNamedQuery("getNamedProcedureTmp");
query.setParameter("P_VAL", valueType);
query.setParameter("P_OPTIONS", optionType);
query.getResultList();
And my question is, how i can get working with multiple OUT parameters, like this named query?
@NamedPLSQLStoredProcedureQuery(
name = "getNamedProcedureTmp",
procedureName = "PS_PACKAGE$.SEL_TMP",
parameters = {
@PLSQLParameter(name = "P_VAL", queryParameter = "P_VAL", databaseType = "PS_PACKAGE$.PS_P_VAL", direction = Direction.IN),
@PLSQLParameter(name = "P_OPTIONS", queryParameter = "P_OPTIONS", databaseType = "API_PACKAGE$.API_P_OPTIONS", direction = Direction.IN_OUT),
@PLSQLParameter(name = "P_ERRORS", queryParameter = "P_ERRORS", databaseType = "API_PACKAGE$.API_ERRORS", direction = Direction.OUT),
@PLSQLParameter(name = "P_RESULT", queryParameter = "P_RESULT", databaseType = "SYS_REFCURSOR", direction = Direction.OUT_CURSOR)
})
I tried using, but this not find named plsql parameters:
StoredProcedureQuery query = entityManager.createNamedStoredProcedureQuery("getNamedProcedureTmp");
query.setParameter("P_VAL", valueType);
query.setParameter("P_OPTIONS", optionType);
query.execute();
query.getOutputParameterValue("P_RESULT");
query.getOutputParameterValue("P_ERRORS");
java.lang.IllegalArgumentException: You have attempted to set a parameter value using a name of P_VAL that does not exist in the query string PS_PACKAGE$.SEL_TMP.
Founded one example, there using multiple out parameters, but there don't used named query and record types. URL: yenlo.nl/en/calling-oracle-stored-procedures-from-eclipselink-with-multiple-out-parameters/
Is it possible using the named PLSQ stored procedure (or function) query to get multiple OUT parameters? And how?
|
|
| |
| Re: NamedPLSQLStoredProcedureQuery with multiple out parameters [message #1521417 is a reply to message #1513571] |
Sun, 21 December 2014 20:20  |
Mindaugas Urbontaitis
Messages: 2
Registered: November 2013 |
Junior Member |
|
|
Hello, Craig Jones,
Yes, i did. i didn't used @NamedPLSQLStoredProcedureQuery annotation. I wrote my own dao class, where I used PLSQLStoredProcedureCall to generate PLSQL query.
Also I created my own class, which generated PLSQLrecord and PLSQLCollection objects. Here's an example:
public static PLSQLrecord generateType(Class<?> clazz) {
PLSQLrecord record = new PLSQLrecord();
PLSQLRecord r = getRecordAnnotation(clazz);
record.setCompatibleType(r.compatibleType());
record.setTypeName(r.name());
record.setJavaType(r.javaType());
for (PLSQLParameter parameter : r.fields()) {
record.addField(parameter.name(), getJDBCType(parameter.databaseType()));
}
return record;
}
public static PLSQLCollection generatedCollection(Class<? extends DomainType> clazz) {
PLSQLCollection collection = new PLSQLCollection();
collection.setTypeName(clazz.getAnnotation(PLSQLTable.class).name() + "." + clazz.getAnnotation(PLSQLTable.class).compatibleType());
collection.setCompatibleType(clazz.getAnnotation(PLSQLTable.class).compatibleType());
collection.setJavaType(ArrayList.class);
collection.setNestedType(generateType(clazz));
return collection;
}
Also, a parameter clazz, must have @PLSQLRecord and @PLSQLParameter annotations. Here's an example:
@PLSQLTable(name = "TEST_PROCEDURE$", compatibleType = "OBJ")
@PLSQLRecord(name = "TEST$.OBJ", compatibleType = "OBJ", javaType = TestObject.class, fields = {
@PLSQLParameter(name = "ID", databaseType = "NUMERIC_TYPE"),
@PLSQLParameter(name = "NAME"), })
public class TestObject extends DomainType {
@Column(name = "ID")
private Long id;
@Column(name = "NAME")
private String name;
}
And it is how a decriptor looked:
public static ObjectRelationalDataTypeDescriptor generateDescriptor(Class<?> clazz) {
ObjectRelationalDataTypeDescriptor descriptor = new ObjectRelationalDataTypeDescriptor();
PLSQLRecord r = getRecordAnnotation(clazz);
descriptor.setJavaClass(r.javaType());
descriptor.setTableName(r.name());
descriptor.setStructureName(r.compatibleType());
PLSQLParameter[] parameters = r.fields();
// setting first element as primery key
if (parameters != null && parameters.length > 0) {
descriptor.addPrimaryKeyFieldName(parameters[0].name());
}
for (Field field : clazz.getDeclaredFields()) {
if (field.isAnnotationPresent(Column.class)) {
Column column = field.getAnnotation(Column.class);
descriptor.addDirectMapping(field.getName(), column.name());
}
}
for (PLSQLParameter parameter : parameters) {
descriptor.addFieldOrdering(parameter.name());
}
return descriptor;
}
And finally you can call a stored procedure:
PLSQLStoredProcedureCall plsql = new PLSQLStoredProcedureCall();
plsql.addNamedArgument("P_VAL", MY_OWN_CLASS.generateType(clazz));
plsql.addNamedInOutputArgument("P_OPTIONS", MY_OWN_CLASS.generateType(POptions.class));
plsql.addNamedOutputArgument("P_ERRORS", MY_OWN_CLASS.generatedCollection(Error.class));
plsql.setProcedureName(clazz.getAnnotation(PLSQLTable.class).name());
addDescriptors(clazz, POptions.class, Error.class);
Query query = ((JpaEntityManager) entityManager.getDelegate()).createQuery(new DataReadQuery(plsql));
query.setParameter("P_VAL", pVal);
query.setParameter("P_OPTIONS", pOptions);
DatabaseRecord result = (DatabaseRecord) query.getSingleResult();
// from result, you can get all out parameters.
Exists one problem with descriptor. You need the same parameters position order in Java and DB, otherwise it would fail.
So, it was the best solution at that moment, then i tried to solve this problem.
[Updated on: Sun, 21 December 2014 20:22] Report message to a moderator |
|
|
Goto Forum:
Current Time: Fri Sep 20 03:08:45 GMT 2024
Powered by FUDForum. Page generated in 0.03717 seconds |
] 
Search
Help
Register
Login
Home
