CODEWITHSUNDEEP
oracle-databasemybatis
theo
theo

Reputation: 181

List as parameter to stored procedure

Hei there, I'm working on a JSF/PF5/Mybatis/Oracle app.

I'm struggling to figure something out.

I'm trying to send a list of ints as parameters of a stored procedure.

I Get this error whilte running the test: 

Caused by: java.sql.SQLException: invalid name pattern: SSLS_WMS.INT_ARRAY

Can somebody help me with this?

(I already browsed a few questions like this )

I created a test package: 

CREATE OR REPLACE PACKAGE SSLS_WMS.pkg_test
as
C_PKG_NAME                    CONSTANT tracer.T_CALL_NAME :=     sys_context('USERENV','CURRENT_SCHEMA') || '.pkg_test.';
TYPE INT_ARRAY is table of SSLS_WMS.ORDERS.ORDER_ID%type;
procedure pMergeOrders(
    pMergeType      INTEGER,
    pToMerge     IN INT_ARRAY,
    pParentOrderId        orders.order_id%type,
    pParentOrderNo        orders.order_no%type,
    p_do_it            pls_integer default 1
);
end pkg_test;
/

CREATE OR REPLACE PACKAGE body SSLS_WMS.pkg_test
as
procedure pMergeOrders(
    pMergeType      INTEGER,
    pToMerge              INT_ARRAY,
    pParentOrderId        orders.order_id%type,
    pParentOrderNo        orders.order_no%type,
    p_do_it            pls_integer default 1
)
AS
v_prog_name         tracer.t_call_name := C_PKG_NAME||'pMergeOrders';
v_prog_params       tracer.t_params :=  'pParentOrderId='||pParentOrderId||
                                        'p_do_it='||p_do_it;
BEGIN
insert into ssls_wms.TEST_THEO2(ORDER_ID) values (pParentOrderId);
for currOrderId in (select column_value FROM TABLE(pToMerge))
loop
  insert into ssls_wms.TEST_THEO2(ORDER_ID) values (currOrderId.column_value);
end loop;
COMMIT;
end pMergeOrders;
end pkg_test;

Mapper update statement: 

    <update id="mergeOrders" parameterType="java.util.Map"
    statementType="CALLABLE">
    {CALL SSLS_WMS.PKG_TEST.PMERGEORDERS(
    #{p_mergeType,
    jdbcType=INTEGER},
    #{p_toMerge, javaType=java.util.List,
    jdbcType=ARRAY,
    jdbcTypeName=INT_ARRAY, mode=IN,
    typeHandler=com.mybatis.thandlers.IntArrayTypeHandler},
    #{p_parentOrdId,
    jdbcType=INTEGER},
    #{p_parentOrdNo,jdbcType=VARCHAR},
    #{p_do_it,
    jdbcType=VARCHAR}
    )
    }
</update>

This is what I called the procedure with:

  @Test
   public void _testOrderMerger() {
     OrderMergerService service = new OrderMergerService();
     List<Integer> orders = new ArrayList<Integer>();
     orders.add(13648);
     orders.add(136500);
     Map<String, Object> params = new HashMap<String, Object>();
     params.put("p_mergeType", 1);
     params.put("p_toMerge", orders);
     params.put("p_parentOrdId", 13652);
     params.put("p_do_it", 1);
     SQLResult result = service.merge(params);
     if (result.isOk()) {
       System.out.println("SUCCES");
     } else
       System.out.println("NOT GOOD");
   }

I'm pretty sure the problem is related to my type Handler implementation:

public class IntArrayTypeHandler implements TypeHandler<Object> {
  @SuppressWarnings("unchecked")
  @Override
  public void setParameter(PreparedStatement ps, int i, Object parameter, JdbcType jdbcType) throws SQLException {
    List<Integer> objects = (List<Integer>) parameter;

    ArrayDescriptor desc = ArrayDescriptor.createDescriptor("SSLS_WMS.PKG_TEST.INT_ARRAY", ps.getConnection());
    ARRAY oracleArray = new ARRAY(desc, ps.getConnection(), objects);
    ps.setArray(i, oracleArray);
  }
    ... etc

Upvotes: 0

Views: 1543

Answers (1)

Yigitalp Ertem
Yigitalp Ertem

Reputation: 2029

With JDBC, you are not allowed to reach the packaged types. This answer may be helpful.

You can define your TYPE in the schema level (by giving the column type since you cannot use %type here):

CREATE OR REPLACE TYPE TA_ORDER_ID AS TABLE OF NUMBER(15);

Then use it as,

ArrayDescriptor desc = ArrayDescriptor.createDescriptor("SSLS_WMS.TA_ORDER_ID", ps.getConnection());

Or, you can create a public synonym for your type, give the grants and then use the synonym in your TypeHandler.

CREATE OR REPLACE PUBLIC SYNONYM S_INT_ARRAY FOR SSLS_WMS.PKG_TEST.INT_ARRAY;

Upvotes: 1

Related Questions