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 := q'(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;
/
Output 1


Note: An error occurs when a blank string is passed as input which is validated by DBMS_ASSERT.simple_sql_name function as shown in the output 1 above. Without the DBMS_ASSERT.simple_sql_name function, it would be interpreted as null value as shown in the output 2 below.

Output 2

Using double quotes   Dynamic SQL

Using   Bind Variable


Developer Tips

Oracle registered trademark of Oracle Corporation.

  260