Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » NamedPLSQLStoredProcedureQuery with multiple out parameters(how to get multiple out parameters, using annotation NamedPLSQLStoredProcedureQuery and Query.)
icon5.gif  NamedPLSQLStoredProcedureQuery with multiple out parameters [message #1187464] Fri, 15 November 2013 05:51 Go to next message
Mindaugas Urbontaitis is currently offline Mindaugas UrbontaitisFriend
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 #1513571 is a reply to message #1187464] Tue, 16 December 2014 17:02 Go to previous messageGo to next message
Craig Jones is currently offline Craig JonesFriend
Messages: 5
Registered: December 2014
Junior Member
Hi,

Did you ever manage to solve this? I'm having similar issues :/
Re: NamedPLSQLStoredProcedureQuery with multiple out parameters [message #1521417 is a reply to message #1513571] Sun, 21 December 2014 20:20 Go to previous message
Mindaugas Urbontaitis is currently offline Mindaugas UrbontaitisFriend
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

Previous Topic:Static Weaving failed with NPE
Next Topic:Default value for @TransactionAttribute.
Goto Forum:
  


Current Time: Wed Jul 29 09:41:32 GMT 2015

Powered by FUDForum. Page generated in 0.01708 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software