Pretius JS Object to Collection

An Oracle APEX Dynamic Action plugin that converts JavaScript objects into APEX collections for server-side processing and storage.

Preview

Features

• JavaScript to Collection Conversion: Convert JavaScript objects into APEX collections automatically
• Automatic Collection Creation: Creates collections on-demand if they don't exist
• Index-Based Storage: Store objects with custom index identifiers for easy retrieval
• Add or Update: Automatically handles adding new members or updating existing ones
• Timestamp Tracking: Records the timestamp of when each object was added/updated
• CLOB Support: Stores JSON data in CLOB columns for handling large payloads
• Server-Side Processing: Seamlessly integrates with APEX collections for PL/SQL operations
• Wait for Result Support: Synchronous execution with other dynamic actions

Installation

Import via APEX Builder

1. Navigate to Shared Components > Plug-ins
2. Click Import
3. Upload dynamic_action_plugin_pretius_apex_js_object_to_collection.sql
4. Follow the import wizard

Usage

Basic Setup

1. Create a new Dynamic Action on your page
2. Select Pretius JS Object to Collection as the action
3. Configure the plugin attributes:

Configuration Attributes

Attribute	Required	Description
JS Object Name	Yes	JavaScript object variable path (e.g., apex.env, window.myData, myApp.config)
Collection Name	Yes	APEX collection name to store the object (default: JS_OBJECT_COLLECTION)
Index Value	Yes	Unique identifier for this object within the collection (e.g., row number, ID)

Examples

Example 1: Simple Object Storage

Store a basic JavaScript object in an APEX collection:

JavaScript Object:

var employee = {
    empNo: 7839,
    ename: 'KING',
    job: 'PRESIDENT',
    sal: 5000,
    deptno: 10
};

Plugin Configuration:

• JS Object Name: employee
• Collection Name: EMP_DATA
• Index Value: 7839

Server-Side Access (PL/SQL):

SELECT * FROM apex_collections 
WHERE collection_name = 'EMP_DATA' 
AND c001 = '7839';

---

Example 2: Nested Object Storage

Store a complex nested JavaScript object:

JavaScript Object:

var departmentData = {
    department: {
        deptno: 10,
        dname: 'ACCOUNTING',
        loc: 'NEW YORK'
    },
    employees: [
        { empno: 7782, ename: 'CLARK' },
        { empno: 7839, ename: 'KING' },
        { empno: 7934, ename: 'MILLER' }
    ],
    metadata: {
        created: new Date().toISOString(),
        version: '1.0'
    }
};

Plugin Configuration:

• JS Object Name: departmentData
• Collection Name: DEPT_OBJECTS
• Index Value: 10

Retrieve in PL/SQL:

DECLARE
    l_json  CLOB;
    l_data  apex_t_varchar2;
BEGIN
    -- Get the stored JSON from collection
    SELECT clob001
      INTO l_json
      FROM apex_collections
     WHERE collection_name = 'DEPT_OBJECTS'
       AND c001 = '10';
    
    -- Parse and use the JSON
    dbms_output.put_line('Department Data: ' || l_json);
END;
/

---

Example 3: Array of Objects

Store multiple objects in a collection with different indices:

Dynamic Action 1 - Store First Employee:

var emp1 = {
    empno: 7369,
    ename: 'SMITH',
    sal: 800,
    deptno: 20
};

Plugin Config:

• Index Value: 1

Dynamic Action 2 - Store Second Employee:

var emp2 = {
    empno: 7499,
    ename: 'ALLEN',
    sal: 1600,
    deptno: 30
};

Plugin Config:

• Index Value: 2

Retrieve All from Collection:

SELECT seq_id, c001 as index_id, clob001 as json_data, d001 as created_date
FROM apex_collections
WHERE collection_name = 'EMP_OBJECTS'
ORDER BY seq_id;

---

Example 4: Dynamic Page-Level Object

Store user session or page-level configuration:

JavaScript Object (Created from Page Item Values):

var pageConfig = {
    userId: apex.env.USER_ID,
    appId: apex.env.APP_ID,
    pageId: apex.env.APP_PAGE_ID,
    timestamp: new Date().toISOString(),
    sessionId: apex.env.SESSION_ID,
    userPreferences: {
        theme: $('#P1_THEME').val(),
        language: $('#P1_LANGUAGE').val(),
        timezone: $('#P1_TIMEZONE').val()
    }
};

Plugin Configuration:

• JS Object Name: pageConfig
• Collection Name: PAGE_SESSION_CONFIG
• Index Value: apex.env.SESSION_ID

Retrieve Session Configuration:

SELECT json_extract(clob001, '$.userPreferences.theme') as theme,
       json_extract(clob001, '$.userPreferences.language') as language
FROM apex_collections
WHERE collection_name = 'PAGE_SESSION_CONFIG'
AND c001 = :v_session_id;

---

Example 5: Update Existing Collection Entry

Update an existing object in the collection (identified by index):

Initial Load:

var userData = {
    id: 100,
    name: 'John Doe',
    email: 'john@example.com',
    status: 'active'
};

Dynamic Action 1 Configuration:

• Index Value: 100

Later Update (User edits email):

var userData = {
    id: 100,
    name: 'John Doe',
    email: 'john.doe@example.com',  // Updated
    status: 'active',
    lastModified: new Date().toISOString()
};

Dynamic Action 2 Configuration (Same settings):

• Index Value: 100

Result: The plugin automatically updates the existing member instead of creating a duplicate.

Verify the Update:

SELECT c001 as user_id, clob001 as user_data, d001 as last_updated
FROM apex_collections
WHERE collection_name = 'USER_OBJECTS'
AND c001 = '100';

---

Example 6: Form Data Serialization

Serialize form data as an object and store in collection:

JavaScript (on Form Submit):

var formData = {
    formId: 'EMPLOYEE_FORM',
    empNo: $('#P1_EMPNO').val(),
    eName: $('#P1_ENAME').val(),
    job: $('#P1_JOB').val(),
    hireDate: $('#P1_HIRE_DATE').val(),
    salary: $('#P1_SAL').val(),
    commission: $('#P1_COMM').val(),
    deptNo: $('#P1_DEPTNO').val(),
    submittedAt: new Date().toISOString()
};

Plugin Configuration:

• JS Object Name: formData
• Collection Name: FORM_SUBMISSIONS
• Index Value: $('#P1_EMPNO').val()

Insert Data to Database:

INSERT INTO emp (empno, ename, job, hiredate, sal, comm, deptno)
SELECT 
    json_extract(clob001, '$.empNo') as empno,
    json_extract(clob001, '$.eName') as ename,
    json_extract(clob001, '$.job') as job,
    TO_DATE(json_extract(clob001, '$.hireDate'), 'YYYY-MM-DD') as hiredate,
    json_extract(clob001, '$.salary') as sal,
    json_extract(clob001, '$.commission') as comm,
    json_extract(clob001, '$.deptNo') as deptno
FROM apex_collections
WHERE collection_name = 'FORM_SUBMISSIONS'
AND c001 = :p_emp_no;

---

Example 7: APEX Environment Storage

Store environment or application-level data:

JavaScript Object:

var appEnvironment = {
    applicationId: apex.env.APP_ID,
    applicationName: apex.env.APP_ALIAS,
    currentUser: apex.env.USER_ID,
    sessionId: apex.env.SESSION_ID,
    pageId: apex.env.APP_PAGE_ID,
    ajaxIdentifier: apex.env.AJAX_IDENTIFIER,
    serverTimeZone: Intl.DateTimeFormat().resolvedOptions().timeZone,
    isDebugEnabled: apex.env.DEBUG === 'Y'
};

Plugin Configuration:

• JS Object Name: appEnvironment
• Collection Name: APP_ENVIRONMENT
• Index Value: apex.env.APP_ID

---

Example 8: Incremental Data Building with Collection

Build up collection data across multiple actions:

Dynamic Action 1 - Load Initial Data:

var collectionData = {
    step: 'initial_load',
    data: [...],
    timestamp: new Date()
};
// Plugin stores with Index: 1

Dynamic Action 2 - Add More Data:

var collectionData = {
    step: 'incremental_update',
    data: [...],
    timestamp: new Date()
};
// Plugin stores with Index: 2 (different index = new entry)

Result: Multiple entries in the collection, each with different indices.

---

Advanced Usage

Collection Querying

Get all objects from collection:

SELECT seq_id, c001 as index_id, clob001 as json_data, d001 as created_date
FROM apex_collections
WHERE collection_name = 'MY_COLLECTION'
ORDER BY seq_id DESC;

Get specific object by index:

SELECT clob001 as json_data
FROM apex_collections
WHERE collection_name = 'MY_COLLECTION'
AND c001 = :p_index_value;

Extract JSON fields:

SELECT 
    c001 as index_id,
    json_extract(clob001, '$.name') as name,
    json_extract(clob001, '$.email') as email,
    d001 as created_date
FROM apex_collections
WHERE collection_name = 'MY_COLLECTION';

Error Handling

Errors are logged to the browser console:

Pretius Plugin: Object does not exist
Pretius Plugin: JSON stringify error for variable: [variable name]
Pretius Plugin: Load Error for variable: [variable name]

Enable APEX debug mode for server-side debugging information.

Collection Cleanup

Clear collections when no longer needed:

BEGIN
    APEX_COLLECTION.DELETE_COLLECTION('MY_COLLECTION');
END;
/

Delete specific collection entries:

DELETE FROM apex_collections 
WHERE collection_name = 'MY_COLLECTION' 
AND c001 = :p_index_value;
COMMIT;

Technical Details

Plugin Information

• Type: Dynamic Action
• Category: Execute
• Internal Name: PRETIUS_APEX_JS_OBJECT_TO_COLLECTION

Files Structure

plugin/
  ├── dynamic_action_plugin_pretius_apex_js_object_to_collection.sql
  ├── db/
  │   ├── pretius_apex_js_object_to_collection.pks
  │   └── pretius_apex_js_object_to_collection.pkb
  └── server/
      └── pretiusJsObjectToCollection.js

Collection Storage

APEX Collection Structure:

• SEQ_ID - Auto-incrementing sequence ID
• C001 - Index Value (custom identifier)
• CLOB001 - JSON data (serialized JavaScript object)
• D001 - Timestamp of last modification

Browser Compatibility

Compatible with all browsers supported by Oracle APEX.

Performance Considerations

• Large objects are stored in CLOB columns
• Collection data persists for the session
• Consider clearing collections in Page Reset PL/SQL
• Multiple collections can be created as needed

Troubleshooting

Object Not Found

• Verify the JavaScript object exists on the page
• Check browser console for errors
• Ensure object is defined before the dynamic action fires
• Check variable path format (e.g., apex.env not apex.env())

Collection Not Created

• Verify Collection Name attribute is specified
• Check database permissions for APEX_COLLECTION operations
• Enable APEX debug mode to see collection creation messages

JSON Serialization Errors

• Ensure the JavaScript object is JSON-serializable
• Avoid circular references in the object
• Remove functions and non-serializable properties
• Use custom serialization if needed

Empty Collection Entries

• Verify the JavaScript object has actual data
• Check if object is null or undefined before storing
• Console errors will indicate JSON stringify failures

About

Version: 24.2.2
Author: Pretius
GitHub: https://github.com/pretius/Pretius-APEX-JS-Object-To-Collection

Version History

• 24.2.2 (February 2026)

  • Fix for Spinner

• 24.2.1 (February 2026)

  • Initial Version

Initial Version

License

Please refer to the LICENSE file in the repository.

Support

For issues, questions, or contributions, please visit the GitHub repository.

Credits

Built with Oracle APEX best practices and APEX collection APIs.