struggling to call a stored procedure with OBJECT parameter... [message #649070] |
Mon, 17 January 2011 13:57 |
Keiran Halcyon Messages: 12 Registered: January 2011 |
Junior Member |
|
|
I have this simple OBJECT on Oracle DB:
TYPE O_VDS_ATTRIBUTE AS OBJECT
(
name VARCHAR2(100),
value VARCHAR2(4000)
)
And this test PROCEDURE:
CREATE OR REPLACE PROCEDURE TEST_PROC(attrib O_VDS_ATTRIBUTE) IS
BEGIN
NULL;
END;
I've defined this Java mapping class:
public class Attribute {
private String name;
private String value;
public Attribute() {
}
public Attribute(String name, String value) {
this.name = name;
this.value = value;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getValue() {
return value;
}
public void setValue(String value) {
this.value = value;
}
}
I'm registering the O_VDS_ATTRIBUTE related descriptor on session:
ServerSession session = ((JpaEntityManager)em.getDelegate()).getServerSession();
ObjectRelationalDataTypeDescriptor descriptor
= new ObjectRelationalDataTypeDescriptor();
descriptor.setJavaClass(Attribute.class);
descriptor.setTableName("VDS.O_VDS_ATTRIBUTE");
descriptor.setStructureName("VDS.O_VDS_ATTRIBUTE");
descriptor.addDirectMapping("name", "getName", "setName", "NAME");
descriptor.addDirectMapping("value", "getValue", "setValue", "VALUE");
descriptor.addPrimaryKeyFieldName("NAME");
session.addDescriptor(descriptor);
Then I'm preparing and executing the PLSQLStoredProcedureCall:
PLSQLrecord record = new PLSQLrecord();
record.setCompatibleType("VDS.O_VDS_ATTRIBUTE");
record.setTypeName("VDS.O_VDS_ATTRIBUTE");
record.setJavaType(Attribute.class);
record.addField("NAME", JDBCTypes.VARCHAR_TYPE, 100);
record.addField("VALUE", JDBCTypes.VARCHAR_TYPE, 4000);
Attribute attribute = new Attribute("nejm", "val");
PLSQLStoredProcedureCall call = new PLSQLStoredProcedureCall();
call.setProcedureName("VUM.TEST_PROC");
call.addNamedArgument("attrib", record);
DataModifyQuery query = new DataModifyQuery();
query.addArgument("attrib", Struct.class);
query.setCall(call);
List<Object> queryArgs = new ArrayList<Object>();
queryArgs.add(attribute);
session.executeQuery(query, queryArgs);
The code actually succesfully sends some SQL to the DB but the compatibility type objects there are not initialized and it's execution throws ORA-06530:
DECLARE
attrib_TARGET VDS.O_VDS_ATTRIBUTE;
attrib_COMPAT VDS.O_VDS_ATTRIBUTE := :1;
FUNCTION EL_SQL2PL_1(aSqlItem VDS.O_VDS_ATTRIBUTE)
RETURN VDS.O_VDS_ATTRIBUTE IS
aPlsqlItem VDS.O_VDS_ATTRIBUTE;
BEGIN
aPlsqlItem.NAME := aSqlItem.NAME;
aPlsqlItem.VALUE := aSqlItem.VALUE;
RETURN aPlsqlItem;
END EL_SQL2PL_1;
BEGIN
attrib_TARGET := EL_SQL2PL_1(attrib_COMPAT);
VUM.TEST_PROC(attrib=>attrib_TARGET);
END;
Also, I tried to make this work without the compatibleType but throwing the "record.setCompatibleType("VDS.O_VDS_ATTRIBUTE");" line out is provoking a totally different error (nothing gets to the database):
java.lang.NullPointerException
at org.eclipse.persistence.internal.helper.DatabaseType$DatabaseTypeHelper.translate(DatabaseType.java:137)
at org.eclipse.persistence.platform.database.oracle.plsql.PLSQLrecord.translate(PLSQLrecord.java:200)
at org.eclipse.persistence.platform.database.oracle.plsql.PLSQLStoredProcedureCall.translate(PLSQLStoredProcedureCall.java:1012)
at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:205)
at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:192)
at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeNoSelectCall(DatasourceCallQueryMechanism.java:235)
at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeNoSelect(DatasourceCallQueryMechanism.java:215)
at org.eclipse.persistence.queries.DataModifyQuery.executeDatabaseQuery(DataModifyQuery.java:85)
at org.eclipse.persistence.queries.DatabaseQuery.execute(DatabaseQuery.java:675)
at org.eclipse.persistence.internal.sessions.AbstractSession.internalExecuteQuery(AbstractSession.java:2322)
at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1225)
at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1207)
at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1181)
at com.visiona.jpa.eclipselink.OracleSpecificDbJPATest.callProcedureWithInAttributeObjectArgument(OracleSpecificDbJPATest.java:61)
Can somebody help me? I''ve spent so much time trying to get this to work! I searched everywhere and those features are so poorly documented. I saw some examples with RECORDs but those work a bit different than OBJECTs (no need for explicit initialization).
|
|
|
|
|
Re: struggling to call a stored procedure with OBJECT parameter... [message #649554 is a reply to message #649215] |
Wed, 19 January 2011 17:04 |
Chris Delahunt Messages: 1389 Registered: July 2009 |
Senior Member |
|
|
Hello,
Creating an ObjectRelationalDataTypeDescriptor is described here:
http://wiki.eclipse.org/Creating_an_Object-Relational_Data_T ype_Descriptor_(ELUG)
The difference between what it is doing and what you are attempting is that it is setting up the Employee to be written to a table, not through stored procedures- your does not have a "VDS.O_VDS_ATTRIBUTE" table, and so the setTableName should probably not be used. While it might not be related, you might also want to add your descriptor in a customizer rather than dynamically.
Simple example from the EclipseLink tests is:
Descriptor:
ObjectRelationalDataTypeDescriptor descriptor = new ObjectRelationalDataTypeDescriptor();
descriptor.setJavaClass(Address.class);
descriptor.descriptorIsAggregate();
descriptor.setStructureName("ADDRESS_TYPE");
descriptor.addDirectMapping("street", "getStreet", "setStreet", "STREET");
descriptor.addDirectMapping("city", "getCity", "setCity", "CITY");
descriptor.setShouldOrderMappings(false);
call, query and execution:
StoredProcedureCall spcall = new StoredProcedureCall();
spcall.setProcedureName("SProc_Insert");
//address arg type is only needed should this query be used with a null value
spcall.addUnamedArgument("address", Types.STRUCT, "ADDRESS_TYPE");
DataModifyQuery query = new DataModifyQuery();
query.setShouldBindAllParameters(true);
query.bindAllParameters();
query.setCall(spcall);
query.addArgument("address");
List<Object> queryArgs = new ArrayList<Object>();
queryArgs.add(address);
session.executeQuery(query, queryArgs);
This example runs nightly. One difference other than the use of unnamed parameters is that the type is not specified on the query.addArgument call. It is only required for differentiating cached queries if the argument types are the same and should not be needed here - specifying STRUCT might be wrong, i just haven't stepped through this code to verify.
Best Regards,
Chris
|
|
|
|
|
Re: struggling to call a stored procedure with OBJECT parameter... [message #649960 is a reply to message #649774] |
Fri, 21 January 2011 08:48 |
Keiran Halcyon Messages: 12 Registered: January 2011 |
Junior Member |
|
|
I did what Chris suggested so...
Now I'm adding the descriptor in customizer:
public class DBObjectsDescriptorCustomizer implements SessionCustomizer {
public void customize(Session session) {
Server server = (Server) session;
ObjectRelationalDataTypeDescriptor descriptor = new ObjectRelationalDataTypeDescriptor();
descriptor.setJavaClass(Attribute.class);
descriptor.descriptorIsAggregate();
descriptor.setStructureName("VDS.O_VDS_ATTRIBUTE");
descriptor.addDirectMapping("name", "getName", "setName", "NAME");
descriptor.addDirectMapping("value", "getValue", "setValue", "VALUE");
descriptor.setShouldOrderMappings(false);
server.addDescriptor(descriptor);
}
}
The descriptor is of course registered in persistence.xml (eclipselink.session.customizer). And this is the code which should use the descriptor:
session = ((JpaEntityManager)em.getDelegate()).getServerSession();
Attribute attribute = new Attribute("nejm", "val");
StoredProcedureCall call = new StoredProcedureCall();
call.setProcedureName("VUM.TEST_PROC");
call.addUnamedArgument("attrib", Types.STRUCT, "VDS.O_VDS_ATTRIBUTE");
DataModifyQuery query = new DataModifyQuery();
query.setShouldBindAllParameters(true);
query.bindAllParameters();
query.setCall(call);
query.addArgument("attrib");
List<Object> queryArgs = new ArrayList<Object>();
queryArgs.add(attribute);
session.executeQuery(query, queryArgs);
Just to be sure, this is the Attribute POJO:
public class Attribute {
public String name;
public String value;
public Attribute() {
}
public Attribute(String name, String value) {
this.name = name;
this.value = value;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getValue() {
return value;
}
public void setValue(String value) {
this.value = value;
}
@Override
public String toString() {
return Objects.toStringHelper(this)
.add("name", name)
.add("value", value)
.toString();
}
}
Effect:
BEGIN VUM.TEST_PROC(?); END;
bind => [null]
If you'd like any other information, let me know.
|
|
|
|
|
|
|
Powered by
FUDForum. Page generated in 0.04658 seconds