0

I need to call an Oracle Stored Procedure that has an input argument with a nested table type, using Mybatis.

I cannot find any documentation or example concerning this particular usage of MyBatis.

Has anyone done this before, or seen an example?

Many thanks.

Andres
  • 10,561
  • 4
  • 45
  • 63
  • Pl/SQL types (e.g table, record etc) are not supported when calling a stored procedure from Java. Your best bet in making this work is creating a new wrapper procedure that will use only SQL types as input (or output ). Your Java code will call this wrapper procedure, which will, in turn, call your original Oracle procedure (it will have to prepare the parameters to match that of the original proc). – dsp_user Sep 04 '17 at 13:13
  • @dsp_user That is incorrect - you can pass PL/SQL collection types to stored procedures and statements in Java [[1](https://stackoverflow.com/a/42697156/1509264), [2](https://stackoverflow.com/a/37161584/1509264), [3](https://stackoverflow.com/a/34699771/1509264)]. – MT0 Sep 04 '17 at 13:21
  • I think he's talking about MyBatis specifically, not Java. – Andres Sep 04 '17 at 13:24
  • Possible duplicate of https://stackoverflow.com/q/12719689/1509264 – MT0 Sep 04 '17 at 13:26
  • This could have portability issues because I don't think that every JDBC driver supports it. But the OP may certainly try some of your solutions.:) – dsp_user Sep 04 '17 at 13:26
  • @Anders, I'm talking about Java, not MyBatis (not sure about MyBatis support for stored procedures in general). – dsp_user Sep 04 '17 at 13:28
  • @dsp_user As far as I know Java needs to be using Oracle's JDBC driver to do this. Some of the DataDirect drivers may support fetching collection types nested in Oracle cursors (and maybe as stored procedure out parameters) but do not appear to support passing them as in parameters. – MT0 Sep 04 '17 at 13:32
  • I know for a fact that a stored procedure can return an Oracle cursor (as a SYS_REFCURSOR), however I thought that tables and records were not directly supported (though perhaps there are ways to still do that) – dsp_user Sep 04 '17 at 13:35

1 Answers1

1

Let's take this example:

PROCEDURE PROCEDURERECORD (P_VAL_REC IN Package.RECORD ,P_VAL_NUM IN VARCHAR2 ,P_DAT_VAL OUT DATE );

In your database redefine the SP:

PROCEDURE PROCEDURERECORD_NEW (P_VAL_REC IN RECORD_TYPE (you create it in Types) ,P_VAL_NUM IN VARCHAR2 ,P_DAT_VAL OUT DATE );

you should reconfigure you SP bean with spring :

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:p="http://www.springframework.org/schema/p" xmlns:util="http://www.springframework.org/schema/util"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.springframework.org/schema/beans                         http://www.springframework.org/schema/beans/spring-beans-3.0.xsd                            http://www.springframework.org/schema/util                             http://www.springframework.org/schema/util/spring-util.xsd">

<bean id="PROCEDURERECORD_NEW" parent="storedProcedure">
    <constructor-arg index="0" value="PROCEDURERECORD" />
    <constructor-arg index="1" value="false" />
    <property name="params">
        <list>
            <bean parent="sqlRecordParamIn">
                <constructor-arg index="0" value="P_VAL_REC" />
                <constructor-arg index="2" value="RECORD_TYPE" />
            </bean>
            <bean parent="sqlNumericParamIn">
                <constructor-arg value="P_VAL_NUM" />
            </bean>
            <bean parent="sqlDateParamOut">
                <constructor-arg value="P_DAT_VAL"/>
            </bean>
        </list>
    </property>
</bean>

In your implementation code, use SqlStructValue like this:

@Override
public DateTime getSpReturn(RecordClass record,Long valNum){
    Map<String, Object> args = new HashMap<String, Object>();
    args.put("P_VAL_REC", new SqlStructValue<RecordClass>(record,new RecordClassMapper()));
    args.put("P_VAL_NUM", valNum);


    Map<String, Object> result = procedureRecordNew.execute(args);
    return (DateTime)result.get("P_DAT_VAL");
}

As for the mapper you create it like this:

@Component("RecordClassMapper")
public class RecordClassMapper implements StructMapper<RecordClass> {

@Override
public STRUCT toStruct(RecordClass source, Connection conn, String typeName) throws SQLException {
    Object[] objectProperties = new Object[] { new source.getrecordatr1(), source.getrecordatr2(), source.getrecordatr3() };
    return new STRUCT(new StructDescriptor(typeName, conn), conn, objectProperties);
}

@Override
public RecordClass fromStruct(STRUCT struct) throws SQLException {
    // Auto-generated method stub
    throw new UnsupportedOperationException("Not implemented");
}

}

K.Mouna
  • 83
  • 1
  • 7
  • plus one for the example...but what I need is Mybatis, not Spring :) – Andres Sep 04 '17 at 16:43
  • As I know, you cannot call such SP in mybatis, if you work with java I think resolve it with spring would be simple. (We had the same problem in our project). – K.Mouna Sep 04 '17 at 16:58
  • We call other SPs. Are you sure that you cannot call sps that have plsql tables as arguments? Is it written somewhere? – Andres Sep 04 '17 at 17:00
  • @Anders, take a look at here http://docs.oracle.com/database/122/JJDBC/Oracle-extensions.htm#JJDBC28180. I haven't tried this but associated arrays are basically index by tables having a fixed length. Don't know if this will work in your case. However, this seems to be used only with OUT args. – dsp_user Sep 04 '17 at 20:01