node-oracledb

10. PL/SQL Execution

PL/SQL stored procedures, functions and anonymous blocks can be called
from node-oracledb.

10.1 PL/SQL Stored Procedures

The PL/SQL procedure:

CREATE OR REPLACE PROCEDURE myproc (id IN NUMBER, name OUT STRING) AS
BEGIN
  SELECT last_name INTO name FROM employees WHERE employee_id = id;
END;

can be called:

. . .
connection.execute(
  "BEGIN myproc(:id, :name); END;",
  {  // bind variables
    id:   159,
    name: { dir: oracledb.BIND_OUT, type: oracledb.STRING, maxSize: 40 },
  },
  function (err, result)
  {
    if (err) { console.error(err.message); return; }
    console.log(result.outBinds);
  });

The output is:

{ name: 'Smith' }

Binding is required for IN OUT and OUT parameters. It is strongly
recommended for IN parameters. See
Bind Parameters for Prepared Statements.

10.2 PL/SQL Stored Functions

The PL/SQL function:

CREATE OR REPLACE FUNCTION myfunc RETURN VARCHAR2 AS
BEGIN
  RETURN 'Hello';
END;

can be called by using an OUT bind variable for the function return value:

. . .
connection.execute(
  "BEGIN :ret := myfunc(); END;",
  { ret: { dir: oracledb.BIND_OUT, type: oracledb.STRING, maxSize: 40 } },
  function (err, result)
  {
    if (err) { console.error(err.message); return; }
    console.log(result.outBinds);
  });

The output is:

{ ret: 'Hello' }

See Bind Parameters for Prepared Statements for information on binding.

10.3 PL/SQL Anonymous PL/SQL Blocks

Anonymous PL/SQL blocks can be called from node-oracledb like:

. . .
connection.execute(
  "BEGIN SELECT last_name INTO :name FROM employees WHERE employee_id = :id; END;",
  {  // bind variables
    id:   134,
    name: { dir: oracledb.BIND_OUT, type: oracledb.STRING, maxSize: 40 },
  },
  function (err, result)
  {
    if (err) { console.error(err.message); return; }
    console.log(result.outBinds);
  });

The output is:

{ name: 'Rogers' }

See Bind Parameters for Prepared Statements for information on binding.

10.4 Using DBMS_OUTPUT

The
DBMS_OUTPUT
package is the standard way to "print" output from PL/SQL. The way
DBMS_OUTPUT works is like a buffer. Your Node.js application code
must first turn on DBMS_OUTPUT buffering for the current connection by
calling the PL/SQL procedure DBMS_OUTPUT.ENABLE(NULL). Then any
PL/SQL executed by the connection can put text into the buffer using
DBMS_OUTPUT.PUT_LINE(). Finally DBMS_OUTPUT.GET_LINE() is used to
fetch from that buffer. Note, any PL/SQL code that uses DBMS_OUTPUT
runs to completion before any output is available to the user. Also,
other database connections cannot access your buffer.

A basic way to fetch DBMS_OUTPUT with node-oracledb is to bind an
output string when calling the PL/SQL DBMS_OUTPUT.GET_LINE()
procedure, print the string, and then repeat until there is no more
data. The following snippet is based on the example
dbmsoutputgetline.js:

function fetchDbmsOutputLine(connection, cb) {
  connection.execute(
    "BEGIN DBMS_OUTPUT.GET_LINE(:ln, :st); END;",
    { ln: { dir: oracledb.BIND_OUT, type: oracledb.STRING, maxSize: 32767 },
      st: { dir: oracledb.BIND_OUT, type: oracledb.NUMBER } },
    function(err, result) {
      if (err) {
        return cb(err, connection);
      } else if (result.outBinds.st == 1) { // no more output
        return cb(null, connection);
      } else {
        console.log(result.outBinds.ln);
        return fetchDbmsOutputLine(connection, cb);
      }
    });
  }

Another way is to wrap the DBMS_OUTPUT.GET_LINE() call into a
pipelined function and fetch the output using a SQL query. See
dbmsoutputpipe.js for the full example.

The pipelined function could be created like:

CREATE OR REPLACE TYPE dorow AS TABLE OF VARCHAR2(32767);
/
CREATE OR REPLACE FUNCTION mydofetch RETURN dorow PIPELINED IS
  line VARCHAR2(32767);
  status INTEGER;
  BEGIN LOOP
    DBMS_OUTPUT.GET_LINE(line, status);
    EXIT WHEN status = 1;
    PIPE ROW (line);
  END LOOP;
END;
/

To get DBMS_OUTPUT that has been created, simply execute the query
using the same connection:

connection.execute(
  "SELECT * FROM TABLE(mydofetch())",
  [],
  { resultSet: true },
  function (err, result) {
  . . .

The query rows can be handled using a
ResultSet.

Remember to first enable output using DBMS_OUTPUT.ENABLE(NULL).