Dynamic Search Conditions using PL SQL

From: Janaka Perera <janakaclk_at_gmail.com>
Date: Tue, 12 Jun 2007 07:57:34 -0000
Message-ID: <1181635054.204151.321510@x35g2000prf.googlegroups.com>

I want to create a stored procedure which has a dynamic "Where" condition. I don't want to create a dynamic query string in the runtime and execute it because then it will pass the SQL on the runtime which will reduce the performance. Therefore I need to compile the SQL's SELECT and JOIN statements and append the WHERE clause at runtime. Is there a way to do something like that using PL/SQL. Thanks in advance.

The following code snippet describes the scenario.

CREATE OR REPLACE Procedure GetCustomers (filterCriteria IN varchar2) IS

• filterCriteria " where customer_id = 11" BEGIN

   Open result For SELECT CUSTOMER_ID FROM C_CUSTOMER || filterCriteria;

........................

............ Received on Tue Jun 12 2007 - 02:57:34 CDT