Re: Dynamic Search Conditions using PL SQL

On Jun 12, 9:57 am, Janaka Perera <janaka..._at_gmail.com> wrote:
> 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;
>
> ........................
> ............

Not possible.
As soon as part of a SQL statement is dynamic, the SQL statement is dynamic.
That is, unless only the value of literals change for every invocation.

--
Sybrand Bakker
Senior Oracle DBA