I'm trying to execute an SP in Express JS with "oracledb" dependency but when i try to pass the object doesn't work.
This is my type Object
create or replace TYPE ORDER_HEADER_OBJ AS object
(
DOC_TYPE VARCHAR2(20),
SALES_ORG NUMBER(10),
DISTR_CHAN NUMBER(30),
DIVISION NUMBER(10),
REQ_DATE_H VARCHAR2(10),
PO_METHOD VARCHAR2(30),
REF_1 VARCHAR2(20),
NAME VARCHAR2(20),
INCOTERMS1 VARCHAR2(50),
PMNTTRMS VARCHAR2(50),
DLV_BLOCK VARCHAR2(50),
COMPL_DLV VARCHAR2(50),
PRICE_DATE VARCHAR2(50),
CUST_GRP5 VARCHAR2(20),
PURCH_NO_C VARCHAR2(30),
REF_1_S VARCHAR2(20),
DOC_DATE VARCHAR2(20),
SHIP_COND NUMBER(10),
COMP_CDE_B NUMBER(10),
ALTTAX_CLS VARCHAR2(20),
BILL_DATE VARCHAR2(10),
CURRENCY VARCHAR2(5)
);
and i create a type table because i want to send 2 or more record
create or replace TYPE ORDER_HEADER_TAB AS TABLE OF ORDER_HEADER_OBJ;
And this is my SP:
create or replace PROCEDURE SP_SaleOrders (order_header_in IN ORDER_HEADER_TAB)
AS
mensaje_error VARCHAR2(200);
filas_insertadas INTEGER := 0;
BEGIN
BEGIN
FOR i IN 1..order_header_in.COUNT LOOP
INSERT INTO
TBLTESTHEADERWS (DOC_TYPE, SALES_ORG, DISTR_CHAN, DIVISION, REQ_DATE_H,
PO_METHOD, REF_1, NAME, INCOTERMS1,PMNTTRMS,
DLV_BLOCK, COMPL_DLV, PRICE_DATE, CUST_GRP5, PURCH_NO_C,
REF_1_S, DOC_DATE, SHIP_COND, COMP_CDE_B, ALTTAX_CLS,
BILL_DATE, CURRENCY)
VALUES
(order_header_in(i).DOC_TYPE, order_header_in(i).SALES_ORG, order_header_in(i).DISTR_CHAN, order_header_in(i).DIVISION, TO_DATE(order_header_in(i).REQ_DATE_H, 'YYYYMMDD'),
order_header_in(i).PO_METHOD, order_header_in(i).REF_1, order_header_in(i).NAME, order_header_in(i).INCOTERMS1, order_header_in(i).PMNTTRMS,
order_header_in(i).DLV_BLOCK, order_header_in(i).COMPL_DLV, TO_DATE(order_header_in(i).PRICE_DATE, 'YYYYMMDD'), order_header_in(i).CUST_GRP5, order_header_in(i).PURCH_NO_C,
order_header_in(i).REF_1_S, TO_DATE(order_header_in(i).DOC_DATE, 'YYYYMMDD'),order_header_in(i).SHIP_COND, order_header_in(i).COMP_CDE_B, order_header_in(i).ALTTAX_CLS,
TO_DATE(order_header_in(i).BILL_DATE, 'YYYYMMDD'), order_header_in(i).CURRENCY);
END LOOP;
EXCEPTION
WHEN INVALID_NUMBER THEN
mensaje_error := 'Error TBLTESTHEADERWS: Valor no válido para la columna';
WHEN VALUE_ERROR THEN
mensaje_error := 'Error TBLTESTHEADERWS: Valor no compatible con el tipo de datos de la columna';
WHEN OTHERS THEN
mensaje_error := 'Error TBLTESTHEADERWS: Se ha producido un error desconocido al insertar los datos';
END;
IF filas_insertadas > 0 THEN
DBMS_OUTPUT.PUT_LINE('Ya se hizo el guiso.');
ELSE
-- Devuelve el mensaje de error al llamante
DBMS_OUTPUT.PUT_LINE(mensaje_error);
END IF;
END;
I tested my SP with this sentence and it works in DB:
DECLARE
order_header_table ORDER_HEADER_TAB := ORDER_HEADER_TAB();
BEGIN
order_header_table.EXTEND;
order_header_table(order_header_table.COUNT) := ORDER_HEADER_OBJ('value1', 'value2', ...);
order_header_table.EXTEND;
order_header_table(order_header_table.COUNT) := ORDER_HEADER_OBJ('value1', 'value2', ...);
SP_SaleOrderS(order_header_table);
END;
I do it like that the SP because i'm using Oracle 11G
Now i want to execute the SP in ExpressJs with oracledb and I get this error
"Error: NJS-012: encountered invalid bind data type in parameter 2"
this is my code:
const insertSalesOrderAllied = async (body) => {
try {
let data = [
{
DOC_TYPE: "API TEST",
SALES_ORG: 10,
DISTR_CHAN: 20,
DIVISION: 30,
REQ_DATE_H: "20220101",
PO_METHOD: "method1",
REF_1: "ref1",
NAME: "name1",
INCOTERMS1: "incoterms1",
PMNTTRMS: "pmnttrms1",
DLV_BLOCK: "dlv_block1",
COMPL_DLV: "compl_dlv1",
PRICE_DATE: "20220101",
CUST_GRP5: "cust_grp5_1",
PURCH_NO_C: "purch_no_c1",
REF_1_S: "ref1_s1",
DOC_DATE: "20220101",
SHIP_COND: 1,
COMP_CDE_B: 2,
ALTTAX_CLS: "alttax_cls1",
BILL_DATE: "20220101",
CURRENCY: "USD",
},
{
DOC_TYPE: "API TEST",
SALES_ORG: 10,
DISTR_CHAN: 20,
DIVISION: 30,
REQ_DATE_H: "20220101",
PO_METHOD: "method1",
REF_1: "ref1",
NAME: "name1",
INCOTERMS1: "incoterms1",
PMNTTRMS: "pmnttrms1",
DLV_BLOCK: "dlv_block1",
COMPL_DLV: "compl_dlv1",
PRICE_DATE: "20220101",
CUST_GRP5: "cust_grp5_1",
PURCH_NO_C: "purch_no_c1",
REF_1_S: "ref1_s1",
DOC_DATE: "20220101",
SHIP_COND: 1,
COMP_CDE_B: 2,
ALTTAX_CLS: "alttax_cls1",
BILL_DATE: "20220101",
CURRENCY: "USD",
},
];
const result = await bd
.open(
`
BEGIN
SP_SaleOrders (:order_header_in );
END;`,
[data],
true
)
.then((res) => {
console.log(res);
})
.catch((err) => {
console.log(err);
console.log("Error al ejecutar el SP SP_SaleOrder", err);
});
return {
status: 201,
message: result,
};
} catch (error) {
throw boom.badRequest("Catch: " + error);
}
};
What do i have to change to make it work?
I try changing the parameter to this:
const result = await bd
.open(
`
BEGIN
SP_SaleOrders (:order_header_in );
END;`,
{
order_header_in: {
type: oracledb.DB_TYPE_OBJECT,
dir: oracledb.BIND_IN,
val: data,
},
},
true
)
.then((res) => {
console.log(res);
})
.catch((err) => {
console.log(err);
console.log("Error al ejecutar el SP SP_SaleOrder", err);
});
But i get the error: "Error: DPI-1025: no object type specified for object variable"
I just want to execute the SP from ExpressJS using oracledb.
Look at the node-oracledb example selectobject.js and see the value(s) it uses for type. See methods 3 & 4. You need to specify the 'shape' of the object, and cannot use oracledb.DB_TYPE_OBJECT.
From selectobject.js:
//
// Insert Method 4: use the Oracle type name.
// Note: use a fully qualified type name when possible.
//
await connection.execute(
`INSERT INTO no_farmtab (id, farm) VALUES (:id, :f)`,
{ id: 4,
f: {
type: 'DBFARMTYPE', // the name of the top level database type, case sensitive
val: { // a JavaScript object that maps to the DB object
FARMERNAME: 'Boy',
HARVEST: ['flowers', 'seedlings' ]
}
}
}
);
With 11.2 you might hit some object binding limitations that have been removed in more recent versions of Oracle Database.