how to pass array parameter in stored procedure with soa oracle

124 views Asked by At

im java developer and new in soa oracle environment with bpel concept. I'll to passing parameter with json which some parameter in the array value. and i've created some service to passing this json to get the output value but while invoking with my service throwing an error.

i have stored procedure like this:

 --MATCH PO
TYPE p_type_shipment_header_id  IS TABLE OF NUMBER          INDEX BY BINARY_INTEGER;

--NON PO
TYPE pt_invoice_amount_l        IS TABLE OF NUMBER          INDEX BY BINARY_INTEGER;
TYPE pt_invoice_description_l   IS TABLE OF VARCHAR2(240)   INDEX BY BINARY_INTEGER;
TYPE pt_project_id              IS TABLE OF NUMBER          INDEX BY BINARY_INTEGER;
TYPE pt_task_id                 IS TABLE OF NUMBER          INDEX BY BINARY_INTEGER;
TYPE pt_expenditure_type        IS TABLE OF VARCHAR2(30)    INDEX BY BINARY_INTEGER;
TYPE pt_expenditure_org_id      IS TABLE OF NUMBER          INDEX BY BINARY_INTEGER;
TYPE pt_expenditure_date        IS TABLE OF DATE            INDEX BY BINARY_INTEGER;
TYPE pt_tracking_status         IS TABLE OF VARCHAR2(240)   INDEX BY BINARY_INTEGER;
--15 December 2021 - Andhika MII : Tambahan Medical
TYPE pt_attribute8_l            IS TABLE OF VARCHAR2(150)   INDEX BY BINARY_INTEGER;
TYPE pt_attribute15_l           IS TABLE OF VARCHAR2(150)   INDEX BY BINARY_INTEGER;

procedure create_invoice_ap(
    p_invoice_source        IN  VARCHAR2,
    p_org_id                IN  NUMBER,
    p_invoice_tracking      IN  NUMBER,
    p_vendor_id             IN  NUMBER      DEFAULT NULL,
    p_vendor_site_id        IN  NUMBER      DEFAULT NULL,
    --p_shipment_header_id    IN  NUMBER      DEFAULT NULL,
    p_shipment_header_id    IN  P_TYPE_SHIPMENT_HEADER_ID,
    p_tracking_status       IN  PT_TRACKING_STATUS,
    p_invoice_amount        IN  NUMBER,
    p_direksi_pekerjaan     IN  VARCHAR2,
    p_tanggal_submit        IN  DATE,
    p_invoice_number        IN  VARCHAR2,
    p_invoice_description   IN  VARCHAR2,
    p_faktur                IN  VARCHAR2    DEFAULT '-',
    p_tanggal_faktur        IN  VARCHAR2,
    p_invoice_currency      IN  VARCHAR2,
    p_invoice_amount_l      IN  PT_INVOICE_AMOUNT_L,
    p_invoice_description_l IN  PT_INVOICE_DESCRIPTION_L,
    p_project_id            IN  PT_PROJECT_ID,  --NUMBER      DEFAULT NULL,
    p_task_id               IN  PT_TASK_ID,     --NUMBER      DEFAULT NULL,
    p_expenditure_type      IN  PT_EXPENDITURE_TYPE,    --VARCHAR2    DEFAULT NULL,
    p_expenditure_org_id    IN  PT_EXPENDITURE_ORG_ID,  --NUMBER      DEFAULT NULL,
    p_expenditure_date      IN  PT_EXPENDITURE_DATE,    --DATE        DEFAULT NULL,
    p_attribute8_l          IN  PT_ATTRIBUTE8_L,
    p_attribute15_l         IN  PT_ATTRIBUTE15_L,
    p_insert_status         OUT NUMBER, 
    p_insert_message        OUT VARCHAR2);

procedure attach_doc_invoice_ap(  
    p_filename              IN  VARCHAR2,
    p_org_id                IN  NUMBER, 
    p_vendor_id             IN  NUMBER,
    p_invoice_number        IN  VARCHAR2, 
    p_insert_status         OUT NUMBER, 
    p_insert_message        OUT VARCHAR2);        

end;

and I tried passing with json payload like this :

{
"P_INVOICE_SOURCE": "TEST",
"P_ORG_ID": 123,
"P_INVOICE_TRACKING": 3213,
"P_VENDOR_ID": 123123,
"P_VENDOR_SITE_ID": 12323,
"P_SHIPMENT_HEADER_ID": {
    "P_SHIPMENT_HEADER_ID_ITEM": [
        132685
    ]
},
 "P_TRACKING_STATUS" : {
  "P_TRACKING_STATUS_ITEM" :[
      "TEST"
    ]   
  },
"P_INVOICE_AMOUNT": 4123,
"P_DIREKSI_PEKERJAAN": "TEST",
"P_TANGGAL_SUBMIT": "2021-12-20",
"P_INVOICE_NUMBER": "TEST",
"P_INVOICE_DESCRIPTION": "TEST",
"P_FAKTUR": "",
"P_TANGGAL_FAKTUR": "2021-12-20",
"P_INVOICE_CURRENCY": "TEST",
"P_INVOICE_AMOUNT_L": {
    "P_INVOICE_AMOUNT_L_ITEM": []
},
"P_INVOICE_DESCRIPTION_L": {
    "P_INVOICE_DESCRIPTION_L_ITEM": []
},
"P_PROJECT_ID": {
    "P_PROJECT_ID_ITEM": []
},
"P_TASK_ID": {
    "P_TASK_ID_ITEM": []
},
"P_EXPENDITURE_TYPE": {
    "P_EXPENDITURE_TYPE_ITEM": []
},
"P_EXPENDITURE_ORG_ID": {
    "P_EXPENDITURE_ORG_ID_ITEM": []
},
"P_EXPENDITURE_DATE": {
    "P_EXPENDITURE_DATE_ITEM": []
},
"P_ATTRIBUTE8_L": {
    "P_ATTRIBUTE8_L_ITEM": []
},
"P_ATTRIBUTE15_L": {
    "P_ATTRIBUTE15_L_ITEM": []
}

}

getting below error :

Cause: java.sql.SQLException: ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'CREATE_INVOICE_AP'
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'CREATE_INVOICE_AP'
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'CREATE_INVOICE_AP'
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'CREATE_INVOICE_AP'
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'CREATE_INVOICE_AP'
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'CREATE_INVOICE_AP'
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'CREATE_INVOICE_AP'
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'CREATE_INVOICE_AP'
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'CREATE_INVOICE_AP'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

im sure my payload right the concept. am i missing something?

0

There are 0 answers