Looping through PL/SQL collection using oracledb npm library

ONLINECAST

2 min read

·

05-10-2024

11

0

Share

Looping Through PL/SQL Collections Using the oracledb NPM Library

The oracledb library provides a comprehensive way to interact with Oracle databases from Node.js applications. One common task is working with PL/SQL collections, which are dynamic arrays holding data. This article explores how to effectively loop through PL/SQL collections retrieved from your Oracle database using the oracledb library.

Scenario: Retrieving and Processing Employee Data

Let's imagine we have a PL/SQL procedure called get_employees that returns an array of employee information:

CREATE OR REPLACE PROCEDURE get_employees (
    p_department_id IN NUMBER,
    p_employees OUT SYS.ODCIVARCHAR2LIST
)
AS
BEGIN
    SELECT employee_name
    BULK COLLECT INTO p_employees
    FROM employees
    WHERE department_id = p_department_id;
END;
/

We want to retrieve the employee names for department ID 10 using our Node.js application and then process each name individually.

Code Example:

const oracledb = require('oracledb');

async function main() {
  try {
    // Connect to the database
    let connection = await oracledb.getConnection({
      user: "user",
      password: "password",
      connectString: "your_database_connection_string"
    });

    // Define the PL/SQL procedure call
    let procedure = {
      name: "get_employees",
      binds: [
        { name: "p_department_id", type: oracledb.NUMBER, dir: oracledb.IN, val: 10 },
        { name: "p_employees", type: oracledb.OUT, dir: oracledb.OUT, val: [] }
      ]
    };

    // Execute the PL/SQL procedure
    let result = await connection.execute(procedure);

    // Retrieve the employee names from the collection
    let employeeNames = result.outBinds.p_employees.value;

    // Loop through the collection and process each employee
    for (let i = 0; i < employeeNames.length; i++) {
      console.log(`Employee ${i + 1}: ${employeeNames[i]}`);
      // Add your logic here to process each employee name
    }

  } catch (err) {
    console.error(err);
  } finally {
    if (connection) {
      try {
        await connection.close();
      } catch (err) {
        console.error(err);
      }
    }
  }
}

main();

Explanation:

1. Connect to the Database: The code first establishes a connection to the Oracle database using the oracledb library.
2. Define the PL/SQL Procedure: We define the get_employees procedure call with the required parameters. The binds array specifies the input parameter (p_department_id) and the output parameter (p_employees), which will hold the collection of employee names.
3. Execute the PL/SQL Procedure: The execute method calls the procedure and returns a result object.
4. Retrieve the Collection: The output parameter (p_employees) is accessed using the outBinds property of the result object. The value property contains the actual collection data.
5. Loop Through the Collection: A standard for loop is used to iterate through each element of the employeeNames array.
6. Process Each Element: Inside the loop, you can perform operations on each employee name, such as logging it to the console or further processing it.

Key Considerations:

• Data Type: The oracledb library automatically handles the conversion of Oracle data types to JavaScript equivalents. In this case, the SYS.ODCIVARCHAR2LIST collection is mapped to a JavaScript array.
• Error Handling: It's essential to include robust error handling in your code to catch any exceptions thrown during database operations.
• Performance Optimization: When working with large collections, you might consider using asynchronous operations or other optimization techniques to improve performance.

Additional Value:

This example demonstrates a straightforward approach to looping through a PL/SQL collection. For more complex scenarios, you can adapt this process to handle different collection types, nested collections, and other data structures. Remember to consult the oracledb documentation for detailed information on working with various Oracle data types and functions.

References:

• oracledb NPM Package: https://www.npmjs.com/package/oracledb
• Oracle Database Documentation: https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/collections.html