Is there something like Python's list zip function for collections in Oracle SQL?

Question

While refactoring some complex PL/SQL packages for performance, several times I had the following situation:

Input: Two PL/SQL VARRAYs A and B or TABLEs of the same length (for example, SYS.ODCIVarchar2List), e.g.

A := SYS.ODCIVarchar2List('Summer', 'Winter');
B := SYS.ODCIVarchar2List('hot', 'cold');

(The entries in the lists at a given index correspond to each other).

Now, I have a more or less complex SQL statement (e.g. a MERGE INTO) where I need the hypothetical "zip(A,B)" as input.

The only solution I have found until now is a construct like this:

with t1 as (select rownum rn, column_value as season from table(:A))
   , t2 as (select rownum rn, column_value as temperature from table(:B))
   , input as (select t1.season, t2.temperature
               from t1, t2
               where t1.rn = t2.rn
              )
-- Now do something with the input. For demonstration purpose, just show it:
select * from input;

This works, but it seems overly complicated for such a simple task.

Is there a better solution?

Remark: I know about the theoretical uncertainty regarding the order of "select from table(...)". This has always worked, so let's not discuss that here until one day someone can show an example where it doesn't.

As a side note (and to clarify the question further): In Python, I can do this:

L=[1,2]
M=["A","B"]
list(zip(L,M))

and this returns a list of the same length as L and M with the entries combined:

[(1, 'A'), (2, 'B')]

What I need is something like this in SQL.

"The entries in the lists at a given index correspond to each other". SQL tables represent *unordered* sets. There is no correspondence unless columns contain that information. I would recommend looping through the varrays in PL/SQL code so the ordering is preserved. — Gordon Linoff, Feb 13 '20 at 16:29
@GordonLinoff - Those aren't SQL tables but PL/SQL variables of datatype VARRAY. Oracle guarantees the order of elements in a VARRAY (unlike nested tables) so this pretty safe. — APC, Feb 13 '20 at 18:28
@APC . . . Does Oracle explicitly make that guarantee when they are converted to tables? — Gordon Linoff, Feb 13 '20 at 19:14
As I explicitly wrote in my question: I know about this and that's not something I want discussed here. — hvb, Feb 14 '20 at 10:13

score 0 · Answer 1 · answered May 25 '22 at 06:53

This could be done in SQL using MODEL clause. It is a bit tricky but quite effective ... Here is the code:

WITH
    lists AS
        (
            Select 'Summer, Winter' "L", 'Hot, Cold' "M" From Dual 
        ),
    mdl AS
        (
            SELECT
                INDX, L, M,
                L_LIST,
                M_LIST
            FROM
                lists
            MODEL
                DIMENSION BY (0 as INDX)
                MEASURES (L, M, REPLACE(L, ', ', ',') as L_LIST, REPLACE(M, ', ', ',') as M_LIST)
                RULES ITERATE(2)
                    (
                        L[ITERATION_NUMBER + 1] = SubStr(L_LIST[ITERATION_NUMBER], 1, CASE WHEN InStr(L_LIST[ITERATION_NUMBER], ',') = 0 THEN 100
                                                                                ELSE InStr(L_LIST[ITERATION_NUMBER], ',', ITERATION_NUMBER + 1) - 1 
                                                                                END),
                        L_LIST[ITERATION_NUMBER + 1] = SubStr(REPLACE(L_LIST[ITERATION_NUMBER], L[ITERATION_NUMBER + 1], ''), 2),
                        M[ITERATION_NUMBER + 1] = SubStr(M_LIST[ITERATION_NUMBER], 1, CASE WHEN InStr(M_LIST[ITERATION_NUMBER], ',') = 0 THEN 100
                                                                                ELSE InStr(M_LIST[ITERATION_NUMBER], ',', ITERATION_NUMBER + 1) - 1 
                                                                                END),
                        M_LIST[ITERATION_NUMBER + 1] = SubStr(REPLACE(M_LIST[ITERATION_NUMBER], M[ITERATION_NUMBER + 1], ''), 2)
                    )       
        ),
    combined AS
        (
            SELECT
                INDX,
                SubStr(LISTAGG(L || ',', ',') WITHIN GROUP (ORDER BY INDX) || ' ' || LISTAGG(M || ',', ',') WITHIN GROUP (ORDER BY INDX),
                        1, Length(LISTAGG(L || ',', ',') WITHIN GROUP (ORDER BY INDX) || ' ' || LISTAGG(M || ',', ',') WITHIN GROUP (ORDER BY INDX)) - 1) "LIST"
            FROM
                mdl
            WHERE
                INDX > 0
            GROUP BY
                INDX, L, M
        )
SELECT
    '[' || LISTAGG('(' || LIST || ')', ', ') WITHIN GROUP (ORDER BY INDX) || ']' "ZIPPED_LIST"
FROM
    combined
-- 
-- Result
-- 
-- ZIPPED_LIST
-- [(Summer, Hot), (Winter, Cold)]

As you can see, the result is like Python's lisst of tuples but you can format it any way you want. If you run mdl cte alone you'll get the picture of how it works. MODEL clause lets you play with the data creating your own formulas in RULES part and addressing different rows to get the data for that formulas. More about it with examples at https://www.oracle.com/webfolder/technetwork/tutorials/obe/db/10g/r2/prod/bidw/sqlmodel/sqlmodel_otn.htm — d r, May 25 '22 at 07:11
This is interesting, but much more complex than the possible solution I showed in the question. — hvb, May 27 '22 at 09:13
Well, it's true, but this one gives so much different posibilities. I took it out from one of my functions - the very opposite one actualy, the Split(csv_str, delimiter) function where the "mdl" part is the main cursor. The rest is here as one of the posibilities to use it. A bit tricky, yes - but on the other side - written once used so many times. Regards.. — d r, May 27 '22 at 17:15

Is there something like Python's list zip function for collections in Oracle SQL?

1 Answers1