14. Using Oracle Database Objects and Collections
You can query and insert most Oracle Database objects and collections, with some limitations.
Both the node-oracledb Thin and Thick modes support Oracle Database Objects. The node-oracledb Thin mode does not support Oracle Database Objects that contain LOBs.
14.1. Inserting Objects
Performance-sensitive applications should consider using scalar types
instead of objects. If you do use objects, avoid calling
connection.getDbObjectClass()
unnecessarily, and avoid objects with
large numbers of attributes. Using the type’s fully qualified name can help
performance.
As an example, the Oracle Spatial type SDO_GEOMETRY can easily be used in node-oracledb. Describing SDO_GEOMETRY in SQL*Plus shows:
Name Null? Type
----------------------------------------- -------- ----------------------------
SDO_GTYPE NUMBER
SDO_SRID NUMBER
SDO_POINT MDSYS.SDO_POINT_TYPE
SDO_ELEM_INFO MDSYS.SDO_ELEM_INFO_ARRAY
SDO_ORDINATES MDSYS.SDO_ORDINATE_ARRAY
In Node.js, a call to connection.getDbObjectClass()
returns a
DbObject prototype object representing the
database type:
const GeomType = await connection.getDbObjectClass("MDSYS.SDO_GEOMETRY");
console.log(GeomType.prototype);
This gives:
DbObject {
schema: 'MDSYS',
name: 'SDO_GEOMETRY',
fqn: 'MDSYS.SDO_GEOMETRY',
attributes:
{ SDO_GTYPE: { type: 2010, typeName: 'NUMBER' },
SDO_SRID: { type: 2010, typeName: 'NUMBER' },
SDO_POINT:
{ type: 2023,
typeName: 'MDSYS.SDO_POINT_TYPE',
typeClass: [Object] },
SDO_ELEM_INFO:
{ type: 2023,
typeName: 'MDSYS.SDO_ELEM_INFO_ARRAY',
typeClass: [Object] },
SDO_ORDINATES:
{ type: 2023,
typeName: 'MDSYS.SDO_ORDINATE_ARRAY',
typeClass: [Object] } },
isCollection: false }
The type
value of 2023 corresponds to the oracledb.DB_TYPE_OBJECT
constant. The value 2010 corresponds to oracledb.DB_TYPE_NUMBER
.
Now that the object prototype has been found, an object can be created by passing a JavaScript object to the constructor.
Note
The case of the attributes is important.
Attributes not assigned values will default to null. Extra attributes set that are not present in the database object will be ignored.
const geom = new GeomType(
{
SDO_GTYPE: 2003,
SDO_SRID: null,
SDO_POINT: null,
SDO_ELEM_INFO: [ 1, 1003, 3 ],
SDO_ORDINATES: [ 1, 1, 5, 7 ]
}
);
An alternative to instantiating the whole object at once is to set individual attributes:
const geom = new GeomType();
geom.S_GTYPE = 2003;
. . .
Once created, the DbObject in geom
can then be bound for insertion.
For example, if TESTGEOMETRY was created as:
CREATE TABLE testgeometry (id NUMBER, geometry MDSYS.SDO_GEOMETRY)
Then the INSERT statement would be:
await connection.execute(
`INSERT INTO testgeometry (id, geometry) VALUES (:id, :g)`,
{id: 1, g: geom}
);
Node-oracledb automatically detects the type for geom
.
Insertion can be simplified by setting the bind parameter type
to
the name of the Oracle Database object and passing a JavaScript object
as the bind value:
await connection.execute(
`INSERT INTO testgeometry (id, geometry) VALUES (:id, :g)`,
{
id: 1,
g: {
type: "MDSYS.SDO_GEOMETRY",
val: {
SDO_GTYPE: 2003,
SDO_SRID: null,
SDO_POINT: null,
SDO_ELEM_INFO: [ 1, 1003, 3 ],
SDO_ORDINATES: [ 1, 1, 5, 7 ]
}
}
}
);
For objects that are nested, such as SDO_GEOMETRY is, you only need to give the name of the top level object.
See selectgeometry.js for a runnable example.
When handling multiple objects of the same type, then use fully
qualified names like “MDSYS.SDO_GEOMETRY” instead of “SDO_GEOMETRY”.
Alternatively retain, and use, the prototype object returned by
connection.getDbObjectClass()
. Node-oracledb will cache
type information using the type’s fully qualified name as the
key to avoid the expense of a round-trip, when
possible. Each connection has its own cache.
When the definition of a type changes in the database, such as might
occur in a development environment, you should fully close connections
to clear the object caches used by node-oracledb and the Oracle client
libraries. For example, when using a pool you could use
await connection.close({drop: true})
, or
restart the pool. Then getDbObjectClass()
can be called again to get
the updated type information.
14.2. Fetching Objects
When objects are fetched, they are represented as a DbObject.
Note that LOBs will be represented as Lob objects
regardless of any fetchAsString
, fetchAsBuffer
, or fetchInfo
setting.
If oracledb.dbObjectAsPojo
is set to true, then
queried objects and OUT bind objects are returned as “plain old
JavaScript objects” instead of being database-backed. The setting can
help performance if an object’s attributes are accessed multiple times.
However if only a few object attributes are accessed, or attributes are
accessed once, then it may be more efficient to keep dbObjectAsPojo
false. Setting dbObjectAsPojo
to true also allows applications
to close connections before any attributes are accessed unless LOBs are
involved.
Accessing a DbObject is the same whichever value of dbObjectAsPojo
you use. For example:
result = await connection.execute(`SELECT geometry FROM testgeometry WHERE id = 1`);
o = result.rows[0][0];
console.log(o);
This gives:
[MDSYS.SDO_GEOMETRY] { SDO_GTYPE: 2003,
SDO_SRID: null,
SDO_POINT: null,
SDO_ELEM_INFO: [ 4, 1003, 3 ],
SDO_ORDINATES: [ 4, 8, 5, 9 ] }
The SDO_ELEM_INFO attribute is itself a DbObject. The following code
console.log(o.SDO_ELEM_INFO);
gives:
[MDSYS.SDO_ELEM_INFO_ARRAY] [ 1, 1003, 3 ]
If a DbObject is for an Oracle Database collection, the
dbObject.isCollection
attribute
will be true.
console.log(o.isCollection); // false
console.log(o.SDO_ELEM_INFO.isCollection); // true
For DbObjects representing Oracle collections, methods such as
dbObject.getKeys()
and dbObject.getValues()
can be used:
console.log(o.SDO_ELEM_INFO.getKeys()); // [ 0, 1, 2 ]
console.log(o.SDO_ELEM_INFO.getValues()); // [ 1, 1003, 3 ]
The options fetchAsBuffer
and
oracledb.fetchAsString
do not affect values in
objects queried from the database.
LOBs will be fetched as Lob objects. The
lob.getData()
method is a convenient way to
retrieve the data. Note it is an asynchronous method and requires a
round-trip to the database.
14.3. PL/SQL Collection Types
PL/SQL has three collection types: associative arrays, VARRAY (variable-size arrays), and nested tables. See Collection Types in the Database PL/SQL Language Reference.
14.3.1. PL/SQL Collection Associative Arrays (Index-by)
Arrays can be bound to PL/SQL IN, IN OUT, and OUT parameters of PL/SQL INDEX BY associative array types with integer keys. This Oracle type was formerly called PL/SQL tables or index-by tables.
While you could bind associative arrays via named types as shown in previous examples, it is more efficient to use the method shown below which uses the type of each element, not the name of the associative array type. Note that if you use named types for BIND_IN, then the resulting arrays in PL/SQL will start from index 0. The method shown below results in indexes starting from 1. (Using named type binding for nested tables and VARRAYs results in indexes starting from 1).
Given this table and PL/SQL package:
DROP TABLE mytab;
CREATE TABLE mytab (id NUMBER, numcol NUMBER);
CREATE OR REPLACE PACKAGE mypkg IS
TYPE numtype IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
PROCEDURE myinproc(p_id IN NUMBER, vals IN numtype);
PROCEDURE myoutproc(p_id IN NUMBER, vals OUT numtype);
END;
/
CREATE OR REPLACE PACKAGE BODY mypkg IS
PROCEDURE myinproc(p_id IN NUMBER, vals IN numtype) IS
BEGIN
FORALL i IN INDICES OF vals
INSERT INTO mytab (id, numcol) VALUES (p_id, vals(i));
END;
PROCEDURE myoutproc(p_id IN NUMBER, vals OUT numtype) IS
BEGIN
SELECT numcol BULK COLLECT INTO vals FROM mytab WHERE id = p_id ORDER BY 1;
END;
END;
/
To bind an array in node-oracledb using “bind by name” syntax for
insertion into mytab
use:
const result = await connection.execute(
`BEGIN mypkg.myinproc(:id, :vals); END;`,
{
id: 1234,
vals: { type: oracledb.NUMBER,
dir: oracledb.BIND_IN,
val: [1, 2, 23, 4, 10]
}
});
Alternatively, “bind by position” syntax can be used:
const result = await connection.execute(
`BEGIN mypkg.myinproc(:id, :vals); END;`,
[
1234,
{ type: oracledb.NUMBER,
dir: oracledb.BIND_IN,
val: [1, 2, 23, 4, 10]
}
]);
After executing either of these mytab
will contain:
ID NUMCOL
---------- ----------
1234 1
1234 2
1234 23
1234 4
1234 10
The type must be set for PL/SQL array binds. It can be set to oracledb.STRING, oracledb.DB_TYPE_VARCHAR, oracledb.NUMBER, oracledb.DB_TYPE_NUMBER, oracledb.DB_TYPE_NVARCHAR, oracledb.DB_TYPE_CHAR, oracledb.DB_TYPE_NCHAR, oracledb.DB_TYPE_BINARY_FLOAT, oracledb.DB_TYPE_BINARY_DOUBLE, oracledb.DB_TYPE_DATE, oracledb.DB_TYPE_TIMESTAMP, oracledb.DB_TYPE_TIMESTAMP_LTZ, oracledb.DB_TYPE_TIMESTAMP_TZ or oracledb.DB_TYPE_RAW.
For OUT and IN OUT binds, the
maxArraySize bind property must
be set. Its value is the maximum number of elements that can be returned
in an array. An error will occur if the PL/SQL block attempts to insert
data beyond this limit. If the PL/SQL code returns fewer items, the
JavaScript array will have the actual number of data elements and will
not contain null entries. Setting maxArraySize
larger than needed
will cause unnecessary memory allocation.
For IN OUT binds, maxArraySize
can be greater than the number of
elements in the input array. This allows more values to be returned than
are passed in.
For IN binds, maxArraySize
is ignored, as also is maxSize
.
For IN OUT or OUT binds that are returned as String or Buffer, the maxSize property may be set. If it is not set the memory allocated per string will default to 200 bytes. If the value is not large enough to hold the longest data item in the collection, then a runtime error occurs. To avoid unnecessary memory allocation, do not let the size be larger than needed.
The next example fetches an array of values from a table. First, insert these values:
INSERT INTO mytab (id, numcol) VALUES (99, 10);
INSERT INTO mytab (id, numcol) VALUES (99, 25);
INSERT INTO mytab (id, numcol) VALUES (99, 50);
COMMIT;
With these values, the following node-oracledb code will print
[ 10, 25, 50 ]
.
const result = await connection.execute(
`BEGIN mypkg.myoutproc(:id, :vals); END;`,
{
id: 99,
vals: { type: oracledb.NUMBER,
dir: oracledb.BIND_OUT,
maxArraySize: 10 // allocate memory to hold 10 numbers
}
}
);
console.log(result.outBinds.vals);
If maxArraySize
was reduced to 2
, the script would fail with:
ORA-06513: PL/SQL: index for PL/SQL table out of range for host language array
See Oracledb Constants and execute(): Bind Parameters for more information about binding.
See plsqlarray.js for a runnable example.
14.3.1.1. Associative Array Indexed By PLS_INTEGER
The following example defines an associative array indexed by PLS_INTEGER and a function that returns an associative array of that type.
DROP TABLE mytable;
CREATE TABLE mytable (id NUMBER, numcol NUMBER);
CREATE OR REPLACE PACKAGE mypkg IS
TYPE numtype IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
FUNCTION F1 RETURN numtype;
END;
/
CREATE OR REPLACE PACKAGE BODY mypkg AS
FUNCTION F1 RETURN numtype IS
R numtype;
BEGIN
R(2):=22;
R(5):=55;
RETURN R;
END;
END;
/
To return a map object for collection types indexed by PLS_INTEGER to
get the keys along with values, you can use the dbObject.toMap()
method:
const connection = await oracledb.getConnection({
user : "hr",
password : mypw, // contains the hr schema password
connectString : "localhost/FREEPDB1"
});
const result = await connection.execute(
`BEGIN
:ret := mypkg.f1;
END;`,
{
ret: {
dir: oracledb.BIND_OUT,
type: `mypkg.numtype`
}
});
const res = result.outBinds.ret;
console.log(res.toMap());
This will print:
Map(2) { 2 => 22, 5 => 55 }
14.3.2. PL/SQL Collection VARRAY Types
Given a table with a VARRAY column:
CREATE TYPE playertype AS OBJECT (
shirtnumber NUMBER,
name VARCHAR2(20));
/
CREATE TYPE teamtype AS VARRAY(10) OF playertype;
/
CREATE TABLE sports (sportname VARCHAR2(20), team teamtype);
You can insert values using:
await connection.execute(
`INSERT INTO sports (sportname, team) VALUES (:sn, :t)`,
{
sn: "Hockey",
t:
{
type: "TEAMTYPE",
val:
[
{SHIRTNUMBER: 11, NAME: 'Georgia'},
{SHIRTNUMBER: 22, NAME: 'Harriet'}
]
}
}
);
// Alternatively:
TeamTypeClass = await connection.getDbObjectClass("TEAMTYPE");
hockeyTeam = new TeamTypeClass(
[
{SHIRTNUMBER: 22, NAME: 'Elizabeth'},
{SHIRTNUMBER: 33, NAME: 'Frank'},
]
);
await connection.execute(
`INSERT INTO sports (sportname, team) VALUES (:sn, :t)`,
{
sn: "Hockey",
t: hockeyTeam
});
Querying the table could be done like:
result = await connection.execute(
`SELECT sportname, team FROM sports`,
[],
{
outFormat: oracledb.OUT_FORMAT_OBJECT
}
);
for (row of result.rows) {
console.log("The " + row.SPORTNAME + " team players are:");
for (const player of row.TEAM) {
console.log(" " + player.NAME);
}
}
The output would be:
The Hockey team players are:
Elizabeth
Frank
See selectvarray.js for a runnable example.
14.3.3. PL/SQL Collection Nested Tables
Given a nested table staffList
:
CREATE TABLE bonuses (id NUMBER, name VARCHAR2(20));
CREATE OR REPLACE PACKAGE personnel AS
TYPE staffList IS TABLE OF bonuses%ROWTYPE;
PROCEDURE awardBonuses (goodStaff staffList);
END personnel;
/
CREATE OR REPLACE PACKAGE BODY personnel AS
PROCEDURE awardBonuses (goodStaff staffList) IS
BEGIN
FORALL i IN INDICES OF goodStaff
INSERT INTO bonuses (id, name) VALUES (goodStaff(i).id, goodStaff(i).name);
END;
END;
/
you can call awardBonuses()
like:
plsql = `CALL personnel.awardBonuses(:gsbv)`;
binds = {
gsbv:
{
type: "PERSONNEL.STAFFLIST",
val:
[
{ID: 1, NAME: 'Chris' },
{ID: 2, NAME: 'Sam' }
]
}
};
await connection.execute(plsql, binds);
Similar with other objects, calling
connection.getDbObjectClass()
and using a constructor
to create a DbObject
for binding can also be used.
14.4. PL/SQL RECORD Types
PL/SQL RECORDS can be bound for insertion and retrieval. This example uses the PL/SQL package:
CREATE OR REPLACE PACKAGE seachange AS
TYPE shiptype IS RECORD (shipname VARCHAR2(40), weight NUMBER);
PROCEDURE biggership (p_in IN shiptype, p_out OUT shiptype);
END seachange;
/
CREATE OR REPLACE PACKAGE BODY seachange AS
PROCEDURE biggership (p_in IN shiptype, p_out OUT shiptype) AS
BEGIN
p_out := p_in;
p_out.weight := p_out.weight * 2;
END;
END seachange;
/
Similar to previous examples, you can use a prototype DbObject from
getdbobjectclass()
for binding, or pass an Oracle type name.
Below a prototype object for the SHIPTYPE record is returned from
getDbObjectClass()
and then a new object vessel
is created for a
ship. This is bound for input when calling the BIGGERSHIP procedure. To
retrieve a SHIPTYPE record back from the the PL/SQL, the prototype
object class is passed for the output bind type
:
ShipTypeClass = await connection.getDbObjectClass("SEACHANGE.SHIPTYPE");
vessel = new ShipTypeClass({ SHIPNAME: 'BoatFace', WEIGHT: 1200 });
binds = {
inbv: vessel,
outbv: { type: ShipTypeClass, dir: oracledb.BIND_OUT }
};
result = await connection.execute(`CALL seachange.biggership(:inbv, :outbv)`, binds);
console.log(result.outBinds.outbv.SHIPNAME, result.outBinds.outbv.WEIGHT);
The output shows the increased ship size:
BoatFace 2400
See plsqlrecord.js for a runnable example.
14.5. Inserting or Passing Multiple Objects of the Same Type
You can use executeMany()
with objects. See Binding Objects with
executeMany().
14.6. Oracle Database Object Type Limitations
PL/SQL collections and records can only be bound when both Oracle client libraries and Oracle Database are 12.1, or higher.
PL/SQL Collection associative array (Index-by) types with INDEX BY VARCHAR2, or VARCHAR2 sub-types, cannot be used natively by node-oracledb.
Subclasses of types are not supported.
Oracle objects with REF references are not supported.
Where there is no native support, use a PL/SQL wrapper that accepts types supported by node-oracledb and converts them to the required Oracle Database type.