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; |
/ |
Oracle registered trademark of Oracle Corporation.