How to select a table using a string in Oracle?

Question

First of all, I know the question is vague, so feel free to edit it if you can describe it better. There are some sets of table like TEST_201812, TEST_201901, etc. . I have another table that stores these values:

TEST_DATE:

ID   DATE
1    201810
2    201811
3    201812
4    201901

Now what I want is to select the tables mentioned above (e.g.TEST_201812) by using TEST_DATE. I know it's wrong, but something like this:

select * from TEST_(select DATE from TEST_DATE where ID = 1)

Does anyone know how to achieve this?

You would have to use dynamic SQL (i.e. execute immediate). However, you should fix your data model. You should have a single table with a column for date in the table. — Gordon Linoff
– Gordon Linoff, Commented Dec 31, 2018 at 12:29

Littlefoot · Accepted Answer · 2018-12-31 12:41:10Z

5

Seriously, such a data model is a disaster. If you want to keep separate months separately, use one - partitioned - table.

Anyway, here's one option of how to do that:

Sample tables and a function that returns refcursor:

SQL> create table test_201812 as select * From dept;

Table created.

SQL> create table test_date (id number, datum number);

Table created.

SQL> insert into test_date values (1, 201812);

1 row created.

SQL> create or replace function f_test (par_id in test_date.id%type)
  2    return sys_refcursor
  3  is
  4    l_datum test_date.datum%type;
  5    l_str   varchar2(200);
  6    l_rc    sys_refcursor;
  7  begin
  8    select datum
  9      into l_datum
 10      from test_date
 11      where id = par_id;
 12
 13    l_str := 'select * from test_' || l_datum;
 14    open l_rc for l_str;
 15    return l_rc;
 16  end;
 17  /

Function created.

Testing:

SQL> select f_test(1) from dual;

F_TEST(1)
--------------------
CURSOR STATEMENT : 1

CURSOR STATEMENT : 1

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON


SQL>

answered Dec 31, 2018 at 12:41

Littlefoot

144k15 gold badges42 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

iminiki Over a year ago

I ran the exact code that you've written. In the end, it returns (CURSOR).

Littlefoot Over a year ago

Which tool do you use? If it is some GUI (TOAD, SQL Developer), double-click that (cursor) thing and see what's in there. Because, you never said what you're going to do with the result once you get it

iminiki Over a year ago

Yes, I'm using Toad. First I wanted to select the table and then drop it and create a new one with a new date. But now I've realized that as you suggested, using only one table is more practical and reasonable.

Littlefoot Over a year ago

Certainly it is; that's the path you should take.

Collectives™ on Stack Overflow

How to select a table using a string in Oracle?

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related