How to concatenate to form the table name from previous query in Oracle

Question

From the following script I am getting the project_name which need to form the part of the table name in my next query, how to do this in oracle

Query1:

SELECT PROJECT FROM testServer_l WHERE liveConnection LIKE '%test01.azure.cloud%'

Output of the above query is: 91869 90237 87732 103868 96995 102646 98178 100587 99887 90277 68240 103630 92756 83169 90070 96194

Query2:

SELECT COUNT(*) FROM T91869.TotalConnection

The table name is 'T' + 'ProjectName' [from the above query] for example T+91869

Answer 1

You'd - as you said - concatenate T to project. Use

• concat function, e.g. select concat('T', project)
  • drawback: concat accepts only two parameters, so - if you have to concatenate more than two of them, you'll have to nest function calls
• double pipe || concatenation operator (as I did)
  • benefit: concatenate as many strings as you want

---

However, that's not enough in what you're doing - you'll need dynamic SQL.

Here's an example:

Sample project tables:

SQL> create table t91869 as select * from emp;

Table created.

SQL> create table t90237 as select * from dept;

Table created.

List of projects:

SQL> create table testserver_l as
  2    select 91869 project from dual union all
  3    select 90237         from dual;

Table created.

Procedure:

SQL> set serveroutput on
SQL> declare
  2    l_cnt number;
  3  begin
  4    for cur_r in (select 'T' || project as project
  5                  from testserver_l
  6                 )
  7    loop
  8      execute immediate 'select count(*) from ' || cur_r.project into l_cnt;
  9      dbms_output.put_line(cur_r.project ||': '|| l_cnt);
 10    end loop;
 11  end;
 12  /
T91869: 14
T90237: 4

PL/SQL procedure successfully completed.

SQL>