Oracle Database: Dynamic SQL in PL/SQL
The code below is an example of use of dynamic SQL in PL/SQL with bind variable and switching of WHERE clause based on the input value. The DBMS_ASSERT is used to avoid SQL injection risks.

DECLARE
   s_sql VARCHAR2(100);
   i_val VARCHAR2(100) := DBMS_ASSERT.simple_sql_name(UPPER('&p_val'));
   s_wcl VARCHAR2(100);
   o_val DATE;
BEGIN
   s_sql := 'SELECT SYSDATE+DECODE(:i_val,''NEGATIVE'',-1, ''ZERO'',0,''POSITIVE'',1,10) c_date FROM DUAL WHERE ';
    IF i_val = 'NEGATIVE' THEN s_wcl := '-1 <0';
    ELSIF i_val = 'ZERO' THEN s_wcl := '0 >-1';
    ELSIF i_val = 'POSITIVE' THEN s_wcl := '1 >0';
    ELSE s_wcl := '1=1';
    END IF;
   EXECUTE IMMEDIATE s_sql||s_wcl INTO o_val USING i_val;
   DBMS_OUTPUT.PUT_LINE(' i_val -> '||i_val||' -> '||TO_CHAR(o_val,'mm/dd/yyyy'));
END;
/

Back

Oracle registered trademark of Oracle Corporation.