Using Large Objects (LOB) with MLE

A PL/SQL wrapper type is used to handle CLOBs and BLOBs with the MLE JavaScript driver.

Handling large objects such as CLOBs (Character Large Object) and BLOBs (Binary Large Object) with the MLE JavaScript driver differs from the node-oracledb driver. Rather than using a Node.js Stream interface, a PL/SQL wrapper type is used. The wrapper types for BLOBs and CLOBs are called OracleBlob and OracleClob, respectively. They are defined in mle-js-plsqltypes. Most types are exposed in the global scope and can be referenced without having to import the module.

Note:

BFILE, commonly counted among LOBs, is not supported.

See Also:

Server-Side JavaScript API Documentation for more information about mle-js-plsqltypes and the other JavaScript built-in modules

Topics

• Writing LOBs
  An example shows how to initialize and write to a CLOB that is finally inserted into a table.
• Reading LOBs
  An example is used to show how to select a CLOB and then use the fetchInfo property to read the contents of the CLOB as a string.

Writing LOBs

An example shows how to initialize and write to a CLOB that is finally inserted into a table.

Example 7-17 Inserting a CLOB into a Table

This example demonstrates how to insert a CLOB into a table. The table defines two columns: an ID column to be used as a primary key and a CLOB column named "C".

CREATE TABLE mle_lob_example ( 
    id NUMBER GENERATED ALWAYS AS IDENTITY, 
    CONSTRAINT pk_mle_blob_table PRIMARY KEY(id), 
    c  CLOB 
); 

CREATE OR REPLACE PROCEDURE insert_clob
AS MLE LANGUAGE JAVASCRIPT
{{
//OracleClob is exposed in the global scope and does not require
//importing 'mle-js-plsqltypes', similar to how oracledb is available
let theClob = OracleClob.createTemporary(false);

theClob.open(OracleClob.LOB_READWRITE);
theClob.write(
    1,
    'This is a CLOB and it has been inserted by the MLE JavaScript SQL Driver'
);

const result = session.execute(
    `INSERT INTO mle_lob_example(c) VALUES(:theCLOB)`,
    {
        theCLOB:{
            type: oracledb.ORACLE_CLOB,
            dir: oracledb.BIND_IN,
            val: theCLOB
        }
    }
);

//it is best practice to close the handle to free memory
theCLOB.close();
}};
/

CLOBs and BLOBs are defined in mle-js-plsqltypes. Most commonly used types are provided in the global scope, rendering the import of mle-js-plsqltypes unnecessary.

The first step is to create a temporary, uncached LOB locator. Following the successful initialization of the LOB, it is opened for read and write operations. A string is written to the CLOB with an offset of 1. Until this point, the LOB exists in memory. The call to session.execute() inserts the CLOB in the table. Calling the close() method closes the CLOB and frees the associated memory.

Reading LOBs

An example is used to show how to select a CLOB and then use the fetchInfo property to read the contents of the CLOB as a string.

Reading an LOB from the database is no different from reading other columns. Example 7-18 demonstrates how to fetch the row inserted by procedure insert_clob, defined in Example 7-17.

Example 7-18 Read an LOB

CREATE OR REPLACE FUNCTION read_clob(
    "p_id" NUMBER
) RETURN VARCHAR2
AS MLE LANGUAGE JAVASCRIPT
{{
const result = session.execute(
    `SELECT c
     FROM mle_lob_example
     WHERE id = :id`,
    {
        id:{
            type: oracledb.NUMBER,
            dir: oracledb.BIND_IN,
            val: p_id
        }
    },
    {
        fetchInfo:{
            "C": {type: oracledb.STRING}
        },
        outFormat: oracledb.OBJECT
    }
);
if (result.rows.length === 0){
    throw new Error(`No data found for ID ${id}`);
} else {
    for (let row of result.rows){
        return row.C;
    }
}
}};
/

The function read_clob receives an ID as a parameter. It is used in the select statement's WHERE clause as a bind variable to identify a row containing the CLOB. The fetchInfo property passed using session.execute() instructs the database to fetch the CLOB as a string.