Re: Dynamic Search Conditions using PL SQL

From: Jerome Hampson <jerome.hampson_at_gmail.com>
Date: Wed, 13 Jun 2007 13:56:28 -0000
Message-ID: <1181742988.003516.14400@n15g2000prd.googlegroups.com>

On Jun 12, 6:52 pm, sybra..._at_hccnet.nl wrote:
> On Tue, 12 Jun 2007 19:37:47 -0000, Valentin Minzatu
>
> <valentinminz..._at_yahoo.com> wrote:
> >I never did an impact analysis between implementing dynamic SQL and
> >VPD and I do not know neither the business case nor the usage/size/etc
> >of the database hence the question (as opposed to affirmation), but
> >from the example it looks like it may be the case for it or for
> >implementing something along those lines.
>
> VPD is always in use, even during export. It is only disabled when SYS
> exports the database.
> Still think this is a viable suggestion to avoid dynamic sql?
>
> --
> Sybrand Bakker
> Senior Oracle DBA

How about something like(not "dynamic sql" per se):

CREATE OR REPLACE Procedure GetCustomers (vcustomer_id in varchar) IS

• vcustomer_id = "11" BEGIN

   Open result For SELECT CUSTOMER_ID FROM C_CUSTOMER    where 1=1

        AND ((CUSTOMER_ID IS NULL AND vcustomer_id IS NULL)
          OR ((CUSTOMER_ID IS NOT NULL AND vcustomer_id IS NOT NULL)
          AND CUSTOMER_ID=vcustomer_id)
          OR vcustomer_id IS NULL
          )

; Received on Wed Jun 13 2007 - 08:56:28 CDT