CODEWITHSUNDEEP
sqloracle-databaseselect
Sara Moradi
Sara Moradi

Reputation: 55

Oracle SQL - Read table name from another table which contains table names

I am going to write a dynamic query which is going to read name of the table from another table.

my first table which contains the table name:(tabel:all_table_names)

table_name
first_table
second_table
third_table

My code for reading data from those tables:

SELECT * FROM
  (replace((SELECT table_name 
   FROM all_table_names  
   WHERE rownum = 1),'"', ''));

Since I know names of the table would be with ' ' I consider a replace statement to omit them but it does not work and has error. Do anyone know how can I handle this problem?

Upvotes: 1

Views: 983

Answers (1)

Littlefoot
Littlefoot

Reputation: 142788

Tables that are created using double quotes are tricky. In Oracle, we normally don't do that. Why? Because Oracle stores table names into data dictionary in uppercase, but you can reference them any way you want. But, if you used double quotes, you have to use them every time you reference that table, matching letter case exactly as you used them during table creation. Too many problems, in my opinion.

For example: this table is created using double quotes and a space between words:

SQL> create table "first table" as select * from dept where deptno = 10;

Table created.

This table is created "normally":

SQL> create table second_table as select empno, ename, job, sal from emp where deptno = 20;

Table created.

This is table that contains list of tables you're interested in:

SQL> create table list_of_tables as
  2    select 'first table' as table_name from dual union all
  3    select 'second_table'              from dual;

Table created.

SQL> select * from list_of_tables;

TABLE_NAME
------------
first table
second_table

Now, if you check tables whose name contains TABLE, in my schema you'd get

SQL> select tname from tab where upper(tname) like '%TABLE%';

TNAME
------------------------------
first table           --> note this table, created using double quotes
TABLE_NAME
TABLE_1
SECOND_TABLE
LIST_OF_TABLES

SQL>

Selecting from first table:

SQL> select * from first table;
select * from first table
                    *
ERROR at line 1:
ORA-00933: SQL command not properly ended


SQL> select * From FIRST TABLE;
select * From FIRST TABLE
                    *
ERROR at line 1:
ORA-00933: SQL command not properly ended


SQL> select * from "FIRST TABLE";
select * from "FIRST TABLE"
              *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> select * from "first table";

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK

SQL>

Only the last one, with double quotes and lower case (as that's how table was created) works.

If compared to e.g. SECOND_TABLE:

SQL> select * from second_table union all
  2  select * from SECONd_TabLE union all
  3  select * from SECOND_TABLE union all
  4  select * from "SECOND_TABLE";

     EMPNO ENAME      JOB              SAL
---------- ---------- --------- ----------
      7369 SMITH      CLERK            800
      7566 JONES      MANAGER         2975
      7788 SCOTT      ANALYST         3000
      7876 ADAMS      CLERK           1100
      <snip>

See? No matter how it is referenced.

What you'd want to do can't be done in SQL - you'll need PL/SQL and dynamic SQL within. As you chose to use double quotes and mixed case, you'll have to use double quotes again (see line #8):

SQL> set serveroutput on
SQL>
SQL> declare
  2    l_str varchar2(200);
  3  begin
  4    for cur_r in (select table_name
  5                  from list_of_tables
  6                 )
  7    loop
  8      l_str := 'select * from ' || '"' || cur_r.table_name || '"';
  9      dbms_output.put_line(l_str);
 10    end loop;
 11  end;
 12  /
select * from "first table"
select * from "second_table"

PL/SQL procedure successfully completed.

Do these SELECT statements work? Partially; first table works, but not the second because table whose name is second_table (in lower case) doesn't exist:

SQL> select * from "first table";

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK

SQL> select * from "second_table";
select * from "second_table"
              *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL>

If you apply e.g. UPPER function, then you'll spoil the first table:

SQL> declare
  2    l_str varchar2(200);
  3  begin
  4    for cur_r in (select table_name
  5                  from list_of_tables
  6                 )
  7    loop
  8      l_str := 'select * from ' || '"' || upper(cur_r.table_name) || '"';
  9      dbms_output.put_line(l_str);
 10    end loop;
 11  end;
 12  /
select * from "FIRST TABLE"
select * from "SECOND_TABLE"

PL/SQL procedure successfully completed.

SQL> select * from "FIRST TABLE";
select * from "FIRST TABLE"
              *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> select * from "SECOND_TABLE";

     EMPNO ENAME      JOB              SAL
---------- ---------- --------- ----------
      7369 SMITH      CLERK            800
      7566 JONES      MANAGER         2975
      7788 SCOTT      ANALYST         3000
      7876 ADAMS      CLERK           1100
      7902 FORD       ANALYST         3000

SQL>

Therefore, you'll either have to abandon the idea of naming tables in that strange manner, or write code which will intercept all possible situations and handle them, somehow. For example, apply double quotes only on tables that contain space:

SQL> declare
  2    l_str varchar2(200);
  3  begin
  4    for cur_r in (select table_name
  5                  from list_of_tables
  6                 )
  7    loop
  8      l_str := 'select * from '     || case when instr(cur_r.table_name, ' ') > 0 then '"' end
  9                || cur_r.table_name || case when instr(cur_r.table_name, ' ') > 0 then '"' end;
 10      dbms_output.put_line(l_str);
 11    end loop;
 12  end;
 13  /
select * from "first table"
select * from second_table

PL/SQL procedure successfully completed.

SQL> select * from "first table";

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK

SQL> select * from second_table;

     EMPNO ENAME      JOB              SAL
---------- ---------- --------- ----------
      7369 SMITH      CLERK            800
      7566 JONES      MANAGER         2975
      7788 SCOTT      ANALYST         3000
      7876 ADAMS      CLERK           1100
      7902 FORD       ANALYST         3000

SQL>

Now both of them work.

However, that's not enough. I'm just displaying select statement on the screen and used copy/paste them to execute them. You won't be doing it, I presume. Question is: what will you do, once you compose those selects? If tables contain different column list, you can't just select * into local variables, nor use arrays as you'd have to create as many of them as tables in the list_of_tables table. Executed dynamically (line #11) (but - there's no "output" of any kind):

SQL> declare
  2    l_str varchar2(200);
  3  begin
  4    for cur_r in (select table_name
  5                  from list_of_tables
  6                 )
  7    loop
  8      l_str := 'select * from '     || case when instr(cur_r.table_name, ' ') > 0 then '"' end
  9                || cur_r.table_name || case when instr(cur_r.table_name, ' ') > 0 then '"' end;
 10      dbms_output.put_line(l_str);
 11      execute immediate l_str;
 12    end loop;
 13  end;
 14  /
select * from "first table"
select * from second_table

PL/SQL procedure successfully completed.

SQL>

So, you might end up with creating a function that returns ref cursor and pass valid table names to it:

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.

SQL> select f_tab('"first table"') from dual;

F_TAB('"FIRSTTABLE"'
--------------------
CURSOR STATEMENT : 1

CURSOR STATEMENT : 1

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK


SQL> select f_tab('SECOND_TABLE') from dual;

F_TAB('SECOND_TABLE'
--------------------
CURSOR STATEMENT : 1

CURSOR STATEMENT : 1

     EMPNO ENAME      JOB              SAL
---------- ---------- --------- ----------
      7369 SMITH      CLERK            800
      7566 JONES      MANAGER         2975
      7788 SCOTT      ANALYST         3000
      7876 ADAMS      CLERK           1100
      7902 FORD       ANALYST         3000


SQL>

Upvotes: 4

Related Questions