help with pl/sql code
i m trying to make the following work..
i have schemas called owner01 to owner31 and all schemas have same tables, i am trying to get the count of rows in each table in each schema.
set serveroutput on
DECLARE
val NUMBER;
stmt VARCHAR2(100) := null;
current_schema_name VARCHAR2(100) := null;
start_number PLS_INTEGER := 0;
ending_number PLS_INTEGER := 31;
BEGIN
FOR i IN start_number .. ending_number LOOP
current_schema_name := 'owner' || to_char(i, 'FM09') ;
stmt := 'ALTER SESSION SET CURRENT_SCHEMA = ' || current_schema_name;
EXECUTE IMMEDIATE stmt ;
EXECUTE IMMEDIATE 'SELECT count(*) FROM ENTITY_PERMISSION INTO val';
DBMS_OUTPUT.PUT_LINE( val );