On Tue, Feb 3, 2009 at 12:37 PM, Johannes Gritsch <johannes.grit...@gnc.at>wrote:
> I would say if a request like this should be solved without using Perl > variables the following applies: > > - the data model is bad > Truly. Especially if it really is changing underneath his feet. - creating views is a bad idea since you would do DDL on a production > system. That in return would deteriorate the execution/parse time > quotient of the database instance. Creating views is based on the assumption it's done at development time, along with any other database changes. If he had to do it dynamically in Perl, then yes, that is no solution (and in fact would be a violation of the rule he's trying to avoid). - using a PL/SQL routine as Scott suggests seems to me the best > solution, though dynamic SQL might also become a tuning nightmare If he can't build the SELECT in Perl, he shouldn't be allowed to do it in PL/SQL, either, right? Can PL/SQL detect SQL injection attacks better than Perl code? > Scott Smith wrote: > > I think his issue is getting list to the server side without > > concatenating strings. > > > > You can't use bind variables as table names or column names. > > > > You may be able to write a stored procedure which takes a table name as > > a variable, does the string concatenation on the server side, and uses > > Oracle's "execute immediate" to effectively "eval" the SQL there.. > > > > Scott > > > > Dale wrote: > >> On Tue, Feb 3, 2009 at 8:47 AM, Deviloper <devilo...@slived.net> wrote: > >> > >> > >>> Hello there! > >>> > >>> lets assume that one has a list of tables @db_tables. (For Example one > per > >>> Month) > >>> Lets assume one wants to find some data in the tables. (For Example > sold > >>> Items.) > >>> > >>> select sales from $db_tables[0]; # gives all sold items in January > >>> > >>> > >> You could use a UNION in the database to combine the common data (you > can > >> create a view and use that for your select): > >> > >> CREATE VIEW v_combined_months AS > >> SELECT 'January' AS "Month",sales FROM jan_sales > >> UNION ALL > >> SELECT 'February' AS "Month",sales FROM feb_sales > >> UNION ALL > >> SELECT 'March' AS "Month",sales FROM march_sales > >> [...] > >> > >> > > > > > -- > -- > Johannes Gritsch > Oracle DBA and Perl afficionado > Vienna, Austria >
