 | |
best way to "parameterize" a tablename?
comp.databases.oracle.misc comp.databases.oracle.misc
 |
| | Thread Tools | Display Modes |
#41
 
| |||
| |||
|
Re: best way to "parameterize" a tablename? -
08-22-2008
, 03:17 PM
#42
| |||
| |||
|
|
DA Morgan (damorgan (AT) psoug (DOT) org) wrote: : Carlos wrote: : > On 22 ago, 04:25, t... (AT) panix (DOT) com (Dan Blum) wrote: : >> gym dot scuba dot kennedy at gmail <kenned... (AT) verizon (DOT) net> wrote: : : : : >>> "Dan Blum" <t... (AT) panix (DOT) com> wrote in message : >>> news:g8l39p$510$1 (AT) reader1 (DOT) panix.com... : >>>> m... (AT) pixar (DOT) com wrote: : >>>>> gym dot scuba dot kennedy at gmail <kenned... (AT) verizon (DOT) net> wrote: : >>>>>> <m... (AT) pixar (DOT) com> 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... (AT) panix (DOT) com : >>>> "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... (AT) panix (DOT) com : >> "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 |
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@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
#43
| |||
| |||
|
|
DA Morgan (damorgan (AT) psoug (DOT) org) wrote: : Carlos wrote: : > On 22 ago, 04:25, t... (AT) panix (DOT) com (Dan Blum) wrote: : >> gym dot scuba dot kennedy at gmail <kenned... (AT) verizon (DOT) net> wrote: : : : : >>> "Dan Blum" <t... (AT) panix (DOT) com> wrote in message : >>> news:g8l39p$510$1 (AT) reader1 (DOT) panix.com... : >>>> m... (AT) pixar (DOT) com wrote: : >>>>> gym dot scuba dot kennedy at gmail <kenned... (AT) verizon (DOT) net> wrote: : >>>>>> <m... (AT) pixar (DOT) com> 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... (AT) panix (DOT) com : >>>> "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... (AT) panix (DOT) com : >> "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 |
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@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
#44
| |||
| |||
|
|
DA Morgan (damorgan (AT) psoug (DOT) org) wrote: : Carlos wrote: : > On 22 ago, 04:25, t... (AT) panix (DOT) com (Dan Blum) wrote: : >> gym dot scuba dot kennedy at gmail <kenned... (AT) verizon (DOT) net> wrote: : : : : >>> "Dan Blum" <t... (AT) panix (DOT) com> wrote in message : >>> news:g8l39p$510$1 (AT) reader1 (DOT) panix.com... : >>>> m... (AT) pixar (DOT) com wrote: : >>>>> gym dot scuba dot kennedy at gmail <kenned... (AT) verizon (DOT) net> wrote: : >>>>>> <m... (AT) pixar (DOT) com> 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... (AT) panix (DOT) com : >>>> "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... (AT) panix (DOT) com : >> "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 |
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@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
#45
| |||
| |||
|
|
DA Morgan (damorgan (AT) psoug (DOT) org) wrote: : Carlos wrote: : > On 22 ago, 04:25, t... (AT) panix (DOT) com (Dan Blum) wrote: : >> gym dot scuba dot kennedy at gmail <kenned... (AT) verizon (DOT) net> wrote: : : : : >>> "Dan Blum" <t... (AT) panix (DOT) com> wrote in message : >>> news:g8l39p$510$1 (AT) reader1 (DOT) panix.com... : >>>> m... (AT) pixar (DOT) com wrote: : >>>>> gym dot scuba dot kennedy at gmail <kenned... (AT) verizon (DOT) net> wrote: : >>>>>> <m... (AT) pixar (DOT) com> 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... (AT) panix (DOT) com : >>>> "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... (AT) panix (DOT) com : >> "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 |
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@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
#46
| |||
| |||
|
|
Malcolm Dew-Jones wrote: DA Morgan (damor... (AT) psoug (DOT) org) wrote: : Carlos wrote: : > On 22 ago, 04:25, t... (AT) panix (DOT) com (Dan Blum) wrote: : >> gym dot scuba dot kennedy at gmail <kenned... (AT) verizon (DOT) net> wrote: : : : : >>> "Dan Blum" <t... (AT) panix (DOT) com> wrote in message : >>>news:g8l39p$510$1 (AT) reader1 (DOT) panix.com... : >>>> m... (AT) pixar (DOT) com wrote: : >>>>> gym dot scuba dot kennedy at gmail <kenned... (AT) verizon (DOT) net> wrote: : >>>>>> <m... (AT) pixar (DOT) com> 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... (AT) panix (DOT) com : >>>> "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... (AT) panix (DOT) com : >> "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 damor...@x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Groupwww.psoug.org |
I've been out for the weekend...
Malcolm explains my point OK.
We use 'sed' a lot in server scripts with Teradata tools. Teradata
tools are not as versatile as sql*plus and we use many bash shell
scripts. Some of them use 'sed' among some other Linux/bash fine
commands in a way that the OP could find helpful, so that was my
point.
Cheers.
Carlos.
#47
| |||
| |||
|
|
Malcolm Dew-Jones wrote: DA Morgan (damor... (AT) psoug (DOT) org) wrote: : Carlos wrote: : > On 22 ago, 04:25, t... (AT) panix (DOT) com (Dan Blum) wrote: : >> gym dot scuba dot kennedy at gmail <kenned... (AT) verizon (DOT) net> wrote: : : : : >>> "Dan Blum" <t... (AT) panix (DOT) com> wrote in message : >>>news:g8l39p$510$1 (AT) reader1 (DOT) panix.com... : >>>> m... (AT) pixar (DOT) com wrote: : >>>>> gym dot scuba dot kennedy at gmail <kenned... (AT) verizon (DOT) net> wrote: : >>>>>> <m... (AT) pixar (DOT) com> 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... (AT) panix (DOT) com : >>>> "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... (AT) panix (DOT) com : >> "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 damor...@x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Groupwww.psoug.org |
I've been out for the weekend...
Malcolm explains my point OK.
We use 'sed' a lot in server scripts with Teradata tools. Teradata
tools are not as versatile as sql*plus and we use many bash shell
scripts. Some of them use 'sed' among some other Linux/bash fine
commands in a way that the OP could find helpful, so that was my
point.
Cheers.
Carlos.
#48
| |||
| |||
|
|
Malcolm Dew-Jones wrote: DA Morgan (damor... (AT) psoug (DOT) org) wrote: : Carlos wrote: : > On 22 ago, 04:25, t... (AT) panix (DOT) com (Dan Blum) wrote: : >> gym dot scuba dot kennedy at gmail <kenned... (AT) verizon (DOT) net> wrote: : : : : >>> "Dan Blum" <t... (AT) panix (DOT) com> wrote in message : >>>news:g8l39p$510$1 (AT) reader1 (DOT) panix.com... : >>>> m... (AT) pixar (DOT) com wrote: : >>>>> gym dot scuba dot kennedy at gmail <kenned... (AT) verizon (DOT) net> wrote: : >>>>>> <m... (AT) pixar (DOT) com> 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... (AT) panix (DOT) com : >>>> "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... (AT) panix (DOT) com : >> "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 damor...@x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Groupwww.psoug.org |
I've been out for the weekend...
Malcolm explains my point OK.
We use 'sed' a lot in server scripts with Teradata tools. Teradata
tools are not as versatile as sql*plus and we use many bash shell
scripts. Some of them use 'sed' among some other Linux/bash fine
commands in a way that the OP could find helpful, so that was my
point.
Cheers.
Carlos.
#49
| |||
| |||
|
|
Malcolm Dew-Jones wrote: DA Morgan (damor... (AT) psoug (DOT) org) wrote: : Carlos wrote: : > On 22 ago, 04:25, t... (AT) panix (DOT) com (Dan Blum) wrote: : >> gym dot scuba dot kennedy at gmail <kenned... (AT) verizon (DOT) net> wrote: : : : : >>> "Dan Blum" <t... (AT) panix (DOT) com> wrote in message : >>>news:g8l39p$510$1 (AT) reader1 (DOT) panix.com... : >>>> m... (AT) pixar (DOT) com wrote: : >>>>> gym dot scuba dot kennedy at gmail <kenned... (AT) verizon (DOT) net> wrote: : >>>>>> <m... (AT) pixar (DOT) com> 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... (AT) panix (DOT) com : >>>> "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... (AT) panix (DOT) com : >> "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 damor...@x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Groupwww.psoug.org |
I've been out for the weekend...
Malcolm explains my point OK.
We use 'sed' a lot in server scripts with Teradata tools. Teradata
tools are not as versatile as sql*plus and we use many bash shell
scripts. Some of them use 'sed' among some other Linux/bash fine
commands in a way that the OP could find helpful, so that was my
point.
Cheers.
Carlos.
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.