I am trying to get data from a table where the table name is stored in another table. I am trying for select query but not able to get the data.
example
t1
table name | some data
t2 - table name is same coming from t1
t2
id | some data
I need to fetch the t2 name first from t1 that I am able to do. but using that query response I am not sure how to fetch the second query as the result is coming from first query. Any help is appreciated.
Thanks
It'll have to be some kind of "dynamic" SQL. One option is to create a function that returns ref cursor. Here's an example.
This is your t1 table; it contains some table names.
SQL> select * from tname;
ID TABLE_NAME
---------- ---------------
1 emp
2 dept
Function:
SQL> create or replace function f_tab (par_table_name in varchar2)
2 return sys_refcursor
3 is
4 rc sys_refcursor;
5 begin
6 open rc for 'select * from ' || dbms_assert.sql_object_name(par_table_name);
7 return rc;
8 end;
9 /
Function created.
Testing: query selects table names from the tname table and returns their contents:
SQL> select f_tab(t.table_name) result
2 from tname t
3 order by t.id;
RESULT
--------------------
CURSOR STATEMENT : 1
CURSOR STATEMENT : 1
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17.12.1980 00:00:00 800 20
7499 ALLEN SALESMAN 7698 20.02.1981 00:00:00 1600 300 30
7521 WARD SALESMAN 7698 22.02.1981 00:00:00 1250 500 30
7566 JONES MANAGER 7839 02.04.1981 00:00:00 2975 20
7654 MARTIN SALESMAN 7698 28.09.1981 00:00:00 1250 1400 30
7698 BLAKE MANAGER 7839 01.05.1981 00:00:00 2850 30
7782 CLARK MANAGER 7839 09.06.1981 00:00:00 2450 10
7788 SCOTT ANALYST 7566 09.12.1982 00:00:00 3000 20
7839 KING PRESIDENT 17.11.1981 00:00:00 5000 10
7844 TURNER SALESMAN 7698 08.09.1981 00:00:00 1500 0 30
7876 ADAMS CLERK 7788 12.01.1983 00:00:00 1100 20
7900 JAMES CLERK 7698 03.12.1981 00:00:00 950 30
7902 FORD ANALYST 7566 03.12.1981 00:00:00 3000 20
7934 MILLER CLERK 7782 23.01.1982 00:00:00 1300 10
14 rows selected.
CURSOR STATEMENT : 1
CURSOR STATEMENT : 1
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL>
