Splicing Table name with variable


Looking to splice table name with another data pull

One table I have
table test
ID       NO
123   55
124   57

select * from XXNOXX a, test b
where a.id = b.id  (Matched only on 123)
in other words the actual query would be
select * from XX55XX a, test b
where a.id = b.id

Eventually want to union the queries

select * from XX55XX a, test b
where a.id = b.id
select * from XX57XX a, test b
where a.id = b.id

Tables XX55XX and XX57XX  exist of course.
 Any help would be much appreciate.


Who is Participating?
slightwv (䄆 Netminder)Connect With a Mentor Commented:
I agree that if possible, change the design.  If you have the license for partitioning by all means use it.

That said:  I've seen the 'pre-create a view with ALL tables' as suggested in http:#30090305.  It didn't work that well from a performance standpoint.  Why scan ALL tables when you are only after 2?

All that said:  What do you want to do with the results once you get them?

Here's a simple function that returns a refcursor.  You can change it to a pilelined function if you really want to 'select' the values like a real table.

drop table tab1 purge;
create table tab1(id number);

drop table tab2 purge;
create table tab2(id number);

drop table test purge;
create table test(id number, no number);

insert into tab1 values(1);
insert into tab2 values(2);

insert into test values(123,1);
insert into test values(123,2);

create or replace function myFunc(inId number) return sys_refcursor is
	mySQL varchar2(4000);
	myCursor sys_refcursor;
	--set initial dummy select to keep from coding a lot of exceptions in case you pass in a
	--an id that returns no rows
	mySQL := ' select null from dual union ';
	for i in (select no from test where id=inId) loop
		mySQL := mySQL || 'select id from tab' || i.no || ' where id=' || i.no || ' union ';
	end loop;

	mySQL := rtrim(mySQL,'union ');

	open myCursor for mySQL;
	return myCursor;

show errors

select cursor( select myfunc(123) from dual) from dual;

--return nothin w/o error
select cursor( select myfunc(999) from dual) from dual;

Open in new window

how may different tables do you have?

other than generating dynamic SQL on the fly and executing it, another approach may be to make a VIEW,
SELECT '55' as no, a.* FROM XX55XX a
SELECT '57' as no, a.* FROM XX57XX a;

and then join to the view, e.g.
select * from XXNOXX a, test b
where a.id = b.id
AND a.no = b.no
Franck PachotCommented:
I don't know if you can change the design, and you have Entreprise Edition, but your table looks like a godd candidate for partitioning: same logical structure, different physical storage.
Göran AnderssonCommented:
If the tables have the same layout and only differs by name, they shouldn't be separate tables. The data should be in the tables, not part of the table name. If possible, make a single table out of them, with one added field that contains that number from the table name. That way you can easily query the table without creating the SQL dynamically.

If you can't change the database design, you have to create the query dynaimcally.
B_riderAuthor Commented:
Great thanks
All Courses

From novice to tech pro — start learning today.