I have used Oracle DBMS pkg for parallel execute and it works fine.

Now i am trying to use to run a simple MERGE stmt. Its giving me all kinds of errors. I tried the same pkg create_chunks_by_rowid and create_chunks_by_number_col all of them errors out.

My question is can it(DBMS_PARALLEL_EXECUTE pkg) handle a merge stmt or what am i doing wrong?
Any help is appreciated.

BTW the merge sql stmt runs fine when run in sql.

Given below is the merge stmt that i tried using and getting errors from the pkg.

CREATE TABLE test_tab (
  id          NUMBER,
  description VARCHAR2(50),
  num_col     NUMBER,
  session_id  NUMBER,
  CONSTRAINT test_tab_pk PRIMARY KEY (id)
);

INSERT /*+ APPEND */ INTO test_tab
SELECT level,
       'Description for ' || level,
       CASE
         WHEN MOD(level, 5) = 0 THEN 10
         WHEN MOD(level, 3) = 0 THEN 20
         ELSE 30
       END,
       SYS_CONTEXT(''USERENV'',''SESSIONID'')
FROM   dual
CONNECT BY level <= 1000000;
COMMIT;

create table test_tab2 as select * from test_tab;

delete from test_tab where rownum<500001;
commit;

exec DBMS_PARALLEL_EXECUTE.drop_task (task_name => 'test_task');
exec DBMS_PARALLEL_EXECUTE.create_task (task_name => 'test_task');

SELECT DBMS_PARALLEL_EXECUTE.generate_task_name
FROM   dual;

set serverout on;
DECLARE
l_stmt  varchar2(32767);
BEGIN
  l_stmt := 'MERGE into test_tab2 b
            using test_tab a
            on (a.id=b.id)
            when matched then 
                update set b.session_id = a.session_id
            when not matched then
                insert (id,description,num_col,session_id)
                values(a.id,a.description,a.num_col,a.session_id)';

  DBMS_PARALLEL_EXECUTE.create_chunks_by_sql(task_name => 'test_task',
                                             sql_stmt  => l_stmt,
                                             by_rowid  => FALSE);
                                            
END;
/

Error report -
ORA-01007: variable not in select list
ORA-06512: at "SYS.DBMS_PARALLEL_EXECUTE", line 373
ORA-06512: at "SYS.DBMS_SQL", line 1641
ORA-06512: at "SYS.DBMS_PARALLEL_EXECUTE", line 324
ORA-06512: at line 13
01007. 00000 -  "variable not in select list"
*Cause:    
*Action: