On Jun 13, 9:56 am, Jerome Hampson <jerome.hamp..._at_gmail.com> wrote:
> 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
> )
> ;
You can ignore the "1=1 AND " piece. It was copied from my sql text
where I have multiple such conditions.
Received on Wed Jun 13 2007 - 09:58:34 CDT
