Node oracledb execute pl/sql function

Question

I created something like below:

DECLARE
active_ver number;
archived_ver number;
draft_ver number;
ret varchar2(10);
FUNCTION checkStatus(active_ver  IN number, archived_ver IN number, draft_ver 
IN number)
return varchar2 is varchar2(10);
BEGIN
IF (archived_ver > active_ver) then
    IF (draft_ver > archived_ver) then 
         z:='DRAFT';
    ELSE z:='ARCHIVED';
    END IF;
ELSE     z:='ACTIVE';
END IF;
RETURN z;
END;
BEGIN
SELECT NVL(max(version),0) into active_ver from table_1 order by run_id desc;// returns 0 
SELECT NVL(max(version),0) into archived_ver from table_2 order by run_id desc;// returns 16
SELECT NVL(max(version),0) into draft_ver from table_3 order by run_id desc;// returns 11.6
ret = checkStatus(active_ver, archived_ver, draft_ver);
dbms_output.put_line('ret' || ret);
END;

It works well and returns ARCHIVED when I execute it in SQL Developer but when I try to do that with node oracledb then I get ACTIVE :(

This is what I do:

async executeFunction(){

const query = 'DECLARE
active_ver number;
archived_ver number;
draft_ver number;
ret varchar2(10);
FUNCTION checkStatus(active_ver  IN number, archived_ver  IN number, draft_ver IN number)
return varchar2 is varchar2(10);
BEGIN
IF (archived_ver > active_ver) then
    IF (draft_ver > archived_ver) then 
         z:='DRAFT';
    ELSE z:='ARCHIVED';
    END IF;
ELSE     z:='ACTIVE';
END IF;
RETURN z;
END;
BEGIN
SELECT NVL(max(version),0) into :active_ver from table_1 order by run_id desc;// returns 0 
SELECT NVL(max(version),0) into :archived_ver from table_2 order by run_id desc;// returns 16
SELECT NVL(max(version),0) into :draft_ver from table_3 order by run_id desc;// returns 11.6
:ret = checkStatus(active_ver, archived_ver, draft_ver);
dbms_output.put_line('ret' || ret);
END;'

const result = connection.execute(query, 
{
active_ver : {dir: oracledb.BIND_OUT,type: oracledb.NUMBER}.
archived_ver : {dir: oracledb.BIND_OUT,type: oracledb.NUMBER}.
draft_ver : {dir: oracledb.BIND_OUT,type: oracledb.NUMBER}.
ret : {dir: oracledb.BIND_OUT,type: oracledb.STRING}.
});
console.log('max ACTIVE ver: ' + result.outBinds.active_ver);
console.log('max ARCHIVED ver: ' + result.outBinds.archived_ver);
console.log('max DRAFT ver: ' + result.outBinds.draft_ver);
console.log('Output: ' + result.outBinds.ret);
return result.outBinds.ret;
}

In the output I get:

 max ACTIVE ver: 0
 max ARCHIVED ver: 22
 max DRAFT ver:  11.01
 Output:        ACTIVE

My question is why I get a proper value in sql developer which is ARCHIVED but wrong output which is ACTIVE generated by oracle db???

Can you help please?

Where to begin? Full stops instead of commas, missing 'await' call before connection.execute(), strange & wrong PL/SQL usage... Check some node-oracledb examples github.com/oracle/node-oracledb/tree/master/examples — Christopher Jones
– Christopher Jones, Commented May 3, 2019 at 22:17
This is pretty simple. You define function with IN parameters. Then you prepare statement and bind OUT parameters. (Yes, Oracle function may besides return value also return values in OUT parameters). Oracle nodejs lib clearly allows this (I would say it is a bug, not feature). Since all not-bound variables get NULL value by default, your function returns ACTIVE. You need to BIND_IN I would say. — Štefan Oravec
– Štefan Oravec, Commented May 17, 2021 at 8:03

Christopher Jones · Accepted Answer · 2019-05-03 22:25:21Z

2

Start with something like this:

'use strict';

const oracledb = require('oracledb');
let config = require('./dbconfig.js');

async function executeFunction() {

  let connection;

  const fn =
   `CREATE OR REPLACE FUNCTION checkStatus(p_archived_ver in number, p_active_ver in number, p_draft_ver in number) return varchar2 is
      z varchar2(20);
    BEGIN
      IF (p_archived_ver > p_active_ver) then
        IF (p_draft_ver > p_archived_ver) then
          z := 'DRAFT';
        ELSE
          z := 'ARCHIVED';
        END IF;
      ELSE
        z := 'ACTIVE';
      END IF;
    RETURN z;
  END;`;

  const query =
   `DECLARE
      active_ver number;
      archived_ver number;
      draft_ver number;
      ret varchar2(10);
    BEGIN
      SELECT 0  into active_ver from dual; -- returns 0
      SELECT 16 into archived_ver from dual; -- returns 16
      SELECT 11.6  into draft_ver from dual; -- returns 11.6
      :ret := checkStatus(active_ver, archived_ver, draft_ver);
    END;`;

  try {

    connection = await oracledb.getConnection(config);

    await connection.execute(fn);

    const result = await connection.execute(
      query,
      { ret: {dir: oracledb.BIND_OUT,type: oracledb.STRING} });
    console.log('Output: ' + result.outBinds.ret);

  } catch (err) {
    console.error(err);
  } finally {
    if (connection) {
      try {
        await connection.close();
      } catch (err) {
        console.error(err);
      }
    }
  }
}

executeFunction();

answered May 3, 2019 at 22:25

Christopher Jones

11.1k7 gold badges32 silver badges64 bronze badges

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

3 Comments

EdXX Over a year ago

It looks like that in my version I shouldn't use something like :active_ver -> unnecessary colon. SELECT NVL(max(version),0) into active_ver from table_1 order by run_id desc; I had await but forgot to rewrite here. Anyway thanks because you made me aware of it :)

Janaki Narayanan Over a year ago

Whether it is possible to call Procedure instead of writing a procedure ?

Christopher Jones Over a year ago

Yes you can call a PL/SQL procedure. See the node-oracledb example plsqlproc.js. Just wrap the name in BEGIN and END.

roland.takacs · Accepted Answer · 2019-05-03 21:25:07Z

1

In your checkStatus() function the parameters are called x, xx and xxx, but inside the function you use archived_ver, active_ver and draft_ver. I would suggest that you refer to the parameter names inside the function, now x, xx and xxx (however you might want to give more meaningful names. :) )

answered May 3, 2019 at 21:25

roland.takacs

714 bronze badges

1 Comment

EdXX Over a year ago

I've changed that in the issue description but anyway that's true that values are not passed to my function and its stay like :ret = checkStatus(active_ver, archived_ver, draft_ver); instead of :ret = checkStatus(0, 22, 11.1);

Collectives™ on Stack Overflow

Node oracledb execute pl/sql function

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related