What is SYS.ODCI (SYS.ODCINumberList or SYS.ODCIVarchar2List) and How Can Use it in APEX ?

⍰  What is SYS.ODCI...?

Oracle database provides predefined collection types, such as SYS.ODCINUMBERLIST and SYS.ODCIVARCHAR2LIST, which simplifies operations involving lists of numbers or strings. 

SYS.ODCI designed for bulk operations and they are nested table types that store lists of numbers and strings, respectively. 

⍰  Can we use SYS.ODCI instead of  TABLE OF Types?

In some cases, you can use SYS.ODCI types like instead of defining your own nested table types. For example, if you need a collection of numbers or strings, you can use these system types instead of defining something like this yourself because User-defined types require explicit definitions. In contrast, SYS.ODCINUMBERLIST and similar predefined types are readily available and can be used both in PL/SQL and SQL contexts without additional definitions. But In general, For more complex structures (like objects with multiple attributes), you must use user-defined types instead of SYS.ODCI types.

TYPE my_num_list IS TABLE OF NUMBER;
l_my_list my_num_list := my_num_list(1, 2, 3);

These types facilitate the handling of collections without the need for user-defined types.👌😘

 👉Let me say some usage and TIPS about it before looking at some examples :  

• You can use these types for Collecting values using BULK COLLECT

• You can use these types for batch inserts or deletes with FORALL

• You can use these types of Direct usage in SQL statements with TABLE()

• You can  Pass lists as these parameters to procedures or functions

⍰  How we can Declare and Use SYS.ODCI...?

Here you see how you can declare list of numbers initialize 

DECLARE
   l_numbers SYS.ODCINUMBERLIST := SYS.ODCINUMBERLIST(10, 20, 30, 40);
--  by this way you declare list of numbers initialize 
 BEGIN
   FOR i IN 1 .. l_numbers.COUNT LOOP
      DBMS_OUTPUT.PUT_LINE('Number: ' || l_numbers(i));
   END LOOP;
END;

in the next example you will see how you can declare list of varchar initialize

DECLARE
   l_names SYS.ODCIVARCHAR2LIST := SYS.ODCIVARCHAR2LIST('Ali', 'Sara', 'Reza');
BEGIN
   FOR i IN 1 .. l_names.COUNT LOOP
      DBMS_OUTPUT.PUT_LINE('Name: ' || l_names(i));
   END LOOP;
END; 


⍰  Can we use BULK COLLECT for SYS.ODCI...?

YES, When you need to retrieve multiple rows from a table into collections, BULK COLLECT can be utilized with these predefined types. For example, in below example, I am saying where department_id is 30, store employee_id, first_name into l_ids and l_names collections

DECLARE
   l_ids    SYS.ODCINUMBERLIST;
   l_names  SYS.ODCIVARCHAR2LIST;
BEGIN
   SELECT employee_id, first_name
   BULK COLLECT INTO l_ids, l_names
   FROM emp
   WHERE department_id = 30;
   FOR i IN 1 .. l_ids.COUNT LOOP
      DBMS_OUTPUT.PUT_LINE('ID: ' || l_ids(i) || ', Name: ' || l_names(i));
   END LOOP;
END;

⍰  Can we use FORALL for SYS.ODCI...? 

FORALL allows for efficient bulk DML operations. For instance, in the below example, you can see deleting employees with IDs 101, 102, and 103 in a single operation.

DECLARE
   l_ids SYS.ODCINUMBERLIST := SYS.ODCINUMBERLIST(101, 102, 103);
BEGIN
   FORALL i IN 1 .. l_ids.COUNT
      DELETE FROM employees WHERE employee_id = l_ids(i);
END;

⍰  How can we select from this collection types?

You can directly use them in SQL with TABLE()

SELECT  *
FROM TABLE(SYS.ODCIVARCHAR2LIST('AAA', 'BBB', 'CC')); 

⍰  How can we Use SYS.ODCI as Predefined Types in Stored Procedures? 

Just simply Passing lists as parameters to stored procedures is straightforward with predefined types. The advantage of Using Predefined Types Over User-Defined Types is that predefined types are ready to use without the need for explicit creation. For example, below you can see a procedure that accepts a list of employee IDs and deletes the corresponding records efficiently using FORALL.

CREATE OR REPLACE PROCEDURE delete_employees(p_ids IN SYS.ODCINUMBERLIST) IS
BEGIN
   FORALL i IN 1 .. p_ids.COUNT
      DELETE FROM employees WHERE employee_id = p_ids(i);
END;

👉Now let's focus on ORACLE APEX💖

⍰ Use SYS.ODCI or APEX_STRING.SPLIT in ORACLE APEX ?

Imagine you're building an Oracle APEX application where you want to collect a list of employee IDs  entered manually by the user and then display a report based on those IDs by querying the employees table. We'll use SYS.ODCINumberList to store and process these IDs.

step1) Add a page item for example a Shuttle where the user can enter IDs

 

select   FIRST_NAME||' '||LAST_NAME  d,EMPLOYEE_ID r

 from MY_EMPLOYEES 

step2) Add a new Interactive Report  

SELECT EMPLOYEE_ID,

       FIRST_NAME,

       LAST_NAME,

       DEPARTMENT_ID,

       SALARY,

       HIRE_DATE

FROM MY_EMPLOYEES

WHERE EMPLOYEE_ID IN (

  SELECT TO_NUMBER(TRIM(COLUMN_VALUE))

  FROM TABLE(

    APEX_STRING.SPLIT(NVL(:P2_EMPLOYERID, '0'), ':') 

  )

);

APEX_STRING.SPLIT for here is perfect, but Suppose you have a very large volume of data.

In Oracle APEX, the selection between SYS.ODCI and APEX_STRING.SPLIT for string parsing is dictated by the intricacy of the text processing required and the specific application needs. For scenarios involving complex pattern matching, regular expression-based data extraction, row by row data manipulation, the necessity for reusable code, high flexibility, seamless SQL integration, or optimized performance for large-scale, complex processing, SYS.ODCI is the preferred option. Conversely, when simple string parsing with basic delimiters is sufficient and prioritization is placed on processing speed and code simplicity, APEX_STRING.SPLIT provides an adequate and efficient solution.

In summary For high-volume data, using SYS.ODCINumberList with FORALL outperforms APEX_STRING.SPLIT for bulk DML operations like updating salaries.

for example Imagine you're building an Oracle APEX application for searching articles or products or something like this and you have a large volume of data. Users can enter a list of keywords in a text field for example :P2_KEYWORDS, and you want to find articles that contain at least one of these keywords in their title or content.

create or replace FUNCTION split(p_keywords VARCHAR2)

RETURN SYS.ODCIVARCHAR2LIST PIPELINED

AS

    v_keyword VARCHAR2(255);

    v_remaining VARCHAR2(4000);

    v_delimiter VARCHAR2(1) := ',';

BEGIN

    v_remaining := p_keywords || v_delimiter;

    WHILE INSTR(v_remaining, v_delimiter) > 0 LOOP

        v_keyword := TRIM(SUBSTR(v_remaining, 1, INSTR(v_remaining, v_delimiter) - 1));

        PIPE ROW(v_keyword);

        v_remaining := SUBSTR(v_remaining, INSTR(v_remaining, v_delimiter) + 1);

    END LOOP;

    RETURN;

END;

Then Create an Interactive report or Grid with below source code

SELECT ARTICLE_ID, TITLE, CONTENT FROM ARTICLES WHERE TITLE LIKE '%' || (SELECT COLUMN_VALUE FROM TABLE(split(:P2_KEYWORDS))) || '%' OR CONTENT LIKE '%' || (SELECT COLUMN_VALUE FROM TABLE(split(:P2_KEYWORDS))) || '%';