What does pl/sql procedure invoked from node-oracle db return when there are no records left?

Question

Given a pl/sql procedure that looks similar to the example provided below,

const result = await connection.execute(
      `BEGIN
         :ret := no_func(:p1, :p2, :p3);
       END;`,
      {
        p1: 'Chris', 
        p2: 'Jones',
        p3: { dir: oracledb.BIND_OUT, type: oracledb.NUMBER },
        ret: { dir: oracledb.BIND_OUT, type: oracledb.STRING, maxSize: 40 }
      });

    console.log(result.outBinds);
 

My task is to continuously call the procedure until there are no records left. What is the response of result.outBinds in that case? (or) what condition do I add to the loop to keep invoking the procedure until there are none left?

Help would be much appreciated. Thanks in advance.

Answer 1

Let us assume that you have a function (without side-effects):

CREATE FUNCTION no_func(
  p1 IN  TABLE_NAME.C1%TYPE,
  p2 IN  TABLE_NAME.C2%TYPE,
  p3 OUT TABLE_NAME.C3%TYPE
) RETURN TABLE_NAME.C4%TYPE
IS
  v4 TABLE_NAME.C4%TYPE;
BEGIN
  SELECT c3, c4
  INTO   p3, v4
  FROM   TABLE_NAME
  WHERE  c1 = p1
  AND    c2 = p2
  FETCH FIRST ROW ONLY;

  RETURN v4;
END;
/

and the data:

CREATE TABLE table_name (c1, c2, c3, c4) AS
SELECT 'Chris', 'Jones', 1, 2 FROM DUAL UNION ALL
SELECT 'Chris', 'Jones', 3, 4 FROM DUAL UNION ALL
SELECT 'Chris', 'Jones', 5, 6 FROM DUAL UNION ALL
SELECT 'Amber', 'Abbot', 7, 8 FROM DUAL;

Then if you continually call the function:

DECLARE
  p1  TABLE_NAME.C1%TYPE := 'Chris';
  p2  TABLE_NAME.C2%TYPE := 'Jones';
  p3  TABLE_NAME.C3%TYPE;
  ret TABLE_NAME.C4%TYPE;
BEGIN
  FOR i IN 1 .. 5 LOOP
    ret := no_func(p1, p2, p3);
    DBMS_OUTPUT.PUT_LINE(p3 || ', ' || ret);
  END LOOP;
END;
/

It outputs:

1, 2
1, 2
1, 2
1, 2
1, 2

(Note: the same would happen if you continually called it from NodeJS or any other client application.)

It does not get the next record it just repeats the same first record. Unless your function has side-effects or is relying on some external state stored in a package or something similar (don't do either of those) then when you call the function for the 1^st time then you are likely to get exactly the same result as when you call it for the n^th time.

There is never going to be a case when there is initially a row and then there are no rows left as the function will repeatedly return the initial result.

---

If you call the function and there are no rows to be found:

DECLARE
  p1  TABLE_NAME.C1%TYPE := 'Bella';
  p2  TABLE_NAME.C2%TYPE := 'Baron';
  p3  TABLE_NAME.C3%TYPE;
  ret TABLE_NAME.C4%TYPE;
BEGIN
  ret := no_func(p1, p2, p3);
END;
/

Then you get the exception:

ORA-01403: no data found

---

My task is to continuously call the procedure until there are no records left.

To do that, modify the function so that it returns a cursor and then read all the matching rows from the cursor:

CREATE OR REPLACE FUNCTION no_func(
  p1 IN  TABLE_NAME.C1%TYPE,
  p2 IN  TABLE_NAME.C2%TYPE
) RETURN SYS_REFCURSOR
IS
  cur SYS_REFCURSOR;
BEGIN
  OPEN cur FOR
    SELECT c3, c4
    FROM   TABLE_NAME
    WHERE  c1 = p1
    AND    c2 = p2;

  RETURN cur;
END;
/

Then:

DECLARE
  p1  TABLE_NAME.C1%TYPE := 'Chris';
  p2  TABLE_NAME.C2%TYPE := 'Jones';
  p3  TABLE_NAME.C3%TYPE;
  p4  TABLE_NAME.C4%TYPE;
  cur SYS_REFCURSOR;
BEGIN
  cur := no_func(p1, p2);
  LOOP
    FETCH cur INTO p3, p4;
    EXIT WHEN cur%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(p3 || ', ' || p4);
  END LOOP;
END;
/

Outputs:

1, 2
3, 4
5, 6

fiddle

You can do similar in NodeJS (based on the REF CURSOR Bind Parameters documentation) - untested as I do not have a schema to test NodeJS code against):

const result = await connection.execute(
 `"BEGIN :cursor := no_func(:p1, :p2); END;`,
 {
    p1: "Chris",
    p1: "Jones",
    cursor: { type: oracledb.CURSOR, dir: oracledb.BIND_OUT }
 },
 {
    prefetchRows:   1000, // tune the internal getRow() data fetch performance
    fetchArraySize: 1000
 }
);

const resultSet = result.outBinds.cursor;
let row;
while ((row = await resultSet.getRow())) {
    console.log(row);
}

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