A job is an database object, so you need privileges to *create* it. This is *not* the same as execute privs on dbms_scheduler.
But thanks for the test case - we love it when people provide them. We can use it to demonstrate the issue.
SQL> create user demo identified by demo;
User created.
SQL> grant create session, create table, create procedure to demo;
Grant succeeded.
SQL> alter user demo quota 100m on users;
User altered.
SQL>
SQL> conn demo/demo
Connected.
SQL> CREATE TABLE PARALLEL_STAGE(EFFDT DATE, ID NUMBER, SALARY NUMBER);
Table created.
SQL> INSERT INTO PARALLEL_STAGE VALUES ('01-JAN-2016', 100000, 200256);
1 row created.
SQL> INSERT INTO PARALLEL_STAGE VALUES ('01-JAN-2016', 100001, 200259);
1 row created.
SQL> INSERT INTO PARALLEL_STAGE VALUES ('01-JAN-2016', 100001, 20025);
1 row created.
SQL> INSERT INTO PARALLEL_STAGE VALUES ('01-JAN-2016', 100001, 20027);
1 row created.
SQL> INSERT INTO PARALLEL_STAGE VALUES ('01-JAN-2016', 100001, 200227);
1 row created.
SQL> INSERT INTO PARALLEL_STAGE VALUES ('01-FEB-2016', 100001, 20027);
1 row created.
SQL> INSERT INTO PARALLEL_STAGE VALUES ('01-FEB-2016', 100001, 20257);
1 row created.
SQL> INSERT INTO PARALLEL_STAGE VALUES ('01-FEB-2016', 100001, 212257);
1 row created.
SQL> INSERT INTO PARALLEL_STAGE VALUES ('01-FEB-2016', 100001, 32257);
1 row created.
SQL> INSERT INTO PARALLEL_STAGE VALUES ('01-FEB-2016', 100001, 120257);
1 row created.
SQL>
SQL> COMMIT;
Commit complete.
SQL> CREATE TABLE PARALLEL_TARGET(EFFDT DATE, ID NUMBER, SALARY NUMBER);
Table created.
SQL> ALTER TABLE PARALLEL_TARGET ADD CONSTRAINT pt_pk PRIMARY KEY (EFFDT, ID);
Table altered.
SQL> CREATE OR REPLACE PROCEDURE PARALLEL_MERGE(PV_IN_DT NUMBER, PV_IN_DT1 NUMBER)
2 AS
3 CURSOR C1
4 IS
5 SELECT * FROM PARALLEL_STAGE WHERE EFFDT = TO_DATE(PV_IN_DT,'YYYYMMDD');
6
7 TYPE T1 IS TABLE OF C1%ROWTYPE INDEX BY PLS_INTEGER;
8 T2 T1;
9 BEGIN
10
11 OPEN C1;
12 FETCH C1 BULK COLLECT INTO T2;
13 CLOSE C1;
14
15 FORALL INDX IN 1..T2.COUNT
16 MERGE INTO PARALLEL_TARGET PT
17 USING
18 (
19 SELECT
20 T2(INDX).EFFDT EFFDT,
21 T2(INDX).ID PID,
22 T2(INDX).SALARY SAL
23 FROM
24 DUAL
25 ) INQ
26 ON
27 (INQ.EFFDT = PT.EFFDT
28 AND INQ.PID = PT.ID)
29 WHEN MATCHED THEN
30 UPDATE SET
31 PT.SALARY = INQ.SAL
32 WHEN NOT MATCHED THEN
33 INSERT (EFFDT,
34 ID,
35 SALARY)
36 VALUES (INQ.EFFDT,
37 INQ.PID,
38 INQ.SAL);
39 COMMIT;
40 EXCEPTION
41 WHEN OTHERS THEN
42 DBMS_OUTPUT.PUT_LINE('ERR DURING MERGE IS ' || SQLERRM);
43 ROLLBACK;
44 END;
45 /
Procedure created.
SQL> declare
2 l_stmt VARCHAR2(100);
3 begin
4 dbms_parallel_execute.drop_task('ASK_TOM_PARALLEL' );
5 dbms_parallel_execute.create_task('ASK_TOM_PARALLEL');
6 l_stmt := 'SELECT DISTINCT TO_CHAR(EFFDT,''YYYYMMDD''), TO_CHAR(EFFDT,''YYYYMMDD'') FROM PARALLEL_STAGE';
7 DBMS_PARALLEL_EXECUTE.create_chunks_by_sql(task_name => 'ASK_TOM_PARALLEL', sql_stmt => l_stmt, by_rowid => FALSE);
8 end;
9 /
PL/SQL procedure successfully completed.
SQL> begin
2 dbms_parallel_execute.run_task
3 ( task_name => 'ASK_TOM_PARALLEL',
4 sql_stmt => 'begin PARALLEL_MERGE(:start_id, :end_id); end;',
5 language_flag => DBMS_SQL.NATIVE,
6 parallel_level => 2 );
7 end;
8 /
begin
*
ERROR at line 1:
ORA-27486: insufficient privileges
ORA-06512: at "SYS.DBMS_ISCHED", line 146
ORA-06512: at "SYS.DBMS_SCHEDULER", line 288
ORA-06512: at "SYS.DBMS_PARALLEL_EXECUTE", line 357
ORA-06512: at "SYS.DBMS_PARALLEL_EXECUTE", line 418
ORA-06512: at line 2
SQL> conn system/system
Connected.
SQL> grant create job to demo;
Grant succeeded.
SQL> conn demo/demo
Connected.
SQL> begin
2 dbms_parallel_execute.run_task
3 ( task_name => 'ASK_TOM_PARALLEL',
4 sql_stmt => 'begin PARALLEL_MERGE(:start_id, :end_id); end;',
5 language_flag => DBMS_SQL.NATIVE,
6 parallel_level => 2 );
7 end;
8 /
PL/SQL procedure successfully completed.
SQL>
SQL>