Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Accessing a table from a name in a variable

715700Aug 28 2009 — edited Aug 28 2009
Hi there
So here's what I'm trying to do.
I have a software that creates tables with this nomenclatur : XB[TYPE][laste two digit of the year][month][number]
so I got tables like XB0609080002 for the type 6, year 2009, august, number 2.

Now, I'd like to select datas in some of those tables and insert them into another table. For ex, I want to put rows A and B of all the tables of type 4 and 6 of august in a table, so I have to select datas from tables going from XB0409080001 to XB0409080054 and from XB0609080001 to XB0609080031.

I'd like to make a procedure to union all those tables so I can select the datas I want or create a view out of it, or create a procedure to go through the table and copy the relevant data into a single table.
I'm usually not using procedure, so the one I've done isn't working (I'm not even sure I'm invoking the procedure correctly).

Any clues ?

here's the code of my procedure, I've put the month and year parameter hard coded for now:

create or replace Procedure type_paiement
(
journal in varchar2)
is
BEGIN
declare
prefix_ntable varchar2(8);
ntable varchar2(12);
i integer;
ex integer;
mois varchar2(2);
an varchar2(2);
requete varchar2(512);
begin

prefix_ntable:='XB' ||journal ||'09' ||'07';
mois :='07';
an :='09';
for i in 1..100
loop
ntable :=prefix_ntable||lpad ( i,4,'0' ) ;
requete:='select count(*) from user_tables where table_name='''||ntable||'''';
execute immediate requete into ex;
if ex >0 then
requete:='insert into xx_jk_mreg (clie_code,journal,periode) select distinct c.code,
xb.doss,
'||journal||',
'||mois||an||'
from
'||ntable||' xb,
dossier d,
client c
where xb.cmpt=''4111''
and xb.doss =d.code
and c.code =d.clie';
execute immediate requete;
end if;
end loop;
END;
end;

PS: I'm on oracle 9i using SQL developer

Edited by: user5880555 on Aug 28, 2009 9:44 AM

Comments

BluShadow
Just to clarify for everyone as you've used formatting characters in your text...
user5880555 wrote:
I have a software that creates tables with this nomenclatur : {noformat}XB[TYPE][laste two digit of the year][month][number]{noformat}
713017
>
I have a software that creates tables with this nomenclatur : XB[TYPE][laste two digit of the year][month][number]
My first suggestion would be to change the software to use Oracle properly - i.e. don't use hundreds of individually named tables when one will do. As you can see, it is very hard to join them together.
715700
I'am well aware of that but i'm not the editor of the software, I'm just trying to get th best of it. The software does use Oracle in a weird way (7000 tables, no foreign keys and so on)
so, I can only try to collect the datas I need and put them in one of my own table...
GaryBao
if all of the tables have the same structure, maybe you can create a table which include all the information of all the other tables
like:
create table tablename
as
select ... from
union all
select ... from
.
.
.

regards
715700
yes, I've thought of that but that means writing select * from XB0109070001 400 or 500 times...
If there was just half a dozen tables, that would be ok, but here, we're talking hundreds...
Billy Verreynne
MacFizz wrote:
I'am well aware of that but i'm not the editor of the software, I'm just trying to get th best of it.
Best!? Impossible. There is no "+best way+" when dealing with 7000 odd tables, no constraints, no data integrity and (created with) no clue and no brains.

Something like Oracle is a total waste of money and resources on that data. Extract it from the database and put it into flat files. Use programs like awk and grep to filter and process these via shell scripts.

In other words, if it looks like cr@p, smells like cr@p, you don't need to have a nibble to know it is cr@p.

If you are expected to fix this mess, then you should be very clear that the fix will entail a one-time load of the data into a proper relational data model.. and then flushing that cr@p down the old bit bucket.

Personally.. I would raise holy bloody hell when given such a "+system+" to deal with. And I will not be taking any prisoners.... Why? Because it is criminal to create a system like that, getting paid for it, and exposing the business to incredible risk and failure. It in essence is fraud... plain and simple.
713017
sounds like you might want to consider generating a statement using table driven sql (either a view creation statement or a insert into new table statement).

for example, create a table with columns xtype, year, month, num - which correspond to your table suffix conventions:
create table targetData (
      xtype varchar2(2),
      year varchar2(2),
      month varchar2(2)
     num varchar2(2)
)
/

insert into targetData values('04', '09', '08', '0001');
insert into targetData values('04', '09', '08', '0002');
insert into targetData values('04', '09', '08', '0003');
insert into targetData values('06', '09', '08', '0001');
insert into targetData values('06', '09', '08', '0002');
insert into targetData values('06', '09', '08', '0003');
then generate sql statements from this table data. E.g.
select decode(rownum, 1, '', 'union '), 'select 1 from '||'XB'||xtype||year||month||num
from targetData
/


       select 1 from XB0409080001
union  select 1 from XB0409080002
union  select 1 from XB0409080003
union  select 1 from XB0609080001
union  select 1 from XB0609080002
union  select 1 from XB0609080003
Obviously you need to change the 'select 1' to do whatever it is you need to do.

If the columns you're selecting are different in the different tables, then you can put their column names in the driving table (what I called targetData) also.

Not sure if this helps... but I think a table driven approach will give you a lot of flexibility, and it is easy to write loops to populate it. You could also include some kind of queryId or taskId, so that the table can be used to generate many different types of query.

hope this helps.
Billy Verreynne
MacFizz wrote:
yes, I've thought of that but that means writing select * from XB0109070001 400 or 500 times...
If there was just half a dozen tables, that would be ok, but here, we're talking hundreds...
Once off exercise. Create a partition table with partition range (likely date?) criteria to cover the 100's of disjointed tables.

Create a stored proc that take a crappy table name as input, use this to determine the applicable partition for the data, and then perform a partition exchange - swapping the (empty) contents of the partition with the contents of the table.

Write another proc to call this one in a loop - once per crappy table.

End result. Empty crappy tables that can be trashed and removed. Single partitioned table containing the data - ready to be indexed and corrected and fixed for proper usage.
713017
I very much agree with Billy's point of view too. In fact I was going to say something similar, but I refrained having been many times in a position of having to deal with very badly written applications, without the power to throw them out or change them, and still having to apply band aids to their gushing wounds to try to keep them from dying.
715700
well, for one thing, it seems I shouldn't have take this new "job opportunity", and the software has to stay as it is, all I can do is extract the datas as regulary and as accuratly as possible.
Now, for the record, the software using such a cr@py db is an international german group, leader on its market, creepy isn't it ?

For the moment, your answers did help me a lot, now I'm just trying to fix the procedure with another post regarding the if statement (3728076 and if I can pull this one out, I should be out of the woods...

thx for your helps
Boneist
MacFizz wrote:
well, for one thing, it seems I shouldn't have take this new "job opportunity", and the software has to stay as it is, all I can do is extract the datas as regulary and as accuratly as possible.
Now, for the record, the software using such a cr@py db is an international german group, leader on its market, creepy isn't it ?

For the moment, your answers did help me a lot, now I'm just trying to fix the procedure with another post regarding the if statement (3728076 and if I can pull this one out, I should be out of the woods...

thx for your helps
If you can't do as Billy suggested with the partitioned table, perhaps you could create a view over all the tables (along with a procedure that would need to be run to amend the view everytime a table was added) to mimic a partitioned table?
Billy Verreynne
Boneist wrote:

If you can't do as Billy suggested with the partitioned table, perhaps you could create a view over all the tables (along with a procedure that would need to be run to amend the view everytime a table was added) to mimic a partitioned table?
Back in Oracle 7 (prior current partitioning) there was a feature called partitioned views - which essentially was (supposedly) doing what you suggest in an intelligent fashion. This was dropped ifo proper partitioning in 8i.

The problem with making a huge view out of it is performance - as each table will need to be hit for every query on the view (that should hit a single partition). The more tables, the worse the performance. 100's of tables? Double ouch...

There's another hackish way that can be considered - gluing these 100's of tables together using a pipelined table function. But doing it in a seamless fashion.. pretty difficult if not impossible. (up for investigating how well one can parse the current SQLs predicate in a pipeline table dynamically? ;-) )

The partitioned method is the better option as the existing (cr@ppy) code can still be supported - by creating views to replace the crappy tables, where the view is a select from the specific partition itself, e.g. 
create view CRP2009082812 for select * from proper_table partition(p20090828_12)
So the code should still work unchanged.. except now against a proper partitioned table.

At times one has to play hardball. And IMO, this is one of those times. Partitioning to resolve the cr@p.. or wash one's hands and walk away. As this type of problem not only dirties one's hands, but one's soul too with immense frustration and lots of unpleasantness.
1 - 12
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Sep 25 2009
Added on Aug 28 2009
#procedure
12 comments
493 views