Dynamic SQL to loop through same table in multiple schemas

Question

I'm trying to create a SQL statement which contains basic information from DBA_USERS for each schema and also select from a specific table in each schema as part of the same statement.

I have part of a statement cobbled together from other answers to similar questions on StackExchange:

DECLARE
  v_sql varchar2(4000);

  cursor c1 is
    select  o.owner
    ,       o.object_name
    ,       u.created
    ,       TO_CHAR(round(sum(ds.bytes)/1024/1024/1024,'0000'))||' GB'
    from    dba_users u
    ,       dba_objects o  
    ,       dba_segments ds
    WHERE   u.account_status = 'OPEN' 
        and u.DEFAULT_TABLESPACE not in ('SYSAUX','SYSTEM')
        and u.username=o.owner 
        and o.object_name='MASTER' 
        and o.object_type='TABLE'
        and ds.owner =o.owner;
BEGIN
    for REC in c1 loop
        v_sql := 'select VERSION from '||REC.owner||'.'||REC.object_name;
        EXECUTE IMMEDIATE v_sql;
    end loop;
END;
    /

This statement runs but won't show me any results as I believe it should be using a bulk collector and printing the output using DBMS_OUTPUT.PUT_LINE

The output should be something like this:

USERNAME    CREATED     SIZE    VERSION
SchemaA     2021-01-01  20GB    1.1
SchemaB     2021-01-02  22GB    1.2.2
SchemaC     2021-01-03  18GB    1.5.8

Firstly, how should I rewrite the statement above to output to the session, and secondly, is it possible to return the results I'm expecting?

If you are using at least Oracle 9i, then it is recommended to use ANSI join syntax. — Abra
– Abra, Commented Sep 23, 2021 at 16:33
you have a field VERSION on each table in the schema MASTER, right ? — Roberto Hernandez
– Roberto Hernandez, Commented Sep 23, 2021 at 17:15
@RobertoHernandez - not quite, in each schema, there is table "MASTER" (always named the same in every schema) containing a column "VERSION" which is a varchar2 datatype with a specific application version number. This can vary depending on schema (underpinning a specific application type) so the query just needs to be an at a glance overview of "version numbers" of all custom/Non-system schemas in the DB along with schema (not table) size and created date etc. — Huskie69
– Huskie69, Commented Sep 28, 2021 at 9:36

Roberto Hernandez · Accepted Answer · 2021-09-28 16:21:50Z

1

One option to get a result as you want would be to use pipelined functions. They deliver results in the form of a table.

By the way, your query is not completely right, as you need to join more elements. That is why is always best to use ANSI syntax. However, I would keep your syntax to make easier for you the explanation.

Let me show you an example. I don't have this field version, so I am using the counter of rows:

First we need to create the two types, one as an object and the other as table of. The first is the row, the second is the table construction.

SQL> CREATE OR REPLACE TYPE t_tf_row AS OBJECT ( username varchar2(40), created_date date, size_mb varchar2(10), counter number );
/

Type created.

SQL> CREATE TYPE t_tf_tab IS TABLE OF t_tf_row;
/

Type created.

Now, we create a pipelined function very similar to yours.

SQL> CREATE OR REPLACE FUNCTION get_schema_details RETURN t_tf_tab PIPELINED 
AS
 v_sql varchar2(4000);
 v_counter pls_integer;
BEGIN
    for h in 
    (  
    select  o.owner
    ,       o.object_name
    ,       u.created
    ,       round(ds.bytes/1024/1024/1024) as table_size
    from    dba_users u
    ,       dba_objects o  
    ,       dba_segments ds
    WHERE   u.account_status = 'OPEN' 
        and u.DEFAULT_TABLESPACE not in ('SYSAUX','SYSTEM')
        and u.username=o.owner 
        and u.username=ds.owner 
        and o.object_name = ds.segment_name 
        and o.object_type = ds.segment_type
        and o.object_name='ODSPOSTING' 
        and o.object_type='TABLE'
    ) 
    loop
        v_sql := 'select count(*) from '||h.owner||'.'||h.object_name;
        EXECUTE IMMEDIATE v_sql into v_counter;
        PIPE ROW(t_tf_row(h.owner,h.created,h.table_size,v_counter));   
    end loop;
END;
/

Function created.

SQL> select * from table(get_schema_details());

USERNAME                                 CREATED_D SIZE_MB       COUNTER
---------------------------------------- --------- ---------- ----------
ODSVIEWS                                 24-MAR-20 14           71853408
ALFAODS                                  20-DEC-19 14           71853408

You can make the function as dynamic as you want, for example introducing input parameters instead of hardcoding the values.

UPDATE

Your test case scenario

SQL> CREATE USER SCHEMA1 IDENTIFIED BY Oracle_1234
 DEFAULT TABLESPACE USERS
 TEMPORARY TABLESPACE TEMP_GROUP;  2    3

User created.

SQL> GRANT CREATE TABLE TO SCHEMA1;

Grant succeeded.

SQL> GRANT UNLIMITED TABLESPACE TO SCHEMA1;

Grant succeeded.

SQL> CREATE USER SCHEMA2 IDENTIFIED BY Oracle_1234
 DEFAULT TABLESPACE USERS
 TEMPORARY TABLESPACE TEMP_GROUP;  

User created.

SQL> GRANT CREATE TABLE TO SCHEMA2;

Grant succeeded.

SQL> GRANT UNLIMITED TABLESPACE TO SCHEMA2;

Grant succeeded.

SQL> CREATE TABLE SCHEMA1.MASTER(VERSION  VARCHAR2(6 BYTE));

Table created.

SQL> CREATE TABLE SCHEMA2.MASTER(VERSION  VARCHAR2(6 BYTE));

Table created.

SQL> INSERT INTO "SCHEMA1"."MASTER" (VERSION) VALUES ('1.1.0');

1 row created.

SQL> COMMIT;

Commit complete.

SQL> INSERT INTO "SCHEMA2"."MASTER" (VERSION) VALUES ('2.2.0');

1 row created.

SQL> COMMIT;

Commit complete.

Now we create the types and function.

SQL> CREATE OR REPLACE TYPE t_tf_row AS OBJECT ( username varchar2(40), created_date DATE, size_mb varchar2(10), counter NUMBER );
  2  /

Type created.

SQL> CREATE OR REPLACE TYPE t_tf_tab IS TABLE OF t_tf_row;
  2  /

Type created.

SQL> CREATE OR REPLACE FUNCTION get_master_version_details RETURN t_tf_tab PIPELINED
AS
 v_sql varchar2(4000);
  2    3    4   v_counter pls_integer;
BEGIN
  5    6      FOR h IN
    (
    SELECT  o.owner
  7    8    9      ,       o.object_name
    ,       u.created
    ,       round(ds.bytes/1024/1024/1024) AS table_size
 10   11   12      FROM    dba_users u
    ,       dba_objects o
    ,       dba_segments ds
 13   14   15      WHERE   u.account_status = 'OPEN'
        AND u.DEFAULT_TABLESPACE NOT IN ('SYSAUX','SYSTEM')
        AND u.username=o.owner
 16   17   18          AND u.username=ds.owner
        AND o.object_name = ds.segment_name
        AND o.object_type = ds.segment_type
 19   20   21          AND o.object_name='MASTER'
        AND o.object_type='TABLE'
    )
 22   23   24      loop
        v_sql := 'select count(*) from '||h.owner||'.'||h.object_name;
        EXECUTE IMMEDIATE v_sql INTO v_counter;
 25   26   27          PIPE ROW(t_tf_row(h.owner,h.created,h.table_size,v_counter));
    END loop;
END;
 28   29   30  /

Function created.

SQL> SELECT COUNT(*) FROM all_objects WHERE object_name='MASTER' AND object_type='TABLE';

  COUNT(*)
----------
         2

SQL> SELECT * FROM TABLE(get_master_version_details());

USERNAME                                 CREATED_D SIZE_MB       COUNTER
---------------------------------------- --------- ---------- ----------
SCHEMA1                                  28-SEP-21 0                   1
SCHEMA2                                  28-SEP-21 0                   1

Why in your case is not working ? You have to install the function and types within a user/schema with the right privileges to run the operations you are doing.

In my example above, as a test, I did install the function and the type on my sys schema ( something you should not do it ). So, let's drop the function and types, and create an additional user for that, we will call it schema3

SQL> DROP TYPE t_tf_tab;

Type dropped.

SQL> DROP TYPE t_tf_row;

Type dropped.

SQL> DROP FUNCTION get_master_version_details;

Function dropped.

SQL> create user schema3 identified by Oracle_1234 default tablespace users temporary tablespace temp_group ;

User created.

SQL> grant select any table, create procedure, create table, select any dictionary to schema3 ;

Grant succeeded.

SQL> CREATE OR REPLACE TYPE schema3.t_tf_row AS OBJECT ( username varchar2(40), created_date DATE, size_mb varchar2(10), counter NUMBER );
  2  /

Type created.

SQL> CREATE OR REPLACE TYPE schema3.t_tf_tab IS TABLE OF t_tf_row;
  2  /

Type created.

SQL> CREATE OR REPLACE FUNCTION schema3.get_master_version_details RETURN t_tf_tab PIPELINED
AS
 v_sql varchar2(4000);
 v_counter pls_integer;
BEGIN
  2    3    4    5    6      FOR h IN
  7      (
    SELECT  o.owner
    ,       o.object_name
  8    9   10      ,       u.created
    ,       round(ds.bytes/1024/1024/1024) AS table_size
    FROM    dba_users u
    ,       dba_objects o
    ,       dba_segments ds
    WHERE   u.account_status = 'OPEN'
 11   12   13   14   15   16          AND u.DEFAULT_TABLESPACE NOT IN ('SYSAUX','SYSTEM')
        AND u.username=o.owner
        AND u.username=ds.owner
 17   18   19          AND o.object_name = ds.segment_name
        AND o.object_type = ds.segment_type
        AND o.object_name='MASTER'
 20   21   22          AND o.object_type='TABLE'
 23      )
    loop
 24   25          v_sql := 'select count(*) from '||h.owner||'.'||h.object_name;
        EXECUTE IMMEDIATE v_sql INTO v_counter;
        PIPE ROW(t_tf_row(h.owner,h.created,h.table_size,v_counter));
 26   27   28      END loop;
END;
/ 29   30

Function created.

SQL> SELECT * FROM TABLE(schema3.get_master_version_details());

USERNAME                                 CREATED_D SIZE_MB       COUNTER
---------------------------------------- --------- ---------- ----------
SCHEMA1                                  28-SEP-21 0                   1
SCHEMA2                                  28-SEP-21 0                   1

Be aware of the privileges I granted to schema3 in order for the pipelined function to work.

edited Sep 28, 2021 at 16:21

answered Sep 23, 2021 at 17:47

Roberto Hernandez

8,5933 gold badges22 silver badges51 bronze badges

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

10 Comments

Roberto Hernandez Over a year ago

can you update the question with a reproducible example that I can test it ?

Roberto Hernandez Over a year ago

@Huskie69, I don't have permissions on my current network to access the chat service.

Roberto Hernandez Over a year ago

that I can work with

Roberto Hernandez Over a year ago

@Huskie69, the function has this line AND o.object_name='ODSPOSTING' and it should be AND o.object_name='MASTER'

Roberto Hernandez Over a year ago

@Huskie69, I will update my answer with your test case

|

William Robertson · Accepted Answer · 2021-09-23 19:06:32Z

0

The reason you are not 'seeing' any result is that PL/SQL operates entirely within the server. It has no connection to the client, and no means of accessing the client's output display. You need to use the DBMS_OUTPUT.PUT_LINE procedure (look it up in the docs). That procedure writes to a buffer that is then returned to the client when the procedure completes. It is then up to the client to deal with that buffer. If using sqlplus, you configure it to display that output by 'set serverout on' as a session setting before invoking any procedures.

Also, I'd rewrite your procedure to eliminate the explicit cursor and use a CURSOR FOR loop: (I'd also convert to user ANSI standard JOIN syntax, but I'm not going to spend time here analyzing the query to figure out exactly how to convert that_). Also, I don't see how the procedure runs at all, given that your SELECT inside the loop needs an INTO clause to have a place to put the result.

DECLARE
  v_sql varchar2(4000);
  v_version varchar2(80);

  BEGIN
    for REC in (select  o.owner
                       ,o.object_name
                       ,u.created
                       ,TO_CHAR(round(sum(ds.bytes)/1024/1024/1024,'0000'))||' GB'
                from    dba_users u
                       ,dba_objects o  
                       ,dba_segments ds
                WHERE   u.account_status = 'OPEN' 
                  and u.DEFAULT_TABLESPACE not in ('SYSAUX','SYSTEM')
                  and u.username=o.owner 
                  and o.object_name='MASTER' 
                  and o.object_type='TABLE'
                  and ds.owner =o.owner;) 
     loop
        v_sql := 'select VERSION from '||REC.owner||'.'||REC.object_name into v_version;
        EXECUTE IMMEDIATE v_sql;
        dbms_output.put_line('Version is '||v_version);
    end loop;
END;
/

edited Sep 23, 2021 at 19:06

William Robertson

16.1k4 gold badges40 silver badges49 bronze badges

answered Sep 23, 2021 at 17:55

EdStevens

3,9203 gold badges12 silver badges19 bronze badges

2 Comments

Steve Kass Over a year ago

I sure hope there are no malicious values of owner or object_name in the tables you're accessing. xkcd.com/327

EdStevens Over a year ago

@SteveKass - Your comment made me immediately think of Little Bobby Tables, I was looking up the link to post it before I noticed that you had already included it. :-)

William Robertson · Accepted Answer · 2021-09-23 19:14:18Z

0

The problem is

execute immediate v_sql;

which has no output. It needs an into clause, and something to display it:

declare
    demo_text varchar2(50);
begin
    execute immediate 'select 2 + 2 as demo from dual'
    into demo_text;

    dbms_output.put_line(demo_text);
end;

By the way, I recommend deciding between end; and END; (bearing in mind this isn't COBOL).

answered Sep 23, 2021 at 19:14

William Robertson

16.1k4 gold badges40 silver badges49 bronze badges

3 Comments

EdStevens Over a year ago

"(bearing in mind this isn't COBOL)" as an old, unrepentant COBOL programmer - who can't remember the last time he actually worked with it - I resemble that remark! ;-) (perhaps it would have been more appropriate to have written my comments in all caps, but in current nettiquete, that would have come off as shouting) :-) Actually I don't think the all caps was so much a COBOL issue as it was a limitation of the hardware in the early days. I do know I wrote a lot of COBOL in lower-case.

William Robertson Over a year ago

@EdStevens, my apologies for the casual slur against COBOL. I should have said COBOL-74 or whatever the last version was that required caps lock, or maybe Fortran. I read the Queen of COBOL blog post "UPPERCASE, lowercase" a couple of years ago so I should have known better.

EdStevens Over a year ago

@Willam Robertson - no apologies needed. I did include a couple of smileys to try to indicate a light-hearted attitude.

Collectives™ on Stack Overflow

Dynamic SQL to loop through same table in multiple schemas

3 Answers 3

10 Comments

2 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

10 Comments

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related