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

<mh (AT) pixar (DOT) com> wrote

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

<mh (AT) pixar (DOT) com> wrote

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

<mh (AT) pixar (DOT) com> wrote

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

<mh (AT) pixar (DOT) com> wrote

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

gym dot scuba dot kennedy at gmail <kennedyii (AT) verizon (DOT) net> wrote:
I guess that's what I'm asking... how can I use bind variables or
equivalent for the table name?

There's a reason for it, we basically don't want to intermingle
one set of data with another. Think of the case where there's
a million records of FOO and 100 records of BAR. An index
won't be very useful, so you end up doing a lot of full
table scans to extract the data, and we don't want to pay
the big cost of FOO for the tiny data from BAR. The real
case is a bit more complicated, but that's what it boils
down to.

Thanks!

--
Mark Harrison
Pixar Animation Studios

gym dot scuba dot kennedy at gmail <kennedyii (AT) verizon (DOT) net> wrote:
I guess that's what I'm asking... how can I use bind variables or
equivalent for the table name?

There's a reason for it, we basically don't want to intermingle
one set of data with another. Think of the case where there's
a million records of FOO and 100 records of BAR. An index
won't be very useful, so you end up doing a lot of full
table scans to extract the data, and we don't want to pay
the big cost of FOO for the tiny data from BAR. The real
case is a bit more complicated, but that's what it boils
down to.

Thanks!

--
Mark Harrison
Pixar Animation Studios

gym dot scuba dot kennedy at gmail <kennedyii (AT) verizon (DOT) net> wrote:
I guess that's what I'm asking... how can I use bind variables or
equivalent for the table name?

There's a reason for it, we basically don't want to intermingle
one set of data with another. Think of the case where there's
a million records of FOO and 100 records of BAR. An index
won't be very useful, so you end up doing a lot of full
table scans to extract the data, and we don't want to pay
the big cost of FOO for the tiny data from BAR. The real
case is a bit more complicated, but that's what it boils
down to.

Thanks!

--
Mark Harrison
Pixar Animation Studios

gym dot scuba dot kennedy at gmail <kennedyii (AT) verizon (DOT) net> wrote:
I guess that's what I'm asking... how can I use bind variables or
equivalent for the table name?

There's a reason for it, we basically don't want to intermingle
one set of data with another. Think of the case where there's
a million records of FOO and 100 records of BAR. An index
won't be very useful, so you end up doing a lot of full
table scans to extract the data, and we don't want to pay
the big cost of FOO for the tiny data from BAR. The real
case is a bit more complicated, but that's what it boils
down to.

Thanks!

--
Mark Harrison
Pixar Animation Studios

mh (AT) pixar (DOT) com wrote:
You can't.

If you can distinguish the BAR records from the FOO records,
an index on the distinguishing column(s) would in fact be very
useful - it would let you get the BAR records quite quickly.

However, if you really can't merge the tables, I would use EXECUTE
IMMEDIATE. Using it with WHERE clauses which you don't have control
over is a bad idea, but for table names it's simple to verify the
string you are getting - just make sure it matches an entry in
USER_TABLES.TABLE_NAME.

--
__________________________________________________ _____________________
Dan Blum tool (AT) panix (DOT) com
"I wouldn't have believed it myself if I hadn't just made it up."