CODEWITHSUNDEEP
oracle-databaseplsql
user1683763
user1683763

Reputation: 51

Oracle: Dynamic SQL

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

Upvotes: 1

Views: 450

Answers (1)

Alex Poole
Alex Poole

Reputation: 191560

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 (;).

Upvotes: 3

Related Questions