Oracle/SQL - Using query results as paramaters in another query -looping?

Question

Hi everyone what I'm wondering if I can do is create a table that lists the record counts of other tables. It would get those table names from a table. So let's assume I have the table TABLE_LIST that looks like this

name
---------
sports_products <-- contains 10 records
house_products  <-- contains 8 records
beauty_products <-- contains 15 records

I would like to write a statement that pulls the names from those tables to query them and coount the records and ultimately produce this table

name                numRecords
------------------------------
sports_products     10
house_products      8
beauty_products     15

So I think I would need to do something like this pseudo code

select *
from    foreach tableName in select name from table_list
            select count(*) as numRecords
            from tableName
        loop

Have to use dynamic SQL - SQL doesn't allow a variable to represent a table name. — OMG Ponies
– OMG Ponies, Commented Apr 7, 2011 at 14:50

Robert Giesecke · Accepted Answer · 2011-04-07 14:48:55Z

2

You can have a function that is doing this for you via dynamic sql.

However, make sure to declare it as authid current_user. You do not want anyone to gain some sort of privilege elevation by exploiting your function.

create or replace function SampleFunction
(
  owner     in VarChar
 ,tableName in VarChar
) return integer authid current_user is
  result Integer;
begin
  execute immediate 'select count(*) from "' || owner || '"."' || tableName || '"'
    INTO result;

  return result;
end;

answered Apr 7, 2011 at 14:48

Robert Giesecke

4,31424 silver badges22 bronze badges

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

Comments

nix · Accepted Answer · 2011-04-07 14:51:44Z

1

One option is to simply keep your DB statistics updated, use dbms_stats package or EM, and then

select num_rows
  from all_tables
 where table_name in (select name from table_list);

answered Apr 7, 2011 at 14:51

nix

3781 silver badge9 bronze badges

2 Comments

Mike Meyers Over a year ago

This will depend on that sample size for the statistics. If sample size of 100% is used for dbms_stat.gather_table_stats then it will be accurate until any change is made. If a lower sample size is used then even that can't be guaranteed.

TTT Over a year ago

I wouldn't rely on this. DBA's can for instance lock the statistics of a table.

Community · Accepted Answer · 2017-05-23 11:55:26Z

1

I think Robert Giesecke solution will work fine.

A more exotic way of solving this is by using dbms_xmlgen.getxml.

See for example: Identify a table with maximum rows in Oracle

edited May 23, 2017 at 11:55

CommunityBot

11 silver badge

answered Apr 7, 2011 at 19:05

TTT

2,37517 silver badges17 bronze badges

1 Comment

Robert Giesecke Over a year ago

Yep, that is pretty much the same principle. AFAIK, DBMS_XmlGen is declared as authid current_user, too. So there's also no privilege hassle involved.

Collectives™ on Stack Overflow

Oracle/SQL - Using query results as paramaters in another query -looping?

3 Answers 3

Comments

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related