Re: best way to "parameterize" a tablename?

<mh_at_pixar.com> wrote in message
news:Lxlrk.25253$Ri.20562_at_flpi146.ffdc.sbc.com...
>I have need to do something like this:
>
>
> if tablename = 'FOO'
> insert into foo ...
> else if tablename = 'BAR'
> insert into bar ... (exact same stuff)
> etc...
>
> I hesitate to use an execute immediate, due to the troublesome
> quoting issues, but is there a good way to do something
> like this?
>
> insert into $TABLENAME ...
>
> Many TIA!
> Mark
>
> --
> Mark Harrison
> Pixar Animation Studios

    if tablename = 'FOO' then

        insert into foo ...
    elsif tablename = 'BAR' then

        insert into bar ... (exact same stuff)     elsif ...
    else

       raiseapplicationerror(.....)
    end if;

Don't use execute immediate for this type of thing. I suspect you are trying to over genericize things. Using execute immediate and similar dynamic sql where strings are executed is a perfect way to subject yourself to sql injection. (and don't give me crap about "filtering" out bad values to prevent it. Won't work, too complex and not scalable. Use bind variables.)

You have an odd schema is each table has the same structure but a different name.
Jim Received on Thu Aug 21 2008 - 18:01:42 CDT