Блог

Все,

Мне нужно перейти к хранимой процедуре в Mulesoft. Определение хранимой процедуры Oracle выглядит следующим образом:

   procedure update_snapsheet_task(P_ASSIGNMENT_ID     IN number,
                              P_ASSIGNMENT_STATUS IN varchar2 default null,
                              P_REFERRAL_ID       IN number,
                              P_DOCUMENT_ARRAY    IN p_document_arr,
                              P_USER_ID           IN varchar2,
                              P_ERROR_STATUS      OUT varchar2);
  

где p_document_arr — параметр UDT, который необходимо передать в Mulesoft

p_document_arr определяется в oracle как:

 Type p_documentid_arr IS RECORD(
   document_key   VARCHAR2(4000),
   document_id    NUMBER(10),
   DOWNLOAD_COUNT NUMBER default 0);
Type p_document_arr is table of p_documentid_arr index by binary_integer;
  

Как мне передать что-либо в P_DOCUMENT_ARRAY в Mulesoft 4.0?

Я попробовал эту ссылку: https://help.mulesoft.com/s/article/How-to-pass-an-array-of-objects-to-a-stored-procedure-in-mule-4

В принципе, ссылка предлагает использовать

 %dw 2.0
output application/java
fun documentContents(documentRecord) = (
    Db::createStruct("Database_Config", "P_DOCUMENTID_ARR", [
                    documentRecord.document_key,
                    documentRecord.document_id,
                    documentRecord.download_count ])
)
fun documentContentTab(documentArray) = (
    documentArray map ((item, index) ->
        documentContents(item)
    )
)
---   
{
    "p_assignment_id": vars.inputPayload.assignment_id,
    "p_assignment_status": vars.inputPayload.assignment_status,
    "p_referral_id": vars.inputPayload.referral_id,
    "p_document_array": Db::createArray("Database_Config",
        "P_DOCUMENT_ARR",
        documentContentTab(vars.inputPayload.documents default [])),
    "p_user_id": vars.inputPayload.user_id
}
  

Для вызова хранимой процедуры:

 SQL Query : { CALL cpm.cpm_notification_services.update_snapsheet_task(:p_assignment_id, :p_assignment_status, :p_referral_id, :p_document_array, :p_user_id, :p_error_type) }
  

Кроме того, в глобальной конфигурации я помещаю:

     <db:config name="Database_Config" doc:name="Database Config"
    doc:id="22ad9e72-3df1-4d21-bf8a-407d0c97e491">
    <db:data-source-connection dataSourceRef="oraclePoolDataSource" >
        <db:column-types >
            <db:column-type id="-10" typeName="CURSOR" className="oracle.jdbc.OracleTypes" />
            <db:column-type id="2003" typeName="P_DOCUMENT_ARR" />
            <db:column-type id="2002" typeName="P_DOCUMENTID_ARR" />
        </db:column-types>
    </db:data-source-connection>
</db:config>
  

Но это не сработало. Mulesoft выдает ошибку:

 {
  "status": "Server Error",
  "message": ""Exception while executing createStruct("Database_Config","P_DOCUMENTID_ARR",["abcdes123", 1, 0]) cause: An error occurred when trying to create JDBC Structure. Fail to construct descriptor: Unable to resolve type "P_DOCUMENTID_ARR" nTrace:n  at callFunction (Unknown)n  at createStruct (line: -1, column: -1)n  at documentContents (line: 4, column: 1)n  at main (line: 11, column: 1), while writing Java.nTrace:n  at callFunction (Unknown)n  at createArray (line: -1, column: -1)n  at main (line: 19, column: 21)" evaluating expression:...
...
  

Или из журналов консоли:

 Message               : "Exception while executing 

createStruct("Database_Config","P_DOCUMENTID_ARR",["abcdes123", 1, 0]) cause: An error occurred when trying to create JDBC Structure. Fail to construct descriptor: Unable to resolve type "P_DOCUMENTID_ARR" 
Trace:
  at callFunction (Unknown)
  at createStruct (line: -1, column: -1)
  at documentContents (line: 4, column: 1)
  at main (line: 11, column: 1), while writing Java.
Trace:
  at callFunction (Unknown)
  at createArray (line: -1, column: -1)
  at main (line: 19, column: 21)" evaluating expression: "%dw 2.0
output application/java
fun documentContents(documentRecord) = (
Db::createStruct("Database_Config", "P_DOCUMENTID_ARR", [
    documentRecord.document_key,
        documentRecord.document_id,
        documentRecord.download_count ])
)
fun documentContentTab(documentArray) = (
documentArray map ((item, index) ->
documentContents(item)
)
)
---
{
"p_assignment_id": vars.inputPayload.assignment_id,
"p_assignment_status": vars.inputPayload.assignment_status,
"p_referral_id": vars.inputPayload.referral_id,
"p_document_array": Db::createArray("Database_Config", 
"P_DOCUMENT_ARR",
documentContentTab(vars.inputPayload.documents default [])),
"p_user_id": vars.inputPayload.user_id
}".
  

Пожалуйста, помогите.