Transforming JSON Arrays from External APIs in Oracle APEX Using MLE JavaScript

Integrating Oracle APEX with external REST APIs has become a standard requirement in modern applications. Whether you're pulling customer data, syncing records, or enriching your internal datasets, APEX makes it easy to call APIs — but processing the JSON you receive is where things often get messy.

Most APIs return arrays of objects, and while PL/SQL can parse them, the code quickly becomes verbose, especially when the JSON is nested or needs transformation.

With Oracle Multilingual Engine (MLE), we now have a powerful new option: ✅ Run JavaScript inside the Oracle Database to reshape JSON before inserting it into tables.

In this post, we’ll walk through a real‑world example using the free public API at:

👉 https://jsonplaceholder.typicode.com/users

This API returns a list of 10 mock users — perfect for demos and testing.

We’ll cover:

1. Calling the API from APEX

2. Receiving an array of JSON objects

3. Using MLE JavaScript to transform the data and insert the cleaned results into a table

Let’s dive in. 😎

The API Response

Calling https://jsonplaceholder.typicode.com/users returns an array like this (shortened for clarity):

[
  {
    "id": 1,
    "username": "Leanne Graham",
    "email": "Sincere@april.biz",
    "address": {
      "city": "Gwenborough"
    }
  },
  {
    "id": 2,
    "username": "Ervin Howell",
    "email": "Shanna@melissa.tv",
    "address": {
      "city": "Wisokyburgh"
    }
  }
]

But your table might expect something like:

USER_ID	USER_NAME	EMAIL	CITY
1	Leanne Graham	sincere@april.biz	Gwenborough
2	Ervin Howell	shanna@melissa.tv	Wisokyburgh

SQL for creating the table, you can run this sql in the sql command in APEX

create table "USERS" (
    "USER_ID"   number,
    "USER_NAME" varchar2(255 char),
    "EMAIL"     varchar2(255 char),
    "CITY"      varchar2(255 char)
);

We’ll use MLE JavaScript to normalize the email, extract the city, and return a clean array.

Let’s get started!

Log in to your APEX Workspace, create a new application using the App Builder, and navigate to the home page. Create a new classic report region, and for the source, choose the Users table.

Create an MLE JavaScript Module to Transform the Array

This module:

• Parses the JSON array

• Extracts the fields we want

• Normalizes email addresses

• Returns a clean JSON array

Log in to your Oracle Apex workspace, navigate to the SQL workshop, and open the SQL command. Make sure you select the right schema, copy and paste the code below.

CREATE MLE MODULE user_transform LANGUAGE JAVASCRIPT AS
export function cleanUsers(payload) {
  const users = JSON.parse(payload);

  const cleaned = users.map(u => ({
    user_id: u.id,
    user_name: u.username,
    email: u.email.trim().toLowerCase(),
    city: u.address.city
  }));

  return JSON.stringify(cleaned);
}
/

CREATE OR REPLACE FUNCTION js_user_transform(pi_json VARCHAR2)
  RETURN VARCHAR2 AS
  MLE MODULE user_transform
  SIGNATURE 'cleanUsers';
/

This is where JavaScript shines — array transformations are simple and expressive.

-- To create and execute MLE Module you will need to grant privileges to the user

GRANT CREATE MLE TO <schema_name>;
GRANT EXECUTE DYNAMIC MLE TO <schema_name>;

Create an APEX application

In your workspace, return to the app builder, create a new application, and then navigate to the home page. Create a classic report region, and for its source, select the Users table that you created before.

In the region attribute, search for the static id attribute and write a report

Call the MLE Module from APEX and insert the cleaned result

On the same page, create a new AJAX process, name it PR_CALL_API, and copy the PLSQL code below

DECLARE
  l_response CLOB;
  l_clean_json JSON;
BEGIN
  l_response := APEX_WEB_SERVICE.make_rest_request(
    p_url         => 'https://jsonplaceholder.typicode.com/users',
    p_http_method => 'GET'
  );

  l_clean_json := JSON(JS_USER_TRANSFORM(
    pi_json=>l_response
  ));

  INSERT INTO users (user_id, user_name, email, city)
  SELECT jt.user_id,
         jt.user_name,
         jt.email,
         jt.city
    FROM JSON_TABLE(
         l_clean_json, '$[*]'
         COLUMNS (
            user_id    NUMBER        PATH '$.user_id',
            user_name  VARCHAR2(100) PATH '$.user_name',
            email      VARCHAR2(200) PATH '$.email',
            city       VARCHAR2(200) PATH '$.city'
          )
        ) jt;
    
    htp.p('{"status":"ok"}');

    EXCEPTION WHEN OTHERS THEN
        htp.p('{"status":"failed"}');
END;

Finally, add a new button in the classic report region, and create an on-click dynamic action on the button. For the true action, choose execute JavaScript code and copy the code below.

apex.server.process(
    'PR_CALL_API',
    {
        success: function (pData) {
            if (pData.status=='ok')
                apex.region('report').refresh()
        },
        dataType: "json"
    }
);

Now, run the application and click on the button 😎.

Why This Approach Is So Effective

1. JavaScript is perfect for JSON

Array transformations are dramatically simpler than PL/SQL equivalents.

2. MLE runs inside the Oracle Database

No external Node.js server, no middle tier, no extra infrastructure

3. APEX stays clean

APEX handles:

• API calls

• UI

• database operations

MLE handles:

• JSON transformation

• normalization

• business logic

4. You get reusable, readable logic

Your transformation code becomes modular and easy to maintain.

🎯 Final Thoughts

In conclusion, leveraging Oracle APEX with MLE JavaScript for processing JSON arrays from external APIs offers a modern and efficient approach to application development. This method simplifies JSON transformations, reduces the complexity of PL/SQL code, and enhances the maintainability of your applications. By integrating APEX for API calls, MLE for JSON processing, and PL/SQL for database operations, developers can create a seamless, scalable, and clean integration pattern. This approach not only modernizes APEX applications but also provides a robust framework for handling complex data transformations, making it an invaluable tool for developers looking to streamline their workflows and improve application performance.

And thanks to free APIs like JSONPlaceholder, you can experiment with this pattern at any time.

---

Referencces

https://docs.oracle.com/en/database/oracle/oracle-database/26/mlejs/introduction-to-mle.html

AI tools were used to help draft portions of this work.