PL/SQL (MOSC)

MOSC Banner

help with pl/sql code

edited Aug 4, 2017 5:02AM in PL/SQL (MOSC) 11 commentsAnswered

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

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center