Databases
StackOverflow2013
Postsdbo
POError while calling a PLSQL stored function having a PLSQLTable type as one of its parameter
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POError while calling a PLSQL stored function having a PLSQLTable type as one of its parameter
    primarykey
    data
    text
    <p>I want to call this function</p> <pre><code> function fn_validate_add_pos_profile(p_pos_pk in t_num_tab, p_profile_pk in number,p_valid_from in date, p_valid_to in date) return t_num_num_str; </code></pre> <p>Here are the definition of used type </p> <pre><code> 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; } **************************************************************************************************** </code></pre> <p>Here is the map class to t_num_num_str_tab type</p> <hr> <pre><code> @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;......} ***************************************************************************************************** </code></pre> <p>the method call of the plsqlstoredfunctioncall </p> <hr> <pre><code> public List&lt;Object&gt; findPointOfSaleConflictList(List&lt;Long&gt; 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; } ***************************************************************************************************** </code></pre> <p>Error Message after calling the method above</p> <hr> <pre><code> 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 &gt; 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=&gt;p_pos_pkTARGET, p_profile_pk=&gt;p_profile_pkTARGET, p_valid_from=&gt;p_valid_fromTARGET, p_valid_to=&gt;p_valid_toTARGET); :5 := EL_PL2SQL_0(RESULTTARGET); END; bind =&gt; [:1 =&gt; [1, 2], :2 =&gt; 3, :3 =&gt; 2013-12-10 17:06:00.121, :4 =&gt; 2112-12-10 17:06:00.121, RESULT =&gt; :5] Query: DataReadQuery() </code></pre>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USuser3090450
    1. USuser3090450
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. POError while calling a PLSQL stored function having a PLSQLTable type as one of its parameter
      1. This table or related slice is empty.
      1. VTApproveEditSuggestion
    1. CODid you connect using the owner schema or not and which DB you are using?
      singulars
      1. POError while calling a PLSQL stored function having a PLSQLTable type as one of its parameter
      1. USMina Tadros
    2. COwe use oracle DB and eclipselink for ORM mapping.
      singulars
      1. POError while calling a PLSQL stored function having a PLSQLTable type as one of its parameter
      1. USuser3090450
    3. CODid u comment to this database with the owner schema or another schema has privilege on the owner schema?
      singulars
      1. POError while calling a PLSQL stored function having a PLSQLTable type as one of its parameter
      1. USMina Tadros
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload