Re: best way to "parameterize" a tablename?

Malcolm Dew-Jones wrote:
DA Morgan (damorgan@xxxxxxxxx) wrote:
: Carlos wrote:
: > On 22 ago, 04:25, t...@xxxxxxxxx (Dan Blum) wrote:
: >> gym dot scuba dot kennedy at gmail <kenned...@xxxxxxxxxxx> wrote:
: >>
: >>
: >>
: >>> "Dan Blum" <t...@xxxxxxxxx> wrote in message
: >>> news:g8l39p$510$1@xxxxxxxxxxxxxxxxxxxx
: >>>> m...@xxxxxxxxx wrote:
: >>>>> gym dot scuba dot kennedy at gmail <kenned...@xxxxxxxxxxx> wrote:
: >>>>>> <m...@xxxxxxxxx> wrote in message
: >>>>>>> if tablename = 'FOO'
: >>>>>>> insert into foo ...
: >>>>>>> else if tablename = 'BAR'
: >>>>>>> insert into bar ... (exact same stuff)
: >>>>>>> etc...
: >>>>>> Use bind
: >>>>>> variables.)
: >>>>> I guess that's what I'm asking... how can I use bind variables or
: >>>>> equivalent for the table name?
: >>>> You can't.
: >>>>>> You have an odd schema is each table has the same structure but a
: >>>>>> different
: >>>>>> 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.
: >>>> 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 t...@xxxxxxxxx
: >>>> "I wouldn't have believed it myself if I hadn't just made it up."
: >>> You can use bind variables for execute immediate. see using clause. I
: >>> would recommend not using execute immediate to dynamically execute a user
: >>> supplied string since you open yourself up to sql injection. So a match
: >>> on an incomming tablename on user_tables and then using a string you supply
: >>> (not the arg coming in would be better)
: >> Yes, you can use bind variables with execute immediate - but not for the TABLE
: >> NAME. The OP does not want to run SQL entered by a user, he wants to run
: >> a given SQL statement against one of two tables without having the statement
: >> in the code twice.
: >>
: >> --
: >> _______________________________________________________________________
: >> Dan Blum t...@xxxxxxxxx
: >> "I wouldn't have believed it myself if I hadn't just made it up."
: > : > You can also use 'sed' (if in Linux) with your sql script in order to
: > find and substitute the table name (which you should have marked with
: > some kind of token) and pipe it to sqlplus.
: > : > HTH.
: > : > Cheers.
: > : > Carlos.

: Give an end-user access to sed on a *NIX host? Surely you jest.

Surely you misunderstand.

sed is used by the programmer who is writing the module so that a single
statement in the source code will be repeated multiple times when the source code is compiled.

SQL "compiled" using sqlplus can be manipulated in many ways. A quick
summary of techniques to consider

define
column X new_value Y
@script parameters
spool temp.sql
@temp.sal

the @ command in particular is extremely useful for sharing code snippets
between many modules, since it accepts parameters, so it acts very much
like a multi line parameterized #define in C. The biggest problem is that
the parameters passed in to @ cannot vary within a statement since the
code is expanded just once, so you can't do things like

create procedure WONT_WORK as
begin

@DO_IF_TABLE_THEN_INSERT 'TABLE_ONE'
@DO_IF_TABLE_THEN_INSERT 'TABLE_TWO'
@DO_IF_TABLE_THEN_INSERT 'TABLE_THREE'
end;
/

For more complex things I might write a package that generates the code I need and include that as part of the development system

-- SQLPLUS SCRIPT
spool TEMP1.SQL
exec my_generator.generate_insert('TABLE_ONE');
exec my_generator.generate_insert('TABLE_TWO');
exec my_generator.generate_insert('TABLE_THREE');
spool off

create or replace procedure TRY_PROC AS
begin
@TEMP.SQL
end;
/



Personally I don't use sed much, I prefer perl. The following does
nothing useful, but it does show how a sqlplus script can be self
contained and generate its own code. (by "self contained" I mean that all
the logic of the code is contained in this single file).

--
-- TRYING.SQL
--
/*
#!perl
foreach (qw( ONE TWO THREE))
{ print "
SELECT x into Y FROM $_;
"
}
__END__
*/

host cmd/c perl -x trying.sql > temp.sql

create or replace procedure TRYING AS
Y number;
begin
@temp.sql
end;
/


$0.10

Makes sense. Not too disimilar from the example I have on my
UTL_FILE page where I use UTL_FILE to dynamically create
SQL*Loader control files.

Given a choice of sed, perl, or UTL_FILE ... for this purpose
.... I'd take UTL_FILE. Easier to handle security, only one
skill set required, no need for the power of the other two.
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan@xxxxxxxxxxxxxxxx (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
.

Relevant Pages

  • Re: best way to "parameterize" a tablename?
    ... The OP does not want to run SQL entered by a user, ... :> Carlos. ... Personally I don't use sed much, I prefer perl. ... but it does show how a sqlplus script can be self ...
    (comp.databases.oracle.misc)
  • Re: use of DBI; I am getting multiple error messages mixed in with the correct output.
    ... T> expected a mature library like DBI to behave like this. ... I learned plenty about the perl side of things from her posts. ... closely represent the SQL idea of a null. ... trivially easy to perform bulk loads using the DB's particular ...
    (comp.lang.perl.misc)
  • ANNOUNCE - Muldis::DB v0.0.0 released, in p5+p6
    ... I am pleased to announce the release of Muldis::DB version 0.0.0 for Perl 5 on CPAN. ... If you want to read all the Muldis::DB documentation that exists now, I recommend doing so in this order: ... Muldis::DB implements a D language as defined by Hugh Darwen and Chris Date, and presents a superior interface for working with the relational model of data, contrasted with SQL. ...
    (perl.dbi.users)
  • Re: use of DBI; I am getting multiple error messages mixed in with the correct output.
    ... %%> Perl was to relate it to uninitialized values in Java or C++. ... %%> The idea of NULL in SQL is very different. ... mapping SQL NULLs to undef was a choice. ...
    (comp.lang.perl.misc)
  • Re: ANNOUNCE: SQL-Interpolate 0.31
    ... SQL-Interpolate does support variable references embedded in strings. ... Since a number of people I talked to distrust source filtering, I moved this capability into the separate module and made it entirely optional. ... I'm somewhat fond of your use of the ":" syntax though since this might clarify the special meaning and differentiate it from the standard Perl string interpolation. ... Some differences I see are that the core SQL::Interpolate module is not dependent on source filtering, and the SQL is treated more as a first-class object separate from the Perl code. ...
    (perl.dbi.users)
Loading