sqloracle-database

How to set degree of parallelism dynamically when using explicit cursor?


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.


Solution

  • 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';
    [...]