Oracle AI Database has been released! It ships with a whole raft of cool new features, including some the Multilingual Engine (MLE) team integrated for JavaScript. This article discusses one of them, support for PL/SQL Records and Collections.
This is a rather wide topic, which is why you find the article broken into a small mini-series.
- Part One: Using Records as input parameters in MLE/JavaScript
- Part Two: returning Records to PL/SQL
- Part Three: Using Collections as input parameters in MLE/JavaScript
- Part Four: returning Collections from MLE/JavaScript to PL/SQL
Up to Release Update 23.26.0, aka the initial 26ai release, it was impossible to exchange PL/SQL records and collections with MLE/JavaScript code. This has all changed now! The MLE/JavaScript documentation has all the details, this article is a mere teaser since you can do a lot more with Record and Collection support than what’s shown here. It is assumed that you know roughly what a PL/SQL Record and PL/SQL Collection are. If not, head over to the documentation for an explanation of both.
Initial Setup
Dominic Giles’ Swingbench Order Entry benchmark deploys a bunch of PL/SQL code, and coincidentally it makes generous use of Records and Collections. This blog post uses the PL/SQL code for inspiration. If you want to follow along, you need
- An Oracle Database 26ai instance (maybe using a container image?)
- Oracle REST Data Services (ORDS) 25.3.1 (you guessed it, there’s a container image, too)
- Swingbench installed in freepdb1
You can spin a database instance up as follows; it’s used for the purpose of this article, nothing is preserved – once you kill the process, everything with it goes to /dev/null and won’t come back, ever. Adjust if needed, you have been warned!
git clone https://github.com/martincarstenbach/javascript-blogposts.git
cd javascript-blogposts/database
podman compose -f compose-podman-ords-apex.yml -p database up -d
Next up, download Swingench and install it in the database by invoking oewizard. Be sure to select a scale of 0.1 or else your FREE database might not provide enough space. Stick with the defaults where possible and install the schema objects into the USERS tablespace. Use this for a lights-out installation from your host, make sure to make the password available as environment variables (or replace them within the following command):
./oewizard \
-cl -create -cs localhost/freepdb1 \
-allindexes -dbap "${ORACLE_PASSWORD}" \
-u soe -p "${SOE_PWD}" \
-ts users -part -scale "0.1" -v
Once Swingbench is installed into the SOE schema, you need to create a new database user: demouser. Grant it the db_developer_role and about 100m of space on the USERS tablespace, plus a few needed extra privileges, then you’re good to go. You should connect to freepdb1 as SYSTEM or another DBA and execute these commands in your playground environment:
alter user demouser quota 100m on users;
grant select on soe.products to demouser;
grant select on soe.inventories to demouser;
grant execute on soe.orderentry to demouser;
The compose file took care of the ORDS installation and configuration – the entire process is hands-off. That’s it, you are set up!
JavaScript support for PL/SQL records
Let’s use the definition of types prod_rec in SOE’s ORDERENTRY package header for the first example. It essentially combines product information, stored in the products and the quantity of a product at hand, found in the inventories table. Here’s an except from the code (the TYPE is defined in the package header)
type prod_rec
is
record
(
product_id products.product_id%type,
product_name products.product_name%type,
product_description products.product_description%type,
category_id products.category_id%type,
weight_class products.weight_class%type,
warranty_period products.warranty_period%type,
supplier_id products.supplier_id%type,
product_status products.product_status%type,
list_price products.list_price%type,
min_price products.min_price%type,
catalog_url products.catalog_url%type,
quantity_on_hand inventories.quantity_on_hand%type
);
Swingbench goes on to define a collection based on the record type, called prod_tab, however that’s not needed for this example. A more generic use of the type is to fetch just one product’s details, like so (the procedure is owned by demouser).
create or replace function get_product_details(
p_product_id in soe.products.product_id%type,
p_warehouse_id in soe.inventories.warehouse_id%type
)
return soe.orderentry.prod_rec
as
l_product_data soe.orderentry.prod_rec;
begin
select
p.product_id,
p.product_name,
p.product_description,
p.category_id,
p.weight_class,
p.warranty_period,
p.supplier_id,
p.product_status,
p.list_price,
p.min_price,
p.catalog_url,
i.quantity_on_hand
INTO l_product_data
from
soe.products p,
soe.inventories i
where
p.product_id = p_product_id
and i.product_id = p.product_id
and i.warehouse_id = p_warehouse_id;
return l_product_data;
end;
/
With the function created let’s use it in MLE/JavaScript – notice that it returns a PL/SQL Record!
A hypothetical example is to validate the information provided by a prod_rec in an upstream system. To this effect, a little bit of ORDS magic, together with MLE code is needed. Here is the JavaScript code. It will receive the output from get_product_details of type SOE.ORDERENTRY.PROD_REC and process it. By processing I mean invoke a REST endpoint via a POST request and check the response. The nice thing about the MLE implementation handling Records is that they translate to JavaScript objects: super-easy to work with!
import "mle-js-fetch";
export async function processProductData(productInfo) {
if (Object.keys(productInfo).length === 0) {
throw new Error('Input data cannot be empty');
}
if (typeof productInfo !== 'object' || Array.isArray(productInfo)) {
throw new Error('Input data must be a non-array object');
}
// check downstream system for product availability and validation
// this is defined in REST_HANDLER_MODULE
try {
const response = await fetch('http://some-ords:8080/ords/demouser/api/v1/validate/', {
method: 'POST',
headers: {
'Content-Type': 'application/json'
},
body: JSON.stringify(productInfo)
});
if (!response.ok) {
throw new Error(`Downstream validation failed, server responded with status ${response.status}`);
}
const data = await response.json();
console.log(`status: ${response.status} message: ${data.message}`);
} catch (error) {
console.error(`Error validating productInfo: ${error}`);
}
}
Refer to my GitHub for the implementation of the REST_HANDLER_MODULE and the ORDS configuration. Before you can proceed with a test, you need to create a Network Access Control Entry (ACE). Connect as SYSDBA to FREEPDB1 and execute this anonymous PL/SQL block:
BEGIN
DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
host => 'some-ords',
ace => xs$ace_type(
privilege_list => xs$name_list('http'),
principal_name => 'DEMOUSER',
principal_type => xs_acl.ptype_db
)
);
COMMIT;
END;
/
For your and my convenience I created a PL/SQL wrapper for the MLE function, named process_product_data. With everything in place, you can run a first test:
set serveroutput on
begin
process_product_data(
get_product_details(1,1)
);
end;
/
The validation should fail with message: Data failed validation: PRODUCT_ID must be >= 100.
Summary
This concludes the first part of the series how to use PL/SQL Records in MLE/JavaScript. A few follow-up articles will detail how you can return records to PL/SQL from within MLE/JavaScript and repeat the same exercise with PL/SQL collections. Refer to the TOC for the next article in the series.
Happy developing!
Martins Blog 