Calling Oracle procedure in Node JS returns an empty array

Question

Env. Oracle 21c, Node v20

The problem is that the parameter values are not being passed from the client to the server. However, with hard-coded values results are returned. Help greatly appreciated. Thanks!

Function roughly outlined here.

const oracledb = require('oracledb');

const mypw = ... // the hr schema password

async function getRecords(req, res) {
    let connection;

    // client inputs
    let p1 = req.query.p1;
    let p2 = req.query.p2;

    try {
        connection = await oracledb.getConnection({
            user: "myuser",
            password: mypw,
            connectString: "localhost/DB1"
        });

        const result = await connection.execute(
            `BEGIN myproc(:p1, :p2, :p_cursor); END;`,
            { // bind variables
                p1: { dir: oracledb.BIND_IN, type: oracledb.STRING },
                p2: { dir: oracledb.BIND_IN, type: oracledb.STRING },
                p_cursor: { dir: oracledb.BIND_OUT, type: oracledb.CURSOR },
            },
            [],
        );
        console.log(result.rows);
        res.send(result.rows); // <-- empty array

    } catch (err) { // catches errors in getConnection and the query
        res.send(err);
    } finally {
        if (connection) {   // the connection assignment worked, must release
            try {
                await connection.release();
            } catch (e) {
                console.error(e);
            }
        }
    };
}

Created a function as described above. Tested in Postman with query parameters. Records are in the database, so I expected that data to be returned.

This question is similar to: Oracle and Nodejs - can't bind parameters in SQL statement. If you believe it’s different, please edit the question, make it clear how it’s different and/or how the answers on that question are not helpful for your problem. — Markus
– Markus, Commented Aug 7, 2024 at 5:49
See docs: node-oracledb.readthedocs.io/en/latest/user_guide/… — Markus
– Markus, Commented Aug 7, 2024 at 5:54

Christopher Jones · Accepted Answer · 2024-08-09 21:56:20Z

1

Look at the node-oracledb REF CURSOR documentation or example examples/refcursor.js. This shows that to access the returned cursor you need to reference the returned bind variable. Results can't be in result.rows because a PL/SQL procedure could have returned multiple REF CURSORS. (Only SELECT and WITH statements will return rows in result.rows).

For a PL/SQL procedure do something like:

    const result = await connection.execute(
      `BEGIN
         no_get_rs(:maxid, :cursor);
       END;`,
      {
        maxid: 3,
        cursor: { type: oracledb.CURSOR, dir: oracledb.BIND_OUT }
      });

    const resultSet1 = result.outBinds.cursor;
    const rows1 = await resultSet1.getRows();  // no parameter means get all rows
    console.log(rows1);

    await resultSet1.close();                  // always close the ResultSet

answered Aug 9, 2024 at 21:56

Christopher Jones

11.1k7 gold badges32 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Fred19 Over a year ago

Thanks for the example! Will go through the documentation.

kfinity · Accepted Answer · 2024-08-05 15:16:05Z

0

You're not actually passing the js vars p1 and p2 into the bind variables anywhere - it needs a val: argument. If you give the js variables different names, like input_p1, it might be more obvious? See the docs for more examples.

{ // bind variables
    p1: { dir: oracledb.BIND_IN, type: oracledb.STRING, val: p1 },
    p2: { dir: oracledb.BIND_IN, type: oracledb.STRING, val: p2 },
    p_cursor: { dir: oracledb.BIND_OUT, type: oracledb.CURSOR },
},

answered Aug 5, 2024 at 15:16

kfinity

9,1411 gold badge15 silver badges22 bronze badges

3 Comments

Fred19 Over a year ago

Thanks for the hint. Even with the "val" set to the parameter name still no data being returned. I console logged the parameters to see if the values are present and correct. They are.

Fred19 Over a year ago

Would be helpful if the Node-Oracledb documentation would have some examples (not only with hard-coded values).

Christopher Jones Over a year ago

Point taken, but in fact there's no real usage difference between the simple bind samples that have hard coded values and using variables instead of the constants. If you want running examples, check github.com/oracle/node-oracledb/tree/main/examples

Collectives™ on Stack Overflow

Calling Oracle procedure in Node JS returns an empty array

2 Answers 2

1 Comment

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related