The availability of Oracle Database 23ai Release Update 7 marks the introduction of several new features for Multilingual Engine (MLE) and JavaScript. As always, you can find the details in Oracle’s JavaScript Developers Guide and the MLE Module API documentation on GitHub.
This post demonstrates the use of Fetch Type Handlers. Fetch Type Handlers have been part of node-oracledb since version 6 and are helpful in many ways.
What are Fetch Type Handlers?
Fetch Type Handlers provide great flexibility in modifying fetched column data. You can specify a Fetch Type Handler for SELECT statements if you have such a requirement.
Technically speaking, a Fetch Type Handler is a function that allows applications to examine and modify queried column data before it is returned to the user. This function is called once for each column being fetched and, therefore, might impact performance if used too generously.
You can find the API references here:
Apart from the mere API reference, the node-oracledb docs also feature a more detailed section about the use of Fetch Type Handlers in SELECT statements, as well as additional use cases.
Let’s have a look at a couple of examples.
Lower-case keys in the result.rows property
If you always wondered if it is possible to have lower-case keys returned by the results.rows property (🔗 to docs) – wonder no more. Using Fetch Type Handlers, you can have them! The result.rows property essentially is an array containing an object per row fetched in MLE/JavaScript’s SQL Driver.
A short example should help clarify the point. Let’s start by creating a demo table:
CREATE TABLE demo_table
AS
SELECT
object_id,
owner,
object_type
FROM
all_objects
FETCH FIRST 10 ROWS ONLY
The following small function optionally implements a Fetch Type Handler to change the column name (each object’s key) to lower-case format:
create function fetch_type_handler_demo (
"useHandler" boolean
) return JSON
as mle language javascript
{{
let options;
if (useHandler) {
options = {
fetchTypeHandler: function (metaData) {
metaData.name = metaData.name.toLowerCase();
}
}
} else {
options = {};
}
const result = session.execute(
`select
*
from
demo_table`,
[],
options
);
return result.rows;
}};
/
If you run this without the Fetch Type Handler, you see the default result:
SQL> select
2 json_serialize(
3 fetch_type_handler_demo(false)
4 pretty
5* ) json_output;
JSON_OUTPUT ______________________________________________________
[
{
"OBJECT_ID" : 139,
"OWNER" : "SYS",
"OBJECT_TYPE" : "EDITION"
},
{
"OBJECT_ID" : 148,
"OWNER" : "SYS",
"OBJECT_TYPE" : "TABLE"
},
... more data ...
]
Note that all object keys are in upper-case.
If, however, you enable the handler, you’ll notice the keys are now in lower-case:
SQL> select
2 json_serialize(
3 fetch_type_handler_demo(true)
4 pretty
5* ) json_output;
JSON_OUTPUT
______________________________________________________
[
{
"object_id" : 139,
"owner" : "SYS",
"object_type" : "EDITION"
},
{
"object_id" : 148,
"owner" : "SYS",
"object_type" : "TABLE"
},
... more data ...
]
That’s neat 🙂
Changing data types on the fly
Changing the data types of a SELECT statement’s rows property on the fly is another everyday use case for Fetch Type Handlers. One of them is the change from JavaScript’s number data type to an Oracle number. Why? Well, the JavaScript Developer’s Guide discusses the topic of JavaScript’s number data type precision, or rather, the loss of it. The topic is complex, perhaps it’s best shown with another example. The latest LTS node container assists in the case you don’t have node installed on your machine. It automatically enters an interactive read-eval-print-loop (REPL) which is nice for testing purposes.
podman run --rm -it --name some-node node:lts-alpine
...
Welcome to Node.js v22.13.1
Type ".help" for more information
> const summand_1 = 0.1
undefined
> const summand_2 = 0.2
undefined
> const sum = summand_1 + summand_2
undefined
> console.log(sum)
0.30000000000000004
undefined
You would have expected the sum of 0.1 and 0.2 to be 0.3. It isn’t when you use JavaScript’s number type. By the way, Mozilla’s Developer Network (MDN) features a great article covering JavaScript’s Number data type, head over there for all the details. And since this is a solved problem, a number of libraries can help. Since this post is about Oracle database, let’s use an Oracle Database feature (its own number data type).
In the following example the Fetch Type Handler is used to change the result’s data type from JavaScript’s number to an Oracle number to preserve precision.
create procedure fetch_type_handler_computation(
"useHandler" boolean
)
as mle language javascript
{{
let options;
if (useHandler) {
options = {
fetchTypeHandler: function(metaData) {
if (metaData.dbType == oracledb.DB_TYPE_NUMBER) {
return {type: oracledb.ORACLE_NUMBER};
}
}
}
} else {
options = {}
}
let result = session.execute(
`select 0.1 as summand_1`,
[],
options
);
const s1 = result.rows[0].SUMMAND_1;
result = session.execute(
`select 0.2 as summand_2`,
[],
options
);
const s2 = result.rows[0].SUMMAND_2;
const sum = s1 + s2;
console.log(`the sum of ${s1} and ${s2} is: ${sum} (fetch type handler used: ${useHandler})`);
}};
/
If you execute the procedure without requesting the use of the Fetch Type Handler, you get JavaScript arithmetic:
exec fetch_type_handler_computation(false)
the sum of 0.1 and 0.2 is: 0.30000000000000004 (fetch type handler used: false)
Pass true to request the use of the Fetch Type Handler, and you get the expected result:
exec fetch_type_handler_computation(true)
the sum of .1 and .2 is: .3 (fetch type handler used: true)
There’s another cool thing at work here: the MLE team provided operator overloading for OracleNumber in Oracle Database 23.5. This is why you can use the + operator to create the sum of 2 OracleNumber variables.
Summary
Fetch Type Handlers, known from node-oracledb, have now been made available to MLE/JavaScript. There are many interesting use cases, including the use of converters, which isn’t covered here.
Happy coding!