Problem with node-oracledb getting both return and out values from a PL/SQL procedure

Question

Good afternoon, I have this stored function definition in oracle db:

CREATE OR REPLACE FUNCTION GET_INFO(id IN VARCHAR2,
account IN VARCHAR2,
info out INFO_ITEM)
RETURN NUMBER
AS
rv NUMBER:= 0;
...

I am trying to get both return and out values using node-oracledb module:

const sql = `
          DECLARE
          info INFO_ITEM;
          result NUMBER;
          BEGIN 
            :result := GET_INFO(:id, :account, info);                     
          END;
`;
const params = {
          id: '123',
          account: '123',
          result: { dir: BIND_OUT },
        };

const options = {
    outFormat: oracledb.OUT_FORMAT_OBJECT,
};

oracleConnection.execute(sql, params, options)

I am getting the query result fine ({outBinds: {result: 1}}), but have troubles with figuring out how to get the output values. I am pretty new to PL/SQL queries, so if someone could give me an advice on how to approach this I would greatly appreciate it.

Answer 1

Look at the node-oracledb examples https://github.com/oracle/node-oracledb/blob/master/examples/plsqlfunc.js and https://github.com/oracle/node-oracledb/blob/master/examples/plsqlrecord.js

Also review the node-oracledb documentation.

To start with you need to use another bind variable to return the function result to. The trick is setting the type - I don't know what INFO_ITEM is. Something like this might work:

const sql = `
          BEGIN
            :result := GET_INFO(:id, :account, :info);
          END;
`;
const params = {
          id: '123',
          account: '123',
          info: { dir: oracledb.BIND_OUT, type: "THEOWNINGSCHEMANAME.INFO_ITEM" },
          result: { dir: oracledb.BIND_OUT, type: oracledb.NUMBER }
        };

const options = {
    outFormat: oracledb.OUT_FORMAT_OBJECT,
};

oracleConnection.execute(sql, params, options)

Note, the doc says:

When handling multiple objects of the same type, then use fully qualified names like “MDSYS.SDO_GEOMETRY” instead of “SDO_GEOMETRY”. Alternatively retain, and use, the prototype object returned by connection.getDbObjectClass(). Node-oracledb will cache type information using the type’s fully qualified name as the key to avoid the expense of a round-trip, when possible. Each connection has its own cache.

Another way (which may actually have performance benefits) is to return individual parts of info as scalar bind values. Something like:

const sql = `
          DECLARE
          info INFO_ITEM;
          result NUMBER;
          BEGIN
            :result := GET_INFO(:id, :account, info);
            :v1 := info.whatever;
          END;
`;
const params = {
          id: '123',
          account: '123',
          result: { dir: oracledb.BIND_OUT,  oracledb.NUMBER },
          v1:  { dir: oracledb.BIND_OUT,  oracledb.NUMBER },
        };

const options = {
    outFormat: oracledb.OUT_FORMAT_OBJECT,
};

oracleConnection.execute(sql, params, options)

Regarding terminology, a PL/SQL block is not a query. Queries are things like SELECT statements.