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.

Christopher Jones · Accepted Answer · 2020-07-06 07:39:39Z

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.

edited Jul 6, 2020 at 7:39

answered Jul 3, 2020 at 22:26

Christopher Jones

11.1k7 gold badges32 silver badges64 bronze badges

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

2 Comments

Kingusha23 Over a year ago

Thank you very much, the first solution worked as charm!

Christopher Jones Over a year ago

For performance you should change the "type" to be a fully qualified name i.e. include the schema name. This can help reduce trips to the DB and improve performance. I've updated the example. Also you should benchmark my second option.

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related