Mule: Invoking an Oracle stored procedure which returns a table of custom type
NickName:CLawson Ask DateTime:2015-08-14T22:07:17

Mule: Invoking an Oracle stored procedure which returns a table of custom type

I am using Mule CE 3.6.1. I have the following Database connector configuration calling an Oracle stored procedure.

<db:stored-procedure config-ref="Oracle_Configuration" doc:name="Database">
    <db:parameterized-query><![CDATA[call get_phone_email(:userId, :tPhoneRecord)]]></db:parameterized-query>
    <db:in-param name="userId" type="NUMERIC" value="#[payload]" />
    <db:out-param name="tPhoneRecord" type="ARRAY" />
</db:stored-procedure>

Parameter tPhoneRecord is defined as IS TABLE OF phone_email%ROWTYPE (i.e. table of records) in the stored procedure. I have tried specifying the parameter type ARRAY but get the error:

Message    : Invalid argument(s) in call (java.sql.SQLException). Message payload is of type Object[]
Code       : MULE_ERROR--2

I have also tried using other out-param types or not specifying a type without success.

Please let me know what out-param type I should use for an Oracle table of records, or if I should do this in Java instead. Many thanks in advance.

Copyright Notice:Content Author:「CLawson」,Reproduced under the CC 4.0 BY-SA copyright license with a link to the original source and this disclaimer.
Link to original article:https://stackoverflow.com/questions/32012072/mule-invoking-an-oracle-stored-procedure-which-returns-a-table-of-custom-type

More about “Mule: Invoking an Oracle stored procedure which returns a table of custom type” related questions

Mule: Invoking an Oracle stored procedure which returns a table of custom type

I am using Mule CE 3.6.1. I have the following Database connector configuration calling an Oracle stored procedure. &lt;db:stored-procedure config-ref="Oracle_Configuration" doc:name="Database"&gt...

Show Detail

Oracle Stored Procedure and custom data type

I have an Oracle stored procedure which takes two parameters: a custom data type and a string. Calling the stored procedure in Oracle, I would do the following: EXECUTE MY_STORED_PROCEDURE(MYTYPE...

Show Detail

How to get TABLE Type return type from Oracle Stored procedure in Mule4?

Hi I am trying to call a stored procedure in Oracle which return a TABLE type . This is my Package create or replace PACKAGE &quot;PKG_LOGIC_SERVICE&quot; AS TYPE ll_ret_rec IS RECORD (

Show Detail

JDBC: Oracle stored procedure returns nested table

I have a stored procedure in oracle(12c): PROCEDURE work(a OUT VARCHAR2, b OUT SYS_REFCURSOR) When I call the procedure the result of cursor contains rows, the row consists of some primitive valu...

Show Detail

Oracle equivalent of stored procedure that returns an inline table?

Example in T-SQL (SQL Server - taken from here): CREATE PROC proc_authors @au_lname VARCHAR(40) AS SELECT au_id, au_fname, au_lname, city, state FROM authors WHERE au_lna...

Show Detail

Creating Oracle stored procedure that returns data

In Firebird you can create a stored procedure that returns data an invoke it like a table passing the arguments: create or alter procedure SEL_MAS_IVA ( PCANTIDAD double precision) returns (

Show Detail

Issue while retrieving varchar values from Stored procedure in Mule

Hi I'm not able to retrieve the varchar values from Stored procedure in to my mule flow. It is always returning NULL values.I have followed the same order while declare the output parameters both i...

Show Detail

Table variable in Oracle stored procedure

I'm new to Oracle and I need to translate my SQL Server stored procedure to oracle. In SQL Server, I have function that returns a table variable. Executing looks like: Declare @tblTSVal table(st...

Show Detail

Mule exec Oracle Stored Procedure

Could someone share how to call a stored procedure under Mule CE, using named parameters? I would like to use the JDBC component or the new one DB component under AnyPointConnect 3.5

Show Detail

MULE 4 : STORED PROCEDURE : Executing mule flow to run stored procedure returns "The index 1 is out of range"

I am trying to execute a stored procedure in using MULE 4. It expects two inputs, one is a table value parameter and another is an integer. For the table value parameter, i am using SQLServerDataTa...

Show Detail