Optimize Merge query inside n^2 loop

Question

I've got a merge query that needs to be executed one time for each combination of day and sessionType inside the request ArrayList. I am using nativeQuery to execute it.

MERGE INTO TABLE_A A
    USING 
    (
    SELECT
    :description AS DESCRIPTION,
    :sessionType AS SESSION_TYPE,
    :day AS DAY,
    :flag1 AS FLAG1,
    :flag2 AS FLAG2,
    FROM DUAL) AS SOURCE
    ON (SOURCE.DESCRIPTION=
        A.DESCRIPTION AND SOURCE.DAY=
        A.DAY
        )
    WHEN MATCHED THEN
        UPDATE SET 
        FLAG1=SOURCE.FLAG1,
        FLAG2=SOURCE.FLAG2  
    WHEN NOT MATCHED THEN
        INSERT (
        DESCRIPTION,
        SESSION_TYPE,
        DAY,
        FLAG1,
        FLAG2
        )
        VALUES (
        SOURCE.DESCRIPTION,
        SOURCE.SESSION_TYPE,
        SOURCE.DAY,
        SOURCE.FLAG1,
        SOURCE.FLAG2    
        );

Is there a way to plain the source data (two ArrayLists, one with a date range instead a single day, and the other with all the session types), so I can execute the merge just one time? I have been told this can be achieved using a WITH but I have no idea how to do it.

Any help would be appreciated.

Answer 1

1. you can use executeBatch: in this case oracle executes your sql statement on the input array automatically

2. you can bind a collection and use it in table() like this:

MERGE INTO TABLE_A A
    USING 
    (
    SELECT *
    FROM table(:bind_collection)
    ) AS SOURCE
    ON (SOURCE.DESCRIPTION=
        A.DESCRIPTION AND SOURCE.DAY=
        A.DAY
        )
    WHEN MATCHED THEN
        UPDATE SET 
        FLAG1=SOURCE.FLAG1,
        FLAG2=SOURCE.FLAG2  
    WHEN NOT MATCHED THEN
    ...