如何在Mule4中从Oracle存储过程中获取表类型返回类型？

Question

您好，我正在尝试调用Oracle中的一个存储过程，它返回一个表类型。这是我的包裹

create or replace PACKAGE        "PKG_LOGIC_SERVICE" AS

TYPE ll_ret_rec IS RECORD (
    ll_attribute      loan_logic.attribute%TYPE,
    ll_ret_value      loan_logic.ret_value%TYPE,
    ll_edit_type      loan_logic.edit_type%TYPE,
    ll_applic_type    loan_logic.application_type%TYPE,
    ll_rule_order     loan_logic.rule_order%TYPE,
    ll_db_field_name  loan_logic.db_field_name%TYPE,
    ll_xpath_name     loan_logic.xpath_name%TYPE,
    ll_loan_id        loans.loan_id%TYPE,
    ll_process_id     NUMBER(12)
);
TYPE ll_ret_table IS
    TABLE OF ll_ret_rec;
PROCEDURE pr_loan_logic_check (
    in_loan_id        IN   loans.loan_id%TYPE,
    in_trans_id       IN   NUMBER,
    as_errm           OUT  VARCHAR2,
   -- curr_cursor_out   IN OUT  SYS_REFCURSOR,
    ll_ret_table_out  OUT  PKG_LOGIC_SERVICE.ll_ret_table
);

PROCEDURE pr_insert_pricing_table (
    in_loan_id     IN   loans.loan_id%TYPE,
    ad_cur_seq_no  OUT  NUMBER,
    ad_retval      OUT  NUMBER,
    as_errm        OUT NOCOPY VARCHAR2
);

END PKG_LOGIC_SERVICE;

我尝试从Mule调用的方式如下所示

{call PKG_LOGIC_SERVICE.pr_loan_logic_check(776665544,987,:as_errm,:ll_ret_table_out)}

但是我得到了一个错误

wrong number or types of arguments in call to 'pr_loan_logic_check'

我的XML SP部分是

<db:stored-procedure doc:name="Stored procedure" doc:id="ffc0e3aa-193a-4e86-91df-a52562e5acc4" 
config-ref="DB_CONFIG">
        <db:sql ><![CDATA[{call 
 PKG_LOGIC_SERVICE.pr_loan_logic_check(776665544,987,:as_errm,:ll_ret_table_out)}]]></db:sql>
        <db:output-parameters >
            <db:output-parameter key="ll_ret_table_out" customType="TABLE" />
            <db:output-parameter key="as_errm" type="VARCHAR" />
        </db:output-parameters>
    </db:stored-procedure>

我做错了什么? mule在调用过程时是如何知道自定义类型表的？

Answer 1

可以通过遵循本文来修复它

https://help.mulesoft.com/s/article/Calling-Oracle-Stored-Procedure-with-User-Defined-Types-UDT-as-IN-OUT-or-IN-OUT-Parameters-in-Mule-4-x

Answer 2

输出类型应为"CURSOR“而非"TABLE”

<db:output-parameter key="ll_ret_table_out" customType="CURSOR" />

此外，我不确定SP调用是否仅使用3个参数就可以工作。您是否在外部SQL客户端上尝试过它？

您可能也想阅读有关Oracle UDT的内容。