Dynamic Where clause

How Static Where clause can be changed to Dynamic Where clause to accept input at runtime?

Think about a situation where you are not sure if your Query have any filters or now. If you don’t have any filters, then you can make Query static but if in case you have to filter your SQL that means you have to make Static and Dynamic Query (For where clause). The object is to dynamically assemble a statement from a variable number of filters in the WHERE clause.

In this Example, we will use an array to handle the parameters:

SQL> CREATE Type Qry_Param As Object (Col_Name Varchar2(30),Col_Value Varchar(20));

Type created.

SQL> Create Type Qry_Params As Table of Qry_Param;

Type created.

This type is passed to a function, which loops around the array. For each entry in the array it appends a line to the WHERE clause in the format <name> = '<value>'. Probably you will require more sophisticated filtering - different operators, explicit data type conversion, bind variables - but this is the general idea. 

SQL> Create or Replace Function Get_Details (p_args in Qry_Params )
  2  Return Sys_Refcursor
  3  As
  4      stmt varchar2(32767);
  5      rc sys_refcursor;
  6      Begin
  7          stmt := 'Select * From EMP_TEST';
  8          FOR i IN p_args.First()..p_args.Last()
  9          Loop
 10             If i = 1 Then
 11                 stmt := stmt || ' Where ';
 12             Else
 13                 stmt := stmt || ' and ';
 14             End If;
 15             stmt := stmt || p_args(i).col_name||' = '''||p_args(i).col_value||'''';
 16         End loop;
 17         Open rc for stmt;
 18         Return rc;
 19  End;
 20  /

Function created.

Finally, to execute this query we need to populate a local variable of the array type and return the result to a ref cursor. 

SQL> SELECT * From EMP_TEST;

    EMP_NO ENAME          DEPTNO HIREDATE
---------- ---------- ---------- ---------
      1001 Ravi               10 19-AUG-16
      1002 Surya              20 26-DEC-16
      1003 Ankit              30 12-DEC-16
      1004 Nikhil             40 12-DEC-10
      1005 Pritesh            50 19-AUG-16
      1006 Rajan              20 16-AUG-10
      1007 Manu               20 16-AUG-10
      1008 Karan              20 16-AUG-10

8 rows selected.

SQL> Var l_rc Refcursor
SQL> Declare
  2  l_args qry_params := qry_params (qry_param('DEPTNO', '20') , qry_param('HIREDATE', '16-Aug-2010'));
  3  Begin
  4  :l_rc := Get_Details(l_args);
  5  End;
  6  /

PL/SQL procedure successfully completed.

SQL> Print l_rc

    EMP_NO ENAME          DEPTNO HIREDATE
---------- ---------- ---------- ---------
      1006 Rajan              20 16-AUG-10
      1007 Manu               20 16-AUG-10
      1008 Karan              20 16-AUG-10

Get involved and leave your Comments in the Box Below. The more people get involved, the more we all benefit. So, leave your thoughts before you leave the page