title: preparing sql dynamically in oracle stored procedure
body: <p>I have 3 input parameters,based on values of each input parameter i have to prepare the SQL in procedure dynamically. I am doing below way but it is failing, if the parameter value is null,then i have to exclude that from the where clause.</p>
  
  <p><strong>IN Parameters:</strong></p>
  
  <blockquote>
    <p>empid in varchar2 || empname IN varchar2 || empsal IN varchar2</p>
  </blockquote>
  
  <pre><code>SELECT
        EMP_NAME
  INTO
        V_EMP_NAME
  FROM
        EMPLOYEE
  WHERE
           ( EMP_ID = EMPID
            OR ( EMP_ID IS NULL
               AND EMPID IS NULL ) )
        AND ( EMP_NAME = EMPNAME
            OR ( EMP_NAME IS NULL
               AND EMPNAME IS NULL ) )
        AND ( EMP_SAL = EMPSAL
            OR ( EMP_SAL IS NULL
               AND EMPSAL IS NULL ) );
  </code></pre>
  
  <p>After updates i modified the query like below,it is compiled but giving run time errors saying that ORA-00933: SQL command not properly ended on just before EXECUTE IMMEDIATE</p>
  
  <pre><code>  V_SQL :='SELECT EMP_NAME INTO V_empname FROM employee WHERE ';
      BEGIN
      IF(EMPID IS NOT NULL) THEN
       V_SQL := V_SQL || ' emp_id='||EMPID;
      END IF; 
      IF(EMPNAME IS NOT NULL) THEN
        V_SQL := V_SQL || ' AND emp_name='||EMPNAME;
      END IF; 
      IF(V_empsalIS NOT NULL) THEN
        V_SQL := V_SQL || ' AND  emp_sal='||empsal;
   V_SQL := V_SQL ||' AND ACTIVE =''Y''' ;
      END IF; 
          EXECUTE IMMEDIATE V_SQL;
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        V_check:= '' ;
      END;
  </code></pre>