6. Executing PL/SQL
PL/SQL stored procedures, functions and anonymous blocks can be called
from node-oracledb using execute()
.
6.1. PL/SQL Stored Procedures
The PL/SQL procedure:
CREATE OR REPLACE PROCEDURE myproc (id IN NUMBER, name OUT VARCHAR2, salary OUT NUMBER) AS
BEGIN
SELECT last_name, salary INTO name, salary FROM employees WHERE employee_id = id;
END;
can be called:
const result = await connection.execute(
`BEGIN
myproc(:id, :name, :salary);
END;`,
{ // bind variables
id: 159,
name: { dir: oracledb.BIND_OUT, type: oracledb.STRING, maxSize: 40 },
salary: { dir: oracledb.BIND_OUT, type: oracledb.NUMBER }
}
);
console.log(result.outBinds);
The output is:
{ name: 'Smith', salary: 8000 }
Binding is required for IN OUT and OUT parameters. It is strongly recommended for IN parameters. See Bind Parameters for Prepared Statements.
6.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:
const result = await connection.execute(
`BEGIN
:ret := myfunc();
END;`,
{
ret: { dir: oracledb.BIND_OUT, type: oracledb.STRING, maxSize: 40 }
}
);
console.log(result.outBinds);
The output is:
{ ret: 'Hello' }
See Bind Parameters for Prepared Statements for information on binding.
6.3. PL/SQL Anonymous PL/SQL Blocks
Anonymous PL/SQL blocks can be called from node-oracledb like:
const result = await 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 },
}
);
console.log(result.outBinds);
The output is:
{ name: 'Rogers' }
See Bind Parameters for Prepared Statements for information on binding.
6.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 that 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:
let result;
do {
result = await 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 }
}
);
if (result.outBinds.st === 0)
console.log(result.outBinds.ln);
} while (result.outBinds.st === 0);
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, simply query this function using the same connection that created the output:
const result = await connection.execute(
`SELECT * FROM TABLE(mydofetch())`,
[],
{ resultSet: true }
);
const rs = result.resultSet;
let row;
while ((row = await rs.getRow())) {
console.log(row);
}
The query rows in this example are handled using a ResultSet.
Remember to first enable output using DBMS_OUTPUT.ENABLE(NULL)
.
6.5. Edition-Based Redefinition
The Edition-Based Redefinition (EBR) feature of Oracle Database allows multiple versions of views, synonyms, PL/SQL objects and SQL Translation profiles to be used concurrently. Each item’s version is associated with an ‘edition’ which can be nominated at runtime by applications. This lets database logic be updated and tested while production users are still accessing the original version. Once every user has begun using the objects in the new edition, the old objects can be dropped.
Note
In this release, Edition-Based Redefinition is only supported in the node-oracledb Thick mode. See Enabling node-oracledb Thick Mode.
To choose the edition, node-oracledb applications can set
oracledb.edition
globally, or specify a value
when creating a pool or a
standalone connection.
The example below shows how a PL/SQL function DISCOUNT
can be
created with two different implementations. The initial procedure is
created as normal in the SQL*Plus command line:
CONNECT nodedemo/welcome
-- The default edition's DISCOUNT procedure
CREATE OR REPLACE FUNCTION discount(price IN NUMBER) RETURN NUMBER
AS
newprice NUMBER;
BEGIN
newprice := price - 4;
IF (newprice < 1) THEN
newprice := 1;
END IF;
RETURN newprice;
END;
/
This initial implementation is in the default ‘edition’ ora$base
,
which is pre-created in new and upgraded databases.
The user nodedemo
can be given permission to create new ‘editions’:
CONNECT system
GRANT CREATE ANY EDITION TO nodedemo;
ALTER USER nodedemo ENABLE EDITIONS FORCE;
The next SQL*Plus script creates a new edition e2
, and changes the
current session to use it. A new version of DISCOUNT
is created
under that edition:
CONNECT nodedemo/welcome
CREATE EDITION e2;
ALTER SESSION SET EDITION = e2;
-- E2 edition's discount
CREATE OR REPLACE FUNCTION discount(price IN NUMBER) RETURN NUMBER
AS
newprice NUMBER;
BEGIN
newprice := 0.75 * price;
RETURN newprice;
END;
/
There are now two implementations of the PL/SQL procedure DISCOUNT
with the same prototype. Applications can choose at runtime which
implementation to use. Here is a script that calls DISCOUNT
:
const mypw = ... // set mypw to the nodedemo schema password
const connection = await oracledb.getConnection(
{
user: 'nodedemo',
password: mypw,
connectString: 'localhost/orclpdb1'
}
);
const result = await connection.execute(
`SELECT name, price, DISCOUNT(price) AS discountprice
FROM parts
ORDER BY id`,
[],
{ outFormat: oracledb.OUT_FORMAT_OBJECT }
);
console.log(result.rows);
Since the code does not explicitly set oracledb.edition
(or
equivalent), then the first implementation of DISCOUNT
in the
default edition is used. The output might be like:
[ { NAME: 'lamp', PRICE: 40, DISCOUNTPRICE: 36 },
{ NAME: 'wire', PRICE: 10, DISCOUNTPRICE: 6 },
{ NAME: 'switch', PRICE: 4, DISCOUNTPRICE: 1 } ]
If the connection uses edition e2
, then the second implementation of
DISCOUNT
will be used:
const connection = await oracledb.getConnection(
{
user: 'nodedemo',
password: mypw, // mypw contains the nodedemo schema password
connectString: 'localhost/orclpdb1',
edition: 'e2'
}
);
. . . // same query code as before
The output might be like:
[ { NAME: 'lamp', PRICE: 40, DISCOUNTPRICE: 30 },
{ NAME: 'wire', PRICE: 10, DISCOUNTPRICE: 7.5 },
{ NAME: 'switch', PRICE: 4, DISCOUNTPRICE: 3 } ]
See the Database Development Guide chapter Using Edition-Based Redefinition for more information about EBR.
6.6. Implicit Results
Oracle Implicit Results allow queries in PL/SQL to be returned to Node.js without requiring REF CURSORS or bind variables. Implicit Results requires node-oracledb 4.0, Oracle Database 12.1 or later, and Oracle Client 12.1 or later.
PL/SQL code uses DBMS_SQL.RETURN_RESULT()
to return query results.
These are accessible in the execute()
callback
implicitResults attribute.
For example:
const plsql = `
DECLARE
c1 SYS_REFCURSOR;
c2 SYS_REFCURSOR;
BEGIN
OPEN c1 FOR SELECT city, postal_code
FROM locations
WHERE location_id < 1200;
DBMS_SQL.RETURN_RESULT(c1);
OPEN C2 FOR SELECT job_id, employee_id, last_name
FROM employees
WHERE employee_id < 103;
DBMS_SQL.RETURN_RESULT(c2);
END;`;
result = await connection.execute(plsql);
console.log(result.implicitResults);
will display:
[
[
[ 'Roma', '00989' ],
[ 'Venice', '10934' ],
],
[
[ 'AD_PRES', 100, 'King' ],
[ 'AD_VP', 101, 'Kochhar' ],
[ 'AD_VP', 102, 'De Haan' ],
]
]
For larger query results, fetching ResultSets is recommended:
result = await connection.execute(plsql, [], { resultSet: true });
for (const i = 0; i < result.implicitResults.length; i++) {
console.log(" Implicit Result Set", i + 1);
const rs = result.implicitResults[i]; // get the next ResultSet
let row;
while ((row = await rs.getRow())) {
console.log(" ", row);
}
console.log();
await rs.close();
}
This displays:
Implicit Result Set 1
[ 'Roma', '00989' ]
[ 'Venice', '10934' ]
Implicit Result Set 2
[ 'AD_PRES', 100, 'King' ]
[ 'AD_VP', 101, 'Kochhar' ]
[ 'AD_VP', 102, 'De Haan' ]
A runnable example is in impres.js.
6.7. Creating PL/SQL Procedures and Functions
PL/SQL procedures and functions can easily be created in node-oracledb
by calling connection.execute()
, for example:
await connection.execute(
`CREATE OR REPLACE PROCEDURE no_proc
(p_in IN VARCHAR2, p_inout IN OUT VARCHAR2, p_out OUT NUMBER)
AS
BEGIN
p_inout := p_in || p_inout;
p_out := 101;
END;`
);
See the examples plsqlproc.js and plsqlfunc.js.
6.7.1. PL/SQL Compilation Warnings
When creating PL/SQL procedures and functions (or creating types) in
node-oracledb using SQL statements, the statement might succeed without
throwing an error, but there may be additional informational messages. (These
messages are sometimes known in Oracle as “success with info” messages). Your
application can manually check for these messages using the
warning property of the
result object in connection.execute()
or
connection.executeMany()
. A subsequent query from a table like
USER_ERRORS
will show more details. For example:
const result = await connection.execute(
`CREATE OR REPLACE PROCEDURE badproc AS
BEGIN
INVALID
END;`);
if (result.warning && result.warning.code == "NJS-700")
console.log(result.warning.message)
const r = await connection.execute(
`SELECT line, position, text
FROM user_errors
WHERE name = 'BADPROC' AND type = 'PROCEDURE'
ORDER BY name, type, line, position`,
[], { outFormat: oracledb.OUT_FORMAT_OBJECT }
);
if (r.rows.length) {
console.error(r.rows[0].TEXT);
console.error('at line', r.rows[0].LINE, 'position', r.rows[0].POSITION);
}
In node-oracledb Thin mode, the output would be:
NJS-700: creation succeeded with compilation errors
PLS-00103: Encountered the symbol "END" when expecting one of the following:
:= . ( @ % ;
The symbol ";" was substituted for "END" to continue.
at line 4 position 8
In node-oracledb Thick mode, the output would be:
NJS-700: creation succeeded with compilation errors
ORA-24344: success with compilation error
PLS-00103: Encountered the symbol "END" when expecting one of the following:
:= . ( @ % ;
The symbol ";" was substituted for "END" to continue.
at line 4 position 8