9

I have been googling this for a while and cannot seem to find any real answers.

I have an Oracle stored procedure that has a number of in parameters that have a type that is table of the table rowtype. So for example:

Declared in the pacakge:

TYPE param1_type_t IS TABLE OF table1%ROWTYPE;
TYPE param2_type_t IS TABLE OF table2%ROWTYPE;
TYPE param3_type_t IS TABLE OF table3%ROWTYPE;

Oracle Procedure:

PROCEDURE my_proc
(
   parameter1    IN param1_type_t,
   parameter2    IN param2_type_t,
   parameter3    IN param3_type_t
)

On the java side, I have 3 corresponding Lists of objects representing each of the parameters that are populated in Java. Is it possible to call the Oracle procedure using MyBatis in this scenario?

<update id="callOracleSP" statementType="CALLABLE">
    {CALL my_proc( #{param1, mode=IN},
                   #{param2, mode=IN},
                   #{param3, mode=IN}
                 )
    }
</update>

The objects themselves are simple VOs with String and Integer properties and their respective getters and setters.

I am not really sure how to proceed. Do I need to somehow map the Java object lists to the Oracle types?

2 Answers 2

12

I can't tell if you do already or not, but you'll need Oracle objects defined.

CREATE OR REPLACE TYPE SCHEMA."YOUR_OBJECT" AS OBJECT
(
    field_one    varchar2(50),
    field_two    varchar2(100)
);
/
CREATE OR REPLACE TYPE SCHEMA."YOUR_OBJECT_ARRAY" AS TABLE OF YOUR_OBJECT;
/

Then you can write type handlers to map the Java objects to the Oracle objects.

import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;
import oracle.sql.STRUCT;
import oracle.sql.StructDescriptor;
....
public class YourTypeHandler implements TypeHandler
{
....
    public void setParameter(PreparedStatement ps, int i, Object parameter, JdbcType jdbcType) throws SQLException
    {
        List<YourObject> objects = (List<YourObject>) parameter;

        StructDescriptor structDescriptor = StructDescriptor.createDescriptor("YOUR_OBJECT", ps.getConnection());

        STRUCT[] structs = new STRUCT[objects.size()];
        for (int index = 0; index < objects.size(); index++)
        {
            YourObject pack = packs.get(index);
            Object[] params = new Object[2];
            params[0] = pack.getFieldOne();
            params[1] = pack.getFieldTwo();
            STRUCT struct = new STRUCT(structDescriptor, ps.getConnection(), params);
            structs[index] = struct;
        }

        ArrayDescriptor desc = ArrayDescriptor.createDescriptor("YOUR_OBJECT_ARRAY", ps.getConnection());
        ARRAY oracleArray = new ARRAY(desc, ps.getConnection(), structs);
        ps.setArray(i, oracleArray);
    }
}

Then invoke the procedure,

call your_proc
(
#{yourObjects, javaType=Object, jdbcType=ARRAY, jdbcTypeName=YOUR_OBJECT_ARRAY, mode=IN, typeHandler=YourObjectArrayTypeHandler}
)
Sign up to request clarification or add additional context in comments.

2 Comments

can't find class/jar for TypeHandler. already have ojdbc6.jar
brother what if pack.getFieldOne() is another TAB (array)
1

Andy Pryor's answer is very good I tested it and it really works. But it has an error at typeHandler:

call your_proc
(
#{yourObjects, javaType=Object, jdbcType=ARRAY, jdbcTypeName=YOUR_OBJECT_ARRAY, mode=IN, typeHandler=YourObjectArrayTypeHandler}
)

should be:

call your_proc
(
#{yourObjects, javaType=Object, jdbcType=ARRAY, jdbcTypeName=YOUR_OBJECT_ARRAY, mode=IN, typeHandler=YourTypeHandler}
)

The TypeHandler has an error as well: (there is no "packs" and there is some difference in the method parameters in my version)

@Override
public void setParameter(PreparedStatement ps, int i, Object parameter, String arg3) throws SQLException {
    List<YourObject> objects = (List<YourObject>) parameter;

    StructDescriptor structDescriptor = StructDescriptor.createDescriptor("YOUR_OBJECT", ps.getConnection());

    STRUCT[] structs = new STRUCT[objects.size()];
    for (int index = 0; index < objects.size(); index++)
    {
        YourObject pack = objects.get(index);
        Object[] params = new Object[2];
        params[0] = pack.getFieldOne();
        params[1] = pack.getFieldTwo();
        STRUCT struct = new STRUCT(structDescriptor, ps.getConnection(), params);
        structs[index] = struct;
    }

    ArrayDescriptor desc = ArrayDescriptor.createDescriptor("YOUR_OBJECT_ARRAY", ps.getConnection());
    ARRAY oracleArray = new ARRAY(desc, ps.getConnection(), structs);
    ps.setArray(i, oracleArray);
}

And here is an example for xml mapping:

  <parameterMap id="updateHierPersonAssignMap" class="java.util.Map" >                      
    <parameter property="p_array" jdbcType="ARRAY" javaType="Object" mode="IN" typeHandler="com.aamtech.ria.model.domain.typehandler.YourTypeHandler"/>
  </parameterMap>
  <procedure id="updateHierPersonAssign" parameterMap="updateHierPersonAssignMap" >
    <![CDATA[ 
        { call ria_am_util_pkg.j_update_hier_person_assign( ? ) }
    ]]>
  </procedure>  

And here is how you can call it from the DAO:

public void update(List array) {
    Map<String, Object> queryParams = new HashMap<String, Object>();
    queryParams.put("p_array", array);
    try {
        client.update("HashMapResult.updateHierPersonAssign", queryParams);
    } catch (SQLException e) {
    }
}

And my procedure looks like this (it just inserts a row into a test table):

Procedure j_update_hier_person_assign (p_array IN YOUR_OBJECT_ARRAY) is
  begin
     FOR i IN 1..p_array.count LOOP
       --dbms_output.put_line();
       insert into test (a) values (p_array(i).field_one);
     END LOOP;
  end;

1 Comment

params[0] = pack.getFieldOne(); what if this is another Array(Tab of Tab)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.