dynamic evaluation of case-when oracle sql-CodePudding

I'm wondering if it's possible to dynamically evaluate a case-statement in the select statement in Oracle SQL. Take the following pseudo sql code as an example:

WITH temp AS (
    SELECT
        'WHEN ' || when_col || ' THEN ''' || then_value || '''' as case_logic
    FROM some_table
)
SELECT
    case
        {temp.case_logic} -- EVALUATE THIS FOR ALL ROWS IN TEMP DURING RUNTIME
        else 'NA'
    end as case_when,
    table2.*
FROM table2;

I tried implementing the above with placeholders, as follows:

DECLARE
    case_when_logic VARCHAR2(4000 byte);
    plsql_block VARCHAR2(4000 byte);
    
    TYPE case_when_logic_tbl_type IS TABLE OF VARCHAR2(1000 BYTE);
    case_when_logic_tbl case_when_logic_tbl_type;

BEGIN
    
    case_when_logic := q'[SELECT 'WHEN ' | | when_condition_col | | ' THEN ''' | | then_condition_col | | '''' as case_when FROM some_table]';

    execute immediate case_when_logic BULK COLLECT INTO case_when_logic_tbl;

    plsql_block := q'[SELECT CASE :case_when
                                  ELSE 'some other value'
                             END case_when_col,
                             table2.*
                        FROM table2]';

    EXECUTE IMMEDIATE plsql_block USING case_when_logic_tbl;
END;
/

But unfortunately this throws an error:

PLS-00457: expressions have to be of SQL types

Is there a way of accomplishing this? Please provide an example in code, as I have not seen analogous solutions.

CodePudding user response：

The USING clause in execute immediate takes bind variables in positional notation (docs). So you can't pass a associative array. And you can only pass valid bind variables (explained here). No table names, clauses, etc.

You could concatenate the select statement and do something like this:

DECLARE
    case_when_logic VARCHAR2(4000 byte);
    plsql_block VARCHAR2(4000 byte);
    
    TYPE t_emp_tab IS TABLE OF emp.ename%TYPE;
    l_ename_tab t_emp_tab;

BEGIN

    case_when_logic := q'[WHEN ENAME = 'KING' THEN 'BOSS' ELSE 'NOBODY']';

    plsql_block := q'[SELECT
                        CASE ]'||case_when_logic||q'[
                        END                         
                    FROM
                        emp WHERE ename = :name]';

    EXECUTE IMMEDIATE plsql_block BULK COLLECT INTO l_ename_tab USING 'KING';
    
    FOR r IN 1 .. l_ename_tab.COUNT LOOP
       dbms_output.put_line(l_ename_tab(r));
    END LOOP;
END;
/

If you must loop through a number if WHEN x THEN y clauses then contatenate your statement. The statement must be complete when passing it to EXECUTE IMMEDIATE

DECLARE
    plsql_block VARCHAR2(4000 byte);
    
    TYPE vc_tab IS TABLE OF VARCHAR2(4000);
    l_case_when_logic_tab vc_tab;
    
    TYPE t_emp_tab IS TABLE OF emp.ename%TYPE;
    l_ename_tab t_emp_tab;

BEGIN

    l_case_when_logic_tab := vc_tab
      (q'[WHEN JOB = 'PRESIDENT' THEN 'BOSS' ]',
       q'[WHEN JOB = 'MANAGER' THEN 'TRYHARD' ]',
       q'[WHEN JOB = 'SALESMAN' THEN 'STORYTELLER' ]'
      );

    plsql_block := q'[SELECT CASE ]';
    FOR i IN l_case_when_logic_tab.FIRST .. l_case_when_logic_tab.LAST LOOP
      plsql_block := plsql_block || l_case_when_logic_tab(i);
    END LOOP;
    plsql_block := plsql_block ||q'[
                        END                         
                    FROM
                        emp WHERE ename = :name]';
    
    dbms_output.put_line('statement: ');                       
    dbms_output.put_line(plsql_block);                            

    EXECUTE IMMEDIATE plsql_block BULK COLLECT INTO l_ename_tab USING 'KING';
    
    FOR r IN 1 .. l_ename_tab.COUNT LOOP
       dbms_output.put_line(l_ename_tab(r));
    END LOOP;
END;
/