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?

Answer 1

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();