CODEWITHSUNDEEP
oracle-databasedynamicoracle11g
Victor Santos
Victor Santos

Reputation: 350

Is possible to use dynamic table names in Oracle?

Can I do something like it?

SELECT * FROM bd.images_ || '2011'

Because I want to do something like this:

SELECT
    x.name, x.year, w.imgblob
FROM
    bd.img_idx x,
    (SELECT imgblob FROM bd.images_ || x.year WHERE name = x.name) w
WHERE
    x.name = 'nanananana'

Upvotes: 0

Views: 6269

Answers (2)

Florin Ghita
Florin Ghita

Reputation: 17643

You have a bad design.

I speak about tables bd.images_yyyy. Best is to have them in a single table bd_images with a column year, partitioned on year.

Your query would become:

SELECT
    x.name, x.year, w.imgblob
FROM
    bd.img_idx x 
    JOIN bd.images w on (x.year = w.year and w.name = x.name) 
WHERE
    x.name = 'nanananana'

However, you can do some dynamic ddl in case you can't modify your database or you don't have enterprise license.

Create a temptable with name, year, imgblob (tmptable).

Populate it with:

Procedure populate_tmp (text varchar2)
begin
for r in (select x.name, x.year from bd.img_idx x where x.name= text)
loop
   execute immediate 'insert into tmptable values ('||r.name||','||r.year||',' (select w.imgblob from bd.images'||r.year||' w where w.year = '||r.year||' and w.name= '||r.name||'))';

end loop;
end;

and after populate('nananannaa') you can select from tmptable.

Attention: for not dealing with old data in tmptable you can create it as global temporary table on commit delete. When you commit, the data will disapear from table.

Upvotes: 1

Samuel
Samuel

Reputation: 2490

Yes, but not with static SQL. You can use Native Dynamic SQL (EXECUTE IMMEDIATE), which probably suits your use case or the more complex(and powerful) DBMS_SQL package.

Upvotes: 2

Related Questions