Oracle: Dynamic SQL

Question

I am trying to write Oracle dynamic SQL to do row count, below is my approach, please help me complete.

This is my select to generate the individual count statements:

select 'select count(*) from '||table_name||';'
from dba_tables
where owner = 'URR';

This is how I started:

declare
 l_owner varchar2(30) := 'URR';
 l_table_name varchar2(30);
 l_columns varchar2(32000);
 l_sql varchar2(32000);
begin

• A.B.Cade: Read about execute immediate

Answer 1

Here's a simple example that looks at tables in your own schema:

set serveroutput on
declare
 c number;
begin
 for r in (select table_name from user_tables) loop
 execute immediate 'select count(*) from ' || r.table_name
 into c;
 dbms_output.put_line(r.table_name ||': '|| c);
 end loop;
end;
/

To look at someone else's tables you'll need to use dba_tables as you started to try, or more likely all_tables as that should exclude tables you can't count from, but you'll also need to specify the owner in the count statement.

Normally you'd want to use bind variables to avoid SQL injection, but you have to specify object names with concatenation like this.

Something else to look out for is a mistake you had in your query, but which Egor has now removed from the question. The dynamic SQL string you execute should not be terminated by a semicolon (;).