| call plsqlstoredfunctioncall with nested table type [message #1220252] |
Tue, 10 December 2013 16:19 |
 Issam Bo
Messages: 1
Registered: December 2013 |
Junior Member |
|
|
i want to call the plsql function:
function fn_validate_add_pos_profile(p_pos_pk in number, p_profile_pk in number,
p_valid_from in date, p_valid_to in date) return t_num_num_str ;
********************************************************************************************************
create or replace
type t_num_tab is table of number ;
************************************************************************************************************
create or replace
type t_num_num_str_tab as table of t_num_num_str ;
*************************************************************************************************
@Embeddable
@PLSQLTable(name = "T_NUM_TAB" ,compatibleType = "T_NUM_TAB",nestedType = "NUMBER",javaType = Long.class)
public class NumTypeTable extends ArrayList {
private static final long serialVersionUID = 1L;
}
*************************************************************************************************************
@Embeddable
@Struct(name ="t_num_num_str_tab",fields = {"num1","num2","str"})
public class NumNumStrType extends ComplexDatabaseType {
@Column(name = "num1",length = 10)
private Long num1;
@Column(name = "num2",length = 10)
private Long num2;
@Column(name = "str",length=255)
private String str;
.............
}
******************************************************************************************************
public List<Object> findPointOfSaleConflictList(List<Long> posIds,Long profileId, Date date){
NumNumStrType numNumStrType= new NumNumStrType();
numNumStrType.setCompatibleType("t_num_num_str_tab");
numNumStrType.setJavaType(NumNumStrType.class);
numNumStrType.setTypeName("t_num_num_str_tab");
PLSQLStoredFunctionCall function = new PLSQLStoredFunctionCall(numNumStrType);
function.setProcedureName("fn_validate_add_pos_profile");
PLSQLCollection plsqlCollection = new PLSQLCollection();
plsqlCollection.setCompatibleType("T_NUM_TAB");
plsqlCollection.setJavaType(NumTypeTable.class);
plsqlCollection.setTypeName("T_NUM_TAB");
plsqlCollection.setNestedType(JDBCTypes.NUMERIC_TYPE);
function.addNamedArgument("p_pos_pk",plsqlCollection);
function.addNamedArgument("p_profile_pk", JDBCTypes.NUMERIC_TYPE);
function.addNamedArgument("p_valid_from", JDBCTypes.DATE_TYPE );
function.addNamedArgument("p_valid_to", JDBCTypes.DATE_TYPE);
DataReadQuery readQuery=new DataReadQuery();
readQuery.addArgument("p_pos_pk",ArrayList.class);
readQuery.addArgument("p_profile_pk",Long.class);
readQuery.addArgument("p_valid_from", Date.class);
readQuery.addArgument("p_valid_to", Date.class);
readQuery.setCall(function);
readQuery.bindAllParameters();
Vector args=new Vector();
NumTypeTable myTableType=new NumTypeTable();
myTableType.add(1);
myTableType.add(2);
args.add(myTableType);
args.add(profileId);
args.add(new Date());
args.add(DateUtils.addYears(new Date(),99));
Object result= ((JpaEntityManager)getEntityManager()).getActiveSession().executeQuery(readQuery,args);
return null;
}
********************************************************************************************************
Caused by: javax.faces.el.EvaluationException: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.5.0.v20130507-3faac2b): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: java.sql.SQLException: Ungültiger Spaltentyp
Error Code: 17004
Call:
DECLARE
p_pos_pkTARGET T_NUM_TAB;
p_pos_pkCOMPAT T_NUM_TAB := :1;
p_profile_pkTARGET NUMERIC := :2;
p_valid_fromTARGET DATE := :3;
p_valid_toTARGET DATE := :4;
RESULTTARGET t_num_num_str_tab;
null FUNCTION EL_SQL2PL_3(aSqlItem T_NUM_TAB)
RETURN T_NUM_TAB IS
aPlsqlItem T_NUM_TAB;
BEGIN
IF aSqlItem.COUNT > 0 THEN
FOR I IN 1..aSqlItem.COUNT LOOP
aPlsqlItem(I) := aSqlItem(I);
END LOOP;
END IF;
RETURN aPlsqlItem;
END EL_SQL2PL_3;
BEGIN
p_pos_pkTARGET := EL_SQL2PL_3(p_pos_pkCOMPAT);
RESULTTARGET := fn_validate_add_pos_profile(p_pos_pk=>p_pos_pkTARGET, p_profile_pk=>p_profile_pkTARGET, p_valid_from=>p_valid_fromTARGET, p_valid_to=>p_valid_toTARGET);
:5 := EL_PL2SQL_0(RESULTTARGET);
END;
bind => [:1 => [1, 2], :2 => 3, :3 => 2013-12-10 17:06:00.121, :4 => 2112-12-10 17:06:00.121, RESULT => :5]
Query: DataReadQuery()
|
|
|
Powered by
FUDForum. Page generated in 0.08169 seconds
] 
Search
Help
Register
Login
Home
