In a procedure, I need to set degree of parallelism dynamically using an explicit cursor.
The reason is some customers have a version of the Oracle that throws a bug when parallels using an index.
I'm trying this code:
[...]
DECLARE
vLicense NUMBER(5);
vParallel VARCHAR2(50);
BEGIN
SELECT LICENSE INTO vLicense FROM LICENSES;
IF vLicense = 1 THEN
vParallel := '/*+ PARALLEL(8) */';
ELSIF vLicense = 2 THEN
vParallel := '/*+ PARALLEL(1) */';
ELSE
vParallel := '/*+ PARALLEL(4) */';
END IF;
DECLARE
CURSOR C_EXAMPLE IS
SELECT vParallel COLUMN_A, COLUMN_B FROM TABLE;
[...]
And this code:
[...]
DECLARE
EXECUTE IMMEDIATE 'CURSOR C_EXAMPLE IS
SELECT ' || vParallel || ' COLUMN_A, COLUMN_B FROM TABLE';
[...]
Both snippets of code don't work.
You can use an explicit cursor, you can open it with a SQL string:
DECLARE
vLicense NUMBER(5);
vParallel VARCHAR2(50);
cur SYS_REFCURSOR;
BEGIN
SELECT LICENSE INTO vLicense FROM LICENSES;
IF VLIC = 1 THEN
vParallel := '/*+ PARALLEL(8) */';
ELSIF VLIC = 2 THEN
vParallel := '/*+ PARALLEL(1) */';
ELSE
vParallel := '/*+ PARALLEL(4) */';
END IF;
OPEN CURSOR cur FOR 'SELECT '||vParallel||' COLUMN_A, COLUMN_B FROM TABLE';
[...]