Accessing a table from a name in a variable

715700

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

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 [email protected], smells like [email protected], you don't need to have a nibble to know it is [email protected]

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 [email protected] 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 [email protected] 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