Db2 On Premise and Cloud

 View Only

Extract collection elements by index in DB2? (in comparison to Oracle SQL)

  • 1.  Extract collection elements by index in DB2? (in comparison to Oracle SQL)

    Posted Sun June 19, 2022 04:59 PM
    Edited by User1971 Sun June 19, 2022 05:01 PM
    Comparing functionality in DB2 vs. Oracle:

    With Oracle, we can use collections such as the SYS.ODCIVARCHAR2LIST array datatype:

    SELECT SYS.ODCIVARCHAR2LIST('a', 'b', 'c') FROM DUAL;
    
    Result:
    ODCIVARCHAR2LIST('a', 'b', 'c')
    

    Oracle collections are useful. But in my opinion, there is functionality missing: the syntax of extracting collection elements by index is not supported in Oracle SQL (although it is supported in PL/SQL).

    For example, getting the first element by putting (1) to at the end of SYS.ODCIVARCHAR2LIST('a', 'b', 'c') fails in Oracle SQL:

    SELECT SYS.ODCIVARCHAR2LIST('a', 'b', 'c')(1) FROM DUAL;
    --                                        🡅🡅 
    Error:
    ORA-03001: unimplemented feature
    

    Question:

    For the particular system I'm using, we're (unfortunately) married to Oracle—and can't switch DBs.
    As such, I want to ask Oracle to add the missing functionality mentioned above. I've posted an idea request for it here: Oracle Ideas: Support extracting collection elements by index in SQL. I want to improve that idea by showing an example of a database that does support that functionality.

    Does DB2 support extracting collection elements by index? If so, what is an example?

    Thanks!