Only numeric and string values are accepted in call to stored procedure from mule 4

This is the sample request payload that is being sent to insert into the oracle stored procedure:

{
    "inputParameters": {
        "p_h_inv_cur": "EUR",
        "p_h_inv_date": "20210101",
        "p_h_tax_amt": 245.57,
        "p_h_po_num": "1234",
        "p_lne_tbl": ""
    },
    "outputParameters": {
        "p_out_inv_id": "NUMERIC",
        "p_out_er_code": "VARCHAR"
    },
    "inOutParameters": {},
    "query": "{call XXX_PKG.XXX_STG(:p_h_inv_cur,:p_h_inv_date,:p_h_tax_amt,:p_h_po_num,:p_lne_tbl,:p_out_inv_id,:p_out_er_code)}"
}

The field named p_lne_tbl is of TABLE (UDT) type. So it should accept an array of objects.

But when I try to send array in that field then I get error as:

Invalid conversion requested: java.util.LinkedHashMap to java.lang.String

So I tried converting that array into application/java, but then I get error as:

ORA-06550: line 1, column 7:\nPLS-00306: wrong number or types of arguments in call to 'INSERT_INVOICE_STAGE'\nORA-06550: line 1, column 7:\nPL/SQL: Statement ignored

Database configuration:

<db:config name="Database_Config" doc:name="Database Config" doc:id="5eaa8218-938e-4fc0-b3e9-0e3ba7e98a89" >
        <db:oracle-connection host="${db.host}" port="${db.port}" user="${db.user}" password="${db.password}" instance="${db.instance}" >
            <reconnection >
                <reconnect frequency="${reconnect.frequency}" count="${reconnect.attempts}" />
            </reconnection>
            <db:pooling-profile maxPoolSize="${db.maxPoolSize}" minPoolSize="${db.minPoolSize}" acquireIncrement="${db.acquireIncrement}" preparedStatementCacheSize="${db.cacheSize}" maxWait="${db.maxWait}" />
        </db:oracle-connection>
    </db:config>

DB stored procedure configuration:

<db:stored-procedure doc:name="invokingStoredProcedure" doc:id="6bcd83f8-bb62-4826-9cb7-4afa5d28168c" config-ref="Database_Config" inOutParameters="#[vars.inOutParameters]" outputParameters='#[vars.outputParameters]' queryTimeout="${db.querytimeout}" fetchSize="${db.prefetch}" doc:description="calling storedprocedure and executing the query">
            <ee:repeatable-file-store-stream inMemorySize="${db.inMemorySize}" />
            <reconnect frequency="${reconnect.frequency}" count="${reconnect.attempts}" />
            <db:sql >#[vars.query]</db:sql>
            <db:input-parameters ><![CDATA[#[vars.inputParameters]]]>
            </db:input-parameters>
        </db:stored-procedure>

the variables used in the configurations are:

inOutParameter:

output application/json
---
payload.inOutParameters

outputParameters:

%dw 2.0
output application/json
--- 
payload.outputParameters pluck(value,key)-> {
     key : key,
     typeClassifier:{
        "type": value
    } 
}

inputParmaters:

%dw 2.0
output application/json
---
payload.inputParameters

query:

%dw 2.0
output application/json
---
payload.query

payload mentioned above is the payload which I have mentioned at the top.

NOTE: for testing, I disabled the field p_lne_tbl from the oracle side, that I was getting successful response. This is how I knew that the issue is with this field, p_lne_tbl .

Please let me know what is the issue here.

Solution

You are using the stored procedure operation of the Database connector in a generic way, ie the parameters and the query are passed dynamically. That works for base types, but for arrays and user defined types it requires them to be constructed with special functions Db::createArray() and Db::createStruct(). Also you may need to specify the types in the database connector configuration.


    <db:config name="dbConfig" >
        <db:oracle-connection host="localhost" user="SYS as SYSDBA" password="Oradoc_db1" instance="ORCLCDB">
            <db:column-types>
                <db:column-type id="2003" typeName="PEOPLE"/>
                <db:column-type id="2003" typeName="PHONE_NUMBER"/>
                <db:column-type id="2008" typeName="PERSON" />
                <db:column-type id="2003" typeName="PHONE_NUMBER_ARRAY"/>
                <db:column-type id="2003" typeName="PHONE_BOOK"/>
            </db:column-types>
        </db:oracle-connection>
    </db:config>

...
        <ee:transform doc:name="Transform Message - Prepare UDT">
            <ee:variables>
                <ee:set-variable variableName="in_people_tab"><![CDATA[%dw 2.0
                output application/java
                fun toPhoneNumberArray(phoneNumber) = Db::createArray("dbConfig", "PHONE_NUMBER_ARRAY",[Db::createStruct("dbConfig", "PHONE_NUMBER", [phoneNumber.areaCode, phoneNumber.phoneNumber])])
                fun toPerson(person) = Db::createStruct("dbConfig", "PERSON", [person.name, person.surname, person.age, toPhoneNumberArray(person.phoneNumber)])
                ---
                Db::createArray("dbConfig", "PEOPLE", payload map (item, index) -> ( toPerson(item) ) )
                ]]></ee:set-variable>
            </ee:variables>
        </ee:transform>
        <db:stored-procedure config-ref="dbConfig">
            <db:sql><![CDATA[{ call proc_insert_humans(:people, :phoneBook) }]]></db:sql>
            <db:input-parameters><![CDATA[{
                people: vars.in_people_tab
            }]]></db:input-parameters>
            <db:output-parameters >
                <db:output-parameter key="phoneBook" customType="PHONE_BOOK" />
            </db:output-parame

You can find the complete example in the documention: https://docs.mulesoft.com/db-connector/1.10/database-connector-udt-stored-procedure

That means you'll need either to construct them manually in a script or develop a DataWeave script that can perform the transformation generically. You example input only shows an empty value for the field and not type information, so I don't sure you would be able to write a generic script with no hints of the types. Once you resolve that the example above could be useful as a basis.