preparing sql dynamically in oracle stored procedure

Question

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.

IN Parameters:

empid in varchar2 || empname IN varchar2 || empsal IN varchar2

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 ) );

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

  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;

Answer 1

Here you are another alternative:

V_SQL_WHERE := null;

  V_SQL :='SELECT EMP_NAME INTO V_empname FROM employee';
    BEGIN

    IF(EMPID IS NOT NULL) THEN
      V_SQL_WHERE := V_SQL_WHERE || ' emp_id='||EMPID;
    END IF; 

    IF(EMPNAME IS NOT NULL) THEN

      IF (V_SQL_WHERE is not null) THEN 
        V_SQL_WHERE := V_SQL_WHERE || ' AND ';
      END IF;

      V_SQL_WHERE := V_SQL_WHERE || ' emp_name='||EMPNAME;
    END IF; 

    IF(V_empsalIS NOT NULL) THEN

      IF (V_SQL_WHERE is not null) THEN 
         V_SQL_WHERE := V_SQL_WHERE || ' AND ';
      END IF; 
      V_SQL_WHERE := V_SQL_WHERE || ' emp_sal=' || empsal;
      V_SQL_WHERE := V_SQL_WHERE || ' AND ACTIVE =''Y''' ;

    END IF; 

    IF (V_SQL_WHERE is not null) then
      V_SQL := V_SQL || ' WHERE ' || V_SQL_WHERE;
    end if;

        EXECUTE IMMEDIATE V_SQL;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      V_check:= '' ;
    END;

Answer 2

Something like this: (Not tested, but just an idea)

BEGIN
    IF EMPID IS NOT NULL
    THEN
        CLAUSE1   := 'EMP_ID = EMPID';
    ELSE
        CLAUSE1   := '1=1';
    END IF;

    IF EMPNAME IS NOT NULL
    THEN
        CLAUSE2   := 'EMP_NAME = EMPNAME';
    ELSE
        CLAUSE2   := '1=1';
    END IF;

    IF EMPSAL IS NOT NULL
    THEN
        CLAUSE3   := 'EMP_SAL = EMPSAL';
    ELSE
        CLAUSE3   := '1=1';
    END IF;

    IF (    EMPSAL IS NULL
        AND EMPNAME IS NULL
        AND EMPID IS NOT NULL )
    THEN
        CLAUSE1   := '1=0';
        CLAUSE2   := '1=0';

        CLAUSE3   := '1=0';
    END IF;

    QUERY_STR   :=
           '    SELECT EMP_NAME INTO V_EMP_NAME FROM EMPLOYEE WHERE'
        || CLAUSE1
        || ' AND '
        || CLAUSE2
        || ' AND '
        || CLAUSE3;
END;

Answer 3

You can create one from scratch, including if's and elses, or you can use a package called plsql-utils, the SQL_BUILDER_PKG. It does the whole work for you.

Have a look on SQL utilities

http://code.google.com/p/plsql-utils/

Eg:

declare
  l_my_query sql_builder_pkg.t_query;
  l_sql varchar2(32000);
begin

  sql_builder_pkg.add_select (l_my_query, 'ename');
  sql_builder_pkg.add_select (l_my_query, 'sal');
  sql_builder_pkg.add_select (l_my_query, 'deptno');
  sql_builder_pkg.add_from (l_my_query, 'emp');
  sql_builder_pkg.add_where (l_my_query, 'ename = :p_ename');
  sql_builder_pkg.add_where (l_my_query, 'sal > :p_sal');
  l_sql := sql_builder_pkg.get_sql (l_my_query);
  dbms_output.put_line (l_sql);
end;

I used it in one project and it was a good tool.