Dynamic PL SQL in where condition for different operands

Question

I have three tables, viz:

Mains_Control
Control_Mapping
Control_Details

along with several conditions and columns. But I am performing dynamic operations on the tables columns given below.

I am unable to write a dynamic SQL block in PL-SQL procedure for the below conditions. Could anyone please assist me on this particular scenario.

SELECT * 
FROM CUR_ALL_CONTENTS
WHERE MAINS_CONTROL SWITCH = $SWITCH 
AND ($ATTRIB_COLUMNS = $ATTRIB_VAL 
  OR $ATTRIB_COLUMNS = $ATTRIB_VAL)

The above business condition is this

SELECT * 
FROM CUR_ALL_CONTENTS 
WHERE MAINS_CONTROL_SWITCH = 'TYPE' 
AND (SWITCH_MODE = 'THRUST' OR SWITCH_MODE_GEAR = 'SEC_GEAR')

Here SWITCH attribute value can change, ATTRIB_COLUMNS and ATTRIB_VAL can change.

Mains_Control table has the following columns

    SWITCH     ACTION_CODE     RULE_MAP_1
    TYPE         ON            R1
    TYPE         OFF           R2
    METHOD      HOLD           R3
    METHOD     TERM_IN         R4

Control_Mapping table has the following columns

    RULE_MAP_1     RULE_MAP_2
    R1             M11
    R2             M22
    R3             M33
    R4             M44

Control_Details table has the following columns

    RULE_MAP_2     ATTRIB_COLUMNS     OPERAND     ATTRIB_VAL
    M11              SWTICH_MODE         =          THRUST
    M22           SWITCH_MODE_GEAR       =         SEC_GEAR
    M33             HOLD_RELEASE         <>          END

How do you define whether condition is `$SWITCH AND ($ATTRIB_COLUMNS OR $ATTRIB_VAL)` or `($SWITCH AND $ATTRIB_COLUMNS) OR $ATTRIB_VAL)`? — Wernfried Domscheit, Aug 24 '21 at 08:45
My apologies, that is the business condition. I edited the question. That is the business logic. Some times, it can be. 1. Query 1 SELECT * FROM CUR_ALL_CONTENTS WHERE MAINS_CONTROL_SWITCH = "TYPE AND (SWITCH_MODE = "THRUST" OR SWITCH_MODE_GEAR = "SEC_GEAR") OR 2. Query 2 SELECT * FROM CUR_ALL_CONTENTS WHERE MAINS_CONTROL_SWITCH = "METHOD AND (SWITCH_MODE = "THRUST" OR HOLD_RELEASE <> "END") — Cool_Oracle, Aug 24 '21 at 09:01
You query is totally unclear. Why not `WHERE MAINS_CONTROL_SWITCH = 'TYPE' AND (SWITCH_MODE = 'THRUST' OR HOLD_RELEASE <> "END")`? How do you define `MAINS_CONTROL_SWITCH = 'TYPE'` vs `MAINS_CONTROL_SWITCH = 'METHOD'`? — Wernfried Domscheit, Aug 24 '21 at 11:17
The values for MAINS_CONTROL_SWITCH comes from upstream systems. It can be TYPE or METHOD based on business logic. So, the combinations can be any number. Like the one which I mentioned in my earlier comment. So, I am trying to use dynamic SQL query to fit the requirement. — Cool_Oracle, Aug 24 '21 at 12:20
@WernfriedDomscheit : I tweaked your code a little and added few variables. I was able to achieve my solution. Thanks a lot ! — Cool_Oracle, Aug 31 '21 at 16:25

score 0 · Accepted Answer · answered Aug 24 '21 at 15:28

Still not clear for me how you determine which condition shall be used and when. Let's give this example with hard-coded rule set:

DECLARE
    sqlstr VARCHAR2(30000);
    cur INTEGER;
    res INTEGER;
    refCur SYS_REFCURSOR;

    val Mains_Control.SWITCH%TYPE;

    CURSOR Conditions IS
    SELECT *
    FROM RULE_MAP_2 IN ('M11', 'M22');


BEGIN
    
    cur := DBMS_SQL.OPEN_CURSOR;
    
    sqlstr := 'SELECT * '||CHR(13);
    sqlstr := sqlstr || 'FROM CUR_ALL_CONTENTS '||CHR(13);
    sqlstr := sqlstr || 'WHERE MAINS_CONTROL_SWITCH = :switch AND (';
    FOR aCond IN Conditions LOOP
        sqlstr := sqlstr || aCond.ATTRIB_COLUMNS ||aCond.OPERAND||' :'||RULE_MAP_2 ||' OR '
    END LOOP;
    sqlstr := REGEXP_REPLACE(sqlstr, ' OR ', ')');
    DBMS_OUTPUT.PUT_LINE(sqlStr); --> verify generated statement
    
    DBMS_SQL.PARSE(cur, sqlStr, DBMS_SQL.NATIVE);
    
    SELECT SWITCH
    INTO val
    FROM Mains_Control
    WHERE RULE_MAP_1 = 'R1';
    DBMS_SQL.BIND_VARIABLE(cur, ':switch', sw);
    
    FOR aCond IN Conditions LOOP
        DBMS_SQL.BIND_VARIABLE(cur, ':'||aCond.RULE_MAP_2, aCond.ATTRIB_VAL);
    END LOOP;
    res := DBMS_SQL.EXECUTE(cur);   
    refCur := DBMS_SQL.TO_REFCURSOR(cur);
    
    FETCH refCur BULK COLLECT INTO ...;
    
END;

The code would be simpler without DBMS_SQL.BIND_VARIABLE, however using bind variables is the proper way of doing it.

Dynamic PL SQL in where condition for different operands

1 Answers1