Using the MLE PL/SQL Foreign Function Interface in APEX

While preparing a talk for the upcoming annual German Oracle User Group conference, DOAG 2025, I noticed I haven’t yet written anything about the using the MLE PL/SQL Foreign Function Interface (or FFI for short) in APEX.

The PL/SQL FFI was introduced in Oracle 23.7 to simplify the way developers (you!) work with PL/SQL. Rather than having to write anonymous PL/SQL blocks as strings another option exists in form of the FFI. I wrote about the FFI when it was introduced, but I have not yet demonstrated how to use it with APEX.

Let’s correct this omission 😀

APEX_DEBUG

APEX_DEBUG is one of my favourite packages, mainly because I’m still learning how to use APEX effectively… I frequently need to debug my code, and I appreciate the APEX debug interface-it’s only natural to use it, too.

Here’s a small example of a page process I wrote. It

• Reads a photo from a BLOB column
• Transforms it to a UInt8Array, a JavaScript built-in designed to handle large objects
• Passes the UInt8Array into the EXIFR library to extract EXIF data from the photo
• Updates a column in a table

WARNING: Please refer to the project’s GitHub for more details about its license and use implications. The article assumes your legal and IT Security departments (as well as any other relevant parties) have agreed that using the module in your code is safe and compliant with your license. Using 3rd party code in your application typically requires specific compliance steps to be completed, which are out of the scope of this article. The EXIFR code base hasn’t been updated in quite a while and might pose security risks.

Implementing the FFI

Let’s have a look at the code irrespective of the implications…

const { default: exifr } = await import ('exifr');
 
// this is where the PL/SQL Foreign Function Interface
// aka FFI shines. The APEX_DEBUG package is resolved,
// and all its member functions can be accessed as if they
// were pure JavaScript methods
const d = plsffi.resolvePackage('APEX_DEBUG');
 
// first it's necessary to grab the BLOB column,
// PHOTOS.PHOTO from the database and get it as
// a UINT8 Array (exifr expects this format)
let result = apex.conn.execute(`
        select
            photo
        from
            photos
        where
            id = :id`,
   {
        id: {
            val: apex.env.P1_ID
        }
   },
   {
        fetchInfo: {
            PHOTO: {
                type: oracledb.UINT8ARRAY
            }
        }
    });
 
d.info(`fetched ${result.rows.length} row(s)`);
 
// if a row was found, try to extract the EXIF data
if (result.rows.length === 1 && result.rows[0].PHOTO.buffer) {
 
  d.info('about to extract the EXIF data from the blob');
  const exifData = await exifr.parse(result.rows[0].PHOTO.buffer);
  d.info('looking good - no error encountered during the EXIF parse operation');
 
  if (!exifData || exifData === undefined) {
    d.info(`sadly no EXIF data was found for image ID ${apex.env.P1_ID}`);
    return;
  }
 
  // now update the EXIF_DATA column
  result = apex.conn.execute(
    `update
      photos
    set
      exif_data = :data
    where
      id = :id`,
    {
      data: {
        type: oracledb.DB_TYPE_JSON,
        val: exifData
      },
      id: {
        val: apex.env.P1_ID
      }
    }
  );
 
  d.info(`${result.rowsAffected} rows have been updated with ${Object.keys(exifData).length} properties`);
   
}

I love it when a plan comes together

And it works … if you enable debugging in APEX, you can see your info() messages in the output. I agree they should probably have been trace() calls, but the concept remains the same.

Summary

The PL/SQL FFI is a great help for any JavaScript developer, as it provides a much more native interface to the comprehensive database API—including your team’s PL/SQL codebase.

Rather than having to wrap calls to PL/SQL in BEGIN … END blocks, with the FFI you resolve the package, function, or procedure using the FFI and use it as a native JavaScript object.

Happy developing!