Using variable as table name and pass into another sql query

Question

without going into details - the tools we use for performance test, it spits out several tables (name changes with every new run) with different type of data. One table has list of all the table names and data type.

I am going to use oracle table as an example so it can be easily explained.

What I am wanting to do...

Query all_table like: QueryA:

select table_name from all_tables where table_name like 'HZ_CUST_%'

    Result will say: Table_name = 'HZ_CUST_ACCOUNTS'

Query using the QueryA result like:

Select * from [QUERYA_RESULT] WHERE creation_date > sysdate, account_number between '500000' and '599999'

I got something like this but not quite sure how to do this.

declare  variable TABLE_NAME CHAR(10);
SELECT TABLE_NAME into :V_NAME from all_tables WHERE TABLE_NAME LIKE 'HZ_CUST_ACCOUNTS';
select *
  from :V_NAME;

Thank you for the help.

Parameters cannot be used for table and column names. Your only option would be dynamic SQL -- using `execute immediate`. — Gordon Linoff, Sep 15 '20 at 00:10
sorry i am not familiar with dynamic sql, how would I go about doing that? — Mochi79, Sep 15 '20 at 00:15
What are you going to do with the results of `select * from :v_name`? If you're just going to print it out in your `SQL*Plus` script, you can use dynamic SQL to open a cursor and then print the cursor. If you actually plan on doing something with the results, though, the approach needs to change — Justin Cave, Sep 15 '20 at 00:22
You can use Polymorphic Table Function (PTF). There are a lot of examples in the official docs and on livesql.oracle.com. Another options: to use xmltable or dbms_xmlgen. I can provide examples tomorrow if you want — Sayan Malakshinov, Sep 15 '20 at 01:15
@SayanMalakshinov that will be great! if I can get some example. Thanks! — Mochi79, Sep 15 '20 at 02:46
@JustinCave yes table contains some performance test results. which i was planning on either export it out to excel to do some calculations or do the calculation within the sql statement such as max(time), min(time) etc — Mochi79, Sep 15 '20 at 02:47

Roberto Hernandez · Answer 1 · 2020-09-15T06:44:50.537

The easiest solution for your problem given what you have expressed in the comment section is to use DYNAMIC SQL

declare
v_table_name varchar2(130);
v_sql        clob;
begin
   -- get the table name
   select table_name into v_table_name from all_tables where table_name like 'HZ_CUST_%' ;
   -- Build dynamic statement
   v_sql := ' select * from '||v_table_name||' where creation_date > sysdate 
              and account_number between ''500000'' and ''599999'' ';
   execute immediate v_sql;
end;
/

From this small version, you can expand this code to whatever you need to do, specialy with the output. Of course, depending on what you need to do with it. I could for example store the maximum value of a field based on this same sentence.

declare
v_table_name varchar2(130);
v_sql        clob;
vmaxvalue     pls_integer;
begin
   -- get the table name
   select table_name into v_table_name from all_tables where table_name like 'HZ_CUST_%' ;
   -- Build dynamic statement
   v_sql := ' select max(table_field) from '||v_table_name||' where creation_date > sysdate 
              and account_number between ''500000'' and ''599999'' ';
   execute immediate v_sql into vmaxvalue;
end;
/

I don't believe you need for your case to use PTF ( Polymorphic Table Functions ) which are intended to evolve the concept of pipeline functions. However, as you did explain very well what you intent to do with the result of the query, you might want to have a look at them. Check this very good example of PTF ( Remember Oracle 18c or higher ).

Polymorphic Table Functions

Using variable as table name and pass into another sql query

1 Answers1