将对象作为参数传递给Express.js和oracledb中的Oracle数据库过程。

huangapple
go评论56阅读模式
英文:

Pass object as a parameter to Oracle DB procedure in Express.js and oracledb

问题

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 records

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 the 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

<details>
<summary>英文:</summary>

I&#39;m trying to execute an SP in Express JS with &quot;oracledb&quot; dependency but when i try to pass the object doesn&#39;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, &#39;YYYYMMDD&#39;), 
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, &#39;YYYYMMDD&#39;), 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, &#39;YYYYMMDD&#39;),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, &#39;YYYYMMDD&#39;), order_header_in(i).CURRENCY);
END LOOP;
EXCEPTION
WHEN INVALID_NUMBER THEN
mensaje_error := &#39;Error TBLTESTHEADERWS: Valor no v&#225;lido para la columna&#39;;
WHEN VALUE_ERROR THEN
mensaje_error := &#39;Error TBLTESTHEADERWS: Valor no compatible con el tipo de datos de la columna&#39;;
WHEN OTHERS THEN
mensaje_error := &#39;Error TBLTESTHEADERWS: Se ha producido un error desconocido al insertar los datos&#39;;
END;
IF filas_insertadas &gt; 0 THEN
DBMS_OUTPUT.PUT_LINE(&#39;Ya se hizo el guiso.&#39;);
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&#39;m using Oracle 11G
Now i want to execute the SP in ExpressJs with oracledb and I get this error
&quot;Error: NJS-012: encountered invalid bind data type in parameter 2&quot;
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) =&gt; {
console.log(res);
})
.catch((err) =&gt; {
console.log(err);
console.log(&quot;Error al ejecutar el SP SP_SaleOrder&quot;, 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: &quot;Error: DPI-1025: no object type specified for object variable&quot;
I just want to execute the SP from ExpressJS using oracledb.
</details>
# 答案1
**得分**: 2
查看 node-oracledb 示例 [`selectobject.js`](https://github.com/oracle/node-oracledb/blob/main/examples/selectobject.js) 并查看其用于 `type` 的值。请参考方法 3 和 4。您需要指定对象的 'shape'，并且不能使用 `oracledb.DB_TYPE_OBJECT`。
从 `selectobject.js`：

//
// 插入方法 4：使用 Oracle 类型名称。
// 注意：尽量使用完全限定的类型名称。
//

await connection.execute(
INSERT INTO no_farmtab (id, farm) VALUES (:id, :f),
{ id: 4,
f: {
type: 'DBFARMTYPE', // 顶级数据库类型的名称，区分大小写
val: { // 与 DB 对象映射的 JavaScript 对象
FARMERNAME: 'Boy',
HARVEST: ['flowers', 'seedlings']
}
}
}
);


在 11.2 版本中，您可能会遇到一些已在更近期的 Oracle 数据库版本中移除的对象绑定限制。
<details>
<summary>英文:</summary>
Look at the node-oracledb example [`selectobject.js`](https://github.com/oracle/node-oracledb/blob/main/examples/selectobject.js) and see the value(s) it uses for `type`.  See methods 3 &amp; 4.  You need to specify the &#39;shape&#39; 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: &#39;DBFARMTYPE&#39;,   // the name of the top level database type, case sensitive
val: {                // a JavaScript object that maps to the DB object
FARMERNAME: &#39;Boy&#39;,
HARVEST: [&#39;flowers&#39;, &#39;seedlings&#39; ]
}
}
}
);


With 11.2 you might hit some object binding limitations that have been removed in more recent versions of Oracle Database. 
</details>

huangapple
  • 本文由 发表于 2023年2月9日 03:38:31
  • 转载请务必保留本文链接：https://go.coder-hub.com/75390929.html
  • express
  • node-oracledb
  • oracle
  • oracle11g
  • stored-procedures
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定