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.
This article assumes you have the initial setup completed. If not, head back to part 1 and set it up (provided you want to follow along, of course).
Return records from MLE/JavaScript to SQL and PL/SQL
The second scenario discussed in this four-part article series concerns passing records to PL/SQL.Recall the following PL/SQL function from the first article:
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;
/
Note the return type in line 5. You can rewrite the same function in JavaScript. One of the possible implementations makes use of the DbObjectClass, representing the type. You can see the implementation below:
create or replace function get_product_details_mle(
"p_product_id" in number,
"p_warehouse_id" in number
) return soe.orderentry.prod_rec
as mle language javascript
{{
const prod_rec = session.getDbObjectClass("SOE.ORDERENTRY.PROD_REC");
const result = session.execute(
`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
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`,
[
p_product_id,
p_warehouse_id
]
);
return new prod_rec({
PRODUCT_ID: result.rows[0].PRODUCT_ID,
PRODUCT_NAME: result.rows[0].PRODUCT_NAME,
PRODUCT_DESCRIPTION: result.rows[0].PRODUCT_DESCRIPTION,
CATEGORY_ID: result.rows[0].CATEGORY_ID,
WEIGHT_CLASS: result.rows[0].WEIGHT_CLASS,
WARRANTY_PERIOD: result.rows[0].WARRANTY_PERIOD,
SUPPLIER_ID: result.rows[0].SUPPLIER_ID,
PRODUCT_STATUS: result.rows[0].PRODUCT_STATUS,
LIST_PRICE: result.rows[0].LIST_PRICE,
MIN_PRICE: result.rows[0].MIN_PRICE,
CATALOG_URL: result.rows[0].CATALOG_URL,
QUANTITY_ON_HAND: result.rows[0].QUANTITY_ON_HAND
});
}};
/
This little snippet does exactly what the PL/SQL function does, as you can see in this example. It uses a nifty trick offered by the JSON operator to convert a PL/SQL Record to JSON, making it much easier to print.
declare
l_result soe.orderentry.prod_rec;
begin
l_result := GET_PRODUCT_DETAILS_MLE(100,1);
dbms_output.put_line(
json_serialize(
JSON(l_result) pretty
)
);
end;
/
{
"PRODUCT_NAME" : "hd4ayoDoYLMYpRRsgdI",
"PRODUCT_DESCRIPTION" : "TmG EkAJYp50i7W nJewtGZ DijP79g yo2ELKyXlXTCQu00PxN2lNtDES1H 2EI5igZHk1a0uMn0K RpQw9pncxIfXJYCHNwAuQioCxBwk7A4zrN5XfdISZXI9xjn3",
"WEIGHT_CLASS" : 5,
"MIN_PRICE" : 2836,
"QUANTITY_ON_HAND" : 5958329,
"CATALOG_URL" : "pnVqLemO",
"CATEGORY_ID" : 14,
"WARRANTY_PERIOD" : "P22Y1M",
"LIST_PRICE" : 2836,
"PRODUCT_STATUS" : "under development",
"PRODUCT_ID" : 100,
"SUPPLIER_ID" : 654610
}
PL/SQL procedure successfully completed.
This way you can easily exchange PL/SQL record types back and forth with MLE/JavaScript.
Happy developing!
Martins Blog 