Total count of a field from all schemas grouped my month

You can use either SQL or a PL\SQL procedure for this.  Either way, you first need to query all_tab_columns (or dba_tab_columns, if you have access to that) to build a list of all of the table_names with the owner (schema_name) for each table that contains this particular column.  Then you can query each of these tables to get the count from each individual table, grouped by month.  This assumes that each of these tables has a DATE column that you can use for the "group by".  Or, is the column that you want the counts of actually a DATE column?

Do you have a preference for doing this in SQL (where you would use a query from all_tab_columns to generate the list of queries for each table) or in PL\SQL, where you would use a cursor loop based on all_tab_columns, then an "execute immediate..." query from each of these tables?

What exactly should be "grouped by month"?  Is that the counts from each table individually?  Or, do want the counts from all tables to be combined first, then "grouped by month"?

@Mark Geerlings
I do not have a preference on SQL or a PL\SQL procedure.

The table_name exists in each schema and the column is display_date. I actually would like it both ways: each table as schema_name grouped by month and I want all the counts from all the tables to be combined first, then grouped by months...

Can you explain with some sample data and expected output?

The way that I would approach this is with a script, in your preference of scripting languages.  That script would actually write a SQL script that would do what you are looking for and then use SQL*Plus to run it.  This can be a much more efficient way to do this than dynamic SQL.

The SQL doesn't change, just the schema, so the script is just copying the same statement over and over, connected by UNION ALL, and changing the schema each time.  Simple loop to get all the schemas that contain that table.

All of these solutions are going to assume that you can run as a privileged user that has access to all of the schemas involved.

As mentioned, sample data and results would go a long way into giving you at least a framework to start with.

Here is the sample data.  The "Display_Date" is the field that we will need to group and do the count on. The "Employer_Code" will be the same for the entire table but each schema represents a different employer_code (i.e. schema_1 = 10001, schema_2 = 10002, schema_ 3 = 10003, etc...).

So this sample date is schema_1...

This is the only table that we will be pulling data from.

I have this so far but this works for one schema. I would like to make this a loop because there are at least 100 different schemas and also order it by month name:

select to_char(display_date, 'YYYY-MON') as "YEAR-MON", count(*)
from schema_1.ep_pay_stubs
WHERE trunc(display_date) BETWEEN to_date('01/01/2019','MM/DD/YYYY') AND TO_DATE('10/31/2019','MM/DD/YYYY')
group by to_char(display_date, 'YYYY-MON');

Results for schema:
YEAR-MON      COUNT(*)
2019-JAN      175465
2019-FEB      171973
2019-MAY      176886
2019-JUN      175429
2019-JUL      187998
2019-AUG      183766
2019-SEP      273212
2019-OCT      185703
2019-MAR      173463
2019-APR      265839
sample_data.xlsx

So, this is the framework of how I would do it.  This is a UNIX shell script, but the idea can easily be adapted to any scripting language.

#!/bin/sh

priv_user="user"
priv_pass="pass"
x=`sqlplus -s ${priv_user}/${priv_pass} << EOF
set feed off
set pause off
set head off
select owner from dba_tables where table_name = 'EP_PAY_STUBS';
EOF`
iter=1
script_file="/tmp/script.sql"
echo "" > ${script_file}
for o in ${x}
do
  if [ ${iter} -ne 1 ] ; then
    echo "union all" >> ${script_file}
  else
    iter=2
  fi
  echo "select '${o}', to_char(trunc(display_date, 'MM'), 'YYYY-MON'), count(1) from ${o}.ep_pay_stubs where display_date between to_date('01012019','mmddyyyy') and to_date('10312019235959','mmddyyyyhh24miss') group by to_char(trunc(display_date, 'MM'), 'YYYY-MON')" >> ${script_file}
done
echo ";" >> ${script_file}

sqlplus ${priv_user}/${priv_pass} @${script_file}

Select allOpen in new window

Again, that is a framework.  There is no error checking and user and password should really not be used that way, but it will work.

I'm not sure why "they" designed the database that way but that is a different story.

I think it can be done in a single query.  It will likely be slow but it shouldn't need a lot of scripting.

Give me a little time to mock something up.

That is why I went for the very short script solution.  Should be fairly quick and take advantage of as many indexing options as it can.

Just because it can be all done in SQL doesn't mean it should be.

I agree with the where clause change Mark suggested.  If there is an index on display_date this should use it.

See if this works for you:

with cte as (
	select
	xmlelement("root",
		xmlagg(
			xmltype(
				dbms_xmlgen.getxml('select to_char(display_date, ''YYYY-MON'') as year_mon, count(*) month_count from ' ||
					owner || '.' || table_name ||
					' WHERE display_date BETWEEN to_date(''01/01/2019'',''MM/DD/YYYY'') AND TO_DATE(''10/31/2019 23:59:59'',''MM/DD/YYYY HH24:MI:SS'')' ||
					' group by to_char(display_date, ''YYYY-MON'')'
				)
			)
		)
	) myxml
	from all_tables
	where table_name = 'EP_PAY_STUBS' 
)
select year_month, sum(month_count)
from cte, xmltable(
			'/root/ROWSET/ROW'
			passing myxml
			columns
				year_month			varchar2(8)		path 'YEAR_MON',
				month_count			number			path 'MONTH_COUNT'
		)
group by year_month
order by to_date(year_month,'YYYY-MON')
/

Select allOpen in new window

What I posted would use the index as well.  I made that change, but didn't note it.

What am I doing wrong...
I am doing it like this below:

DECLARE 
	startDate DATE := to_date('01/01/2019', 'mm/dd/yyyy');
	endDate DATE := to_date('10/31/2019', 'mm/dd/yyyy');
        month_cnt number := 0;
	year_month date := SYSDATE;	

BEGIN 
	
FOR cur_schema IN (select upper(schema) as "SCHEMA" from epayrolladmin.schema_translate t where t.employer_code in (10001,10002,10639))
	
    loop						
		
		execute immediate 'select to_char(display_date, "YYYY-MON"), count(*) from ' || cur_schema.SCHEMA || '.ep_pay_stubs ' ||
				'WHERE trunc(display_date) BETWEEN ' || startDate || ' AND ' || endDate || ' group by to_char(display_date, "YYYY-MON")'
				into year_month, month_cnt; 
				
		dbms_output.put_line (cur_schema.SCHEMA || ' ' ||  month_cnt);

	END LOOP;	
   	
END;
/

show errors;

Select allOpen in new window

Results:
DECLARE
*
ERROR at line 1:
ORA-00904: "YYYY-MON": invalid identifier
ORA-06512: at line 20

which is the display_date in the group by

Oracle cannot use double quotes for string literals.

Use two single quotes to get one in a string.  Two single quotes looks like a double quote but it isn't.

Change:
to_char(display_date, "YYYY-MON")

to (in both places):
to_char(display_date, ''YYYY-MON'')


Did you try the query I posted?  It is a single select with no code necessary.

@slightwv

The script that you sent errors"


                        xmltype(
                        *
ERROR at line 5:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "SYS.XMLTYPE", line 272
ORA-06512: at line 1

My script errors:
DECLARE
*
ERROR at line 1:
ORA-00904: "OCT": invalid identifier
ORA-06512: at line 20
(still at the group by line)

>>"OCT"

That is using double quotes.  Since it isn't in the last script you posted, I cannot suggest the correct syntax.  My guess is you need two single quotes.

'' is not the same as " even though they look the same...

>>ORA-06502: PL/SQL: numeric or value error

What is your Oracle version?

I can post my complete test case where I tested the SQL I posted if it would help.
or
You can post a table definition and I can set up a test case based on that.

@slightwv

I am sorry but I do not have any double quotes in my query:

execute immediate 'select to_char(display_date, ''YYYY-MON''), count(*) from ' || cur_schema.SCHEMA || '.ep_pay_stubs ' ||
			'WHERE trunc(display_date) BETWEEN ' || startDate || ' AND ' || endDate || ' group by to_char(display_date, ''YYYY-MON'')'
			into year_month, month_cnt;

Select allOpen in new window

I am using version 11.2

Here is my XML testcase against 11.2:
https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=3af7a4c55035ebfde66692bc83fdf446

Did you modify the query I posted in any way?

>>I  am sorry but I do not have any double quotes in my query:

Try this:

DECLARE 
	startDate varchar2(10) := '01/01/2019';
	endDate varchar2(10) := '01/01/2019';
        month_cnt number := 0;
	year_month varchar2(8);

BEGIN 
	FOR cur_schema IN (select upper(schema) as SCHEMA from epayrolladmin.schema_translate t where t.employer_code in (10001,10002,10639))

		execute immediate 'select to_char(display_date, ''YYYY-MON''), count(*) from ' || cur_schema.SCHEMA || '.ep_pay_stubs ' ||
			'WHERE trunc(display_date) BETWEEN to_date(:startDate, ''mm/dd/yyyy'') AND to_date(:endDate,''mm/dd/yyyy'') group by to_char(display_date, ''YYYY-MON'')'
			into year_month, month_cnt
			using startDate, endDate;

		dbms_output.put_line (cur_schema.SCHEMA || ' ' ||  month_cnt);
	END LOOP;	
   	
END;
/

Select allOpen in new window

@slightwv..

Error:

EPAYROLLQA1 1
DECLARE
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 16

It worked the first time but seems like error came after first loop:

DECLARE 
	startDate varchar2(10) := '01/01/2019';
	endDate varchar2(10) := '10/31/2019';
    month_cnt number := 0;
	year_month varchar2(8);
	
BEGIN 

	FOR cur_schema IN (select upper(schema) as SCHEMA from epayrolladmin.schema_translate t where t.employer_code in (10001,10002,10639))
	LOOP
		execute immediate 'select to_char(display_date, ''YYYY-MON''), count(*) from ' || cur_schema.SCHEMA || '.ep_pay_stubs ' ||
			'WHERE trunc(display_date) BETWEEN to_date(:startDate, ''mm/dd/yyyy'') AND to_date(:endDate,''mm/dd/yyyy'') group by to_char(display_date, ''YYYY-MON'')'
			into year_month, month_cnt
			using startDate, endDate;

		dbms_output.put_line (cur_schema.SCHEMA || ' ' ||  month_cnt);
	END LOOP;	
	--dbms_output.put_line('DELETED ' || total_records_cnt || ' TOTAL RECORDS!!');
   	
END;
/

Select allOpen in new window

The no data found means the second schema in the loop likely didn't have any data.  That is something you need to debug because I don't have your tables or data.

Easiest is add an exception handler to ignore the no_data_found exception.

I'm still against the code-based method because you will still have to write the code that aggregates all the results from your loop then display them properly.

@slightwv...

I am not sure how to get yours to work

Did you modify my query before it generated the ORA-06502 or did you run it as-posted?

As is

How many rows are you expecting back from each table?

Not sure, maybe a few thousand from each schema because it is only for 10 months but it would be grouped by months so only 10 rows to display

What do you do for the exception to have it to continue if no data found?

EXCEPTION    
    WHEN NO_DATA_FOUND THEN <code2>;

EXCEPTION    
    WHEN NO_DATA_FOUND THEN null;

I don't have any 11g to test with.

See if this runs:

with cte as (
	select
	xmlelement("root",
		xmlagg(
				dbms_xmlgen.getxmltype('select to_char(display_date, ''YYYY-MON'') as year_mon, count(*) month_count from ' ||
					owner || '.' || table_name ||
					' WHERE display_date BETWEEN to_date(''01/01/2019'',''MM/DD/YYYY'') AND TO_DATE(''10/31/2019 23:59:59'',''MM/DD/YYYY HH24:MI:SS'')' ||
					' group by to_char(display_date, ''YYYY-MON'')'
				)
		)
	) myxml
	from all_tables
	where table_name = 'EP_PAY_STUBS' 
)
select year_month, sum(month_count)
from cte, xmltable(
			'/root/ROWSET/ROW'
			passing myxml
			columns
				year_month			varchar2(8)		path 'YEAR_MON',
				month_count			number			path 'MONTH_COUNT'
		)
group by year_month
order by to_date(year_month,'YYYY-MON')
/

Select allOpen in new window

I upped the 11g testcase on uk fiddle to 50,000 rows and it runs there:
https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=649806f0084b60680c00c357796849f7

I only get one row:
YEAR_MON SUM(MONTH_COUNT)
-------- ----------------
2019-JUL                1

Where does the where clause go for the schemas (owner)?

>>I only get one row:

There are two different SQL's in discussion:  Your PL/SQL and my XML version.  Which one is only returning the one row?

I like the PL\SQL option because it can get the monthly totals for each schema plus the overall totals by month with just one pass through the data.  Here is a PL\SQL procedure to do that:
(Note that I guessed six digits would be enough  to display the totals by month in each schema, and that eight digits would be enough for the overall totals by month.  If your values are larger than that, you will need to increase the number of "9" characters in the format masks for the to_char conversions.)

create or replace procedure monthly_totals (start_date in date, end_date in date) as
  cursor get_schemas is
	select owner from dba_tab_columns
	where column_name = 'DISPLAY_DATE'
	order by owner;
  v_owner    varchar2(30);
  v_yrmon    varchar2(14);
  v_month    varchar2(3);
  v_count    pls_integer;
  v_jantot   pls_integer := 0;
  v_febtot   pls_integer := 0;
  v_martot   pls_integer := 0;
  v_aprtot   pls_integer := 0;
  v_maytot   pls_integer := 0;
  v_juntot   pls_integer := 0;
  v_jultot   pls_integer := 0;
  v_augtot   pls_integer := 0;
  v_septot   pls_integer := 0;
  v_octtot   pls_integer := 0;
  v_novtot   pls_integer := 0;
  v_dectot   pls_integer := 0;
  qry_prefix varchar2(80) := 'select to_char(display_date, ''YYYY-MM-MON''), count(*) from ';
  qry_suffix varchar2(160) := '.ep_pay_stubs WHERE display_date BETWEEN start_date AND '
	||'trunc(end_date) + 86399/86400 group by to_char(display_date, ''YYYY-MM-MON'') order by 1');
  full_text  varchar2(270);
begin
  open get_schemas;
  loop
    fetch get_schemas into v_owner;
	exit when get_schemas%notfound;
	full_text := qry_prefix||v_owner||qry_suffix;
	execute immediate(full_text) into v_yrmon, v_count;
	dbms_output.put_line(substr(v_yr_mon,1,5||substr(v_yr_mon,9-3)||to_char(v_count,'999,999');
	if substr(v_yr_mon,6,2) = '01' then
	  v_jantot := v_jantot + v_count;
	elsif substr(v_yr_mon,6,2) = '02' then
	  v_febtot := v_febtot + v_count;
	elsif substr(v_yr_mon,6,2) = '03' then
	  v_martot := v_martot + v_count;
	elsif substr(v_yr_mon,6,2) = '04' then
	  v_aprtot := v_aprtot + v_count;
	elsif substr(v_yr_mon,6,2) = '05' then
	  v_maytot := v_maytot + v_count;
	elsif substr(v_yr_mon,6,2) = '06' then
	  v_juntot := v_juntot + v_count;
	elsif substr(v_yr_mon,6,2) = '07' then
	  v_jultot := v_jultot + v_count;
	elsif substr(v_yr_mon,6,2) = '08' then
	  v_augtot := v_augtot + v_count;
	elsif substr(v_yr_mon,6,2) = '09' then
	  v_septot := v_septot + v_count;
	elsif substr(v_yr_mon,6,2) = '10' then
	  v_octtot := v_octtot + v_count;
	elsif substr(v_yr_mon,6,2) = '11' then
	  v_novtot := v_novtot + v_count;
	else then
	  v_dectot := v_dectot + v_count;
	end if;
  end loop;
  close get_schemas;
-- Now print the totals for each month:
  dbms_output.put_line('Jan '||to_char(v_jantot,'99,999,999');
  dbms_output.put_line('Feb '||to_char(v_febtot,'99,999,999');
  dbms_output.put_line('Mar '||to_char(v_martot,'99,999,999');
  dbms_output.put_line('Apr '||to_char(v_aprtot,'99,999,999');
  dbms_output.put_line('May '||to_char(v_maytot,'99,999,999');
  dbms_output.put_line('Jun '||to_char(v_juntot,'99,999,999');
  dbms_output.put_line('Jul '||to_char(v_jultot,'99,999,999');
  dbms_output.put_line('Aug '||to_char(v_augtot,'99,999,999');
  dbms_output.put_line('Sep '||to_char(v_septot,'99,999,999');
  dbms_output.put_line('Oct '||to_char(v_octtot,'99,999,999');
  dbms_output.put_line('Nov '||to_char(v_novtot,'99,999,999');
  dbms_output.put_line('Dec '||to_char(v_dectot,'99,999,999');  
end;
/

Select allOpen in new window

I'm also curious how this PL\SQL approach compares to a SQL-only approach in terms of performance in your system.

@Mark

62/62 PLS-00103: Encountered the symbol ";" when expecting one of the following: . ( ) , * % = - + < / > at in is mod remainder not rem =>

This happens at every line here:
dbms_output.put_line('Jan '||to_char(v_jantot,'99,999,999');
  dbms_output.put_line('Feb '||to_char(v_febtot,'99,999,999');
  dbms_output.put_line('Mar '||to_char(v_martot,'99,999,999');
  dbms_output.put_line('Apr '||to_char(v_aprtot,'99,999,999');
  dbms_output.put_line('May '||to_char(v_maytot,'99,999,999');
  dbms_output.put_line('Jun '||to_char(v_juntot,'99,999,999');
  dbms_output.put_line('Jul '||to_char(v_jultot,'99,999,999');
  dbms_output.put_line('Aug '||to_char(v_augtot,'99,999,999');
  dbms_output.put_line('Sep '||to_char(v_septot,'99,999,999');
  dbms_output.put_line('Oct '||to_char(v_octtot,'99,999,999');
  dbms_output.put_line('Nov '||to_char(v_novtot,'99,999,999');
  dbms_output.put_line('Dec '||to_char(v_dectot,'99,999,999');

Simple typo missing close paran for DBMS_OUTPUT.

dbms_output.put_line('Jan '||to_char(v_jantot,'99,999,999'));

I am drained.

Where do I declare v_yr_mon

PLS-00201: identifier 'V-YR-MON' must be declared

Problem is that V_YRMON is declared and then used as V_YRMON and as V_YR_MON.  I think you need to go through and change V_YR_MON to V_YRMON.

Another simple typo.  It is already declared.

change:
 v_yrmon    varchar2(14);


to:
 v_yr_mon    varchar2(14);

Yes, but it is used as both names, you have to change the code somewhere.  It is referenced with both names:

execute immediate(full_text) into v_yrmon, v_count;

if substr(v_yr_mon,6,2) = '01' then

These are all really simple syntax errors.  You should be able to find and fix them yourself.

I figured that so did I did that and nothing prints. Nothing prints to the screen or spool file

>>I figured that so did I did that and nothing prints.

The code Mark posted creates a procedure.  Did you execute after you created it?

What tool are you using?

If SQL*Plus, did you enable DBMS_OUTPUT by using the "set serverout on" command?  If you didn't, everything will run and you'll see nothing.

exec monthly_totals (to_date('01/01/2019','MM/DD/YYYY'), TO_DATE('10/31/2019','MM/DD/YYYY'));

I am using SQLPLUS and yes I have : set serveroutput on size unlimited.

Results:
Procedure created.
No errors.

After you created it, did you run it like this:
exec monthly_totals (to_date('01/01/2019','MM/DD/YYYY'), TO_DATE('10/31/2019','MM/DD/YYYY'));

If that still returns nothing, does this query return the schema names you expect?
     select owner from dba_tab_columns
      where column_name = 'DISPLAY_DATE'
      order by owner;

Does this sample PL\SQL block display "Hello there"?

begin
  dbms_output.put_line('Hello there');
end;
/

Note the slight correction (no "procedure" after "exec").

If you create this simpler procedure, then run it (with the command below the code block) do you get the schema names displayed?

create or replace procedure test_owners as
  cursor get_schemas is
	select owner from dba_tab_columns
	where column_name = 'DISPLAY_DATE'
	order by owner;
  v_owner    varchar2(30);
begin
  open get_schemas;
  loop
    fetch get_schemas into v_owner;
    exit when get_schemas%notfound;
    dbms_output.put_line(v_owner);
  end loop;
  close get_schemas;
end;
/

Select allOpen in new window

exec test_owners;

after it is created I exec procedure monthly_totals (to_date('01/01/2019','MM/DD/YYYY'), TO_DATE('10/31/2019','MM/DD/YYYY')); at the cmd line

I have attached a print screen

I have never ran a stored procedure so I think I am doing this correct
Capture.PNG

You don't need the word "procedure" in the "exec..." command.  You just need the name of the procedure (and any parameter values) on that "exec …" line.

Try the simpler procedure to confirm that you see the schema names displayed.

Sorry, I had a copy/paste error.  I corrected my post.

I just noticed something I missed in both the simpler procedure and the big one.  The "get_schemas" cursor must include this line:
  and table_name = 'EP_PAY_STUBS'
like this:

create or replace procedure test_owners as
  cursor get_schemas is
	select owner from dba_tab_columns
	where column_name = 'DISPLAY_DATE'
        and table_name = 'EP_PAY_STUBS'
	order by owner;
  v_owner    varchar2(30);
begin
  open get_schemas;
  loop
    fetch get_schemas into v_owner;
	exit when get_schemas%notfound;
	dbms_output.put_line(v_owner);
  end loop;
  close get_schemas;
end;
/

Select allOpen in new window

It works but some reason it is trying to concatenate the schema_name with the the procedure name.

View attachment

I cannot see why
Capture.PNG

No, the error message just gives you full name of the procedure in Oracle's complete: [schema_name].[procedure_name] syntax.  That is not the problem.  That just tried to tell you explictly which procedure has the problem.  In theory, any other schema in your database could also have a procedure named: "monthly_totals".

Please add the line:
  and table_name = 'EP_PAY_STUBS'
to both "monthly_totals"  and to the simpler schema I posted,  Then try running the simpler schema to confirm that displays each schema name on a separate line.  If that works then we need to figure out what is wrong with the dynamic query in the "execute immediate..." command.

Error at line 1:
ORA-06502: PL?SQL: numeric or value error: character string buffer too small

line 23

I changed the buffer now "NO DATA FOUND"

Would do I do if one of the schemas returns no data

I need an exception handler... where do it go?

Yes, on line 23, if you make that variable larger (180 should be enough there) that should be enough.  Then you may also need to make the "full_text" variable (on line 25) 20 bytes longer also.

To get around an exception on the "execute immediate..." line you need to enclose that in a separate "begin ...exception... end" sub-block.  The following "dbms_output…" line and the multi-line "if..." check should also be in the same separate sub-block.  So replace the lines from
"loop" to "end loop;" with these lines

  loop
    fetch get_schemas into v_owner;
	exit when get_schemas%notfound;
	full_text := qry_prefix||v_owner||qry_suffix;
	begin
	  execute immediate(full_text) into v_yrmon, v_count;
	  dbms_output.put_line(substr(v_yrmon,1,5||substr(v_yrmon,9-3)||to_char(v_count,'999,999');
	  if substr(v_yrmon,6,2) = '01' then
		v_jantot := v_jantot + v_count;
	  elsif substr(v_yrmon,6,2) = '02' then
		v_febtot := v_febtot + v_count;
	  elsif substr(v_yrmon,6,2) = '03' then
		v_martot := v_martot + v_count;
	  elsif substr(v_yrmon,6,2) = '04' then
		v_aprtot := v_aprtot + v_count;
	  elsif substr(v_yrmon,6,2) = '05' then
		v_maytot := v_maytot + v_count;
	  elsif substr(v_yrmon,6,2) = '06' then
		v_juntot := v_juntot + v_count;
	  elsif substr(v_yrmon,6,2) = '07' then
		v_jultot := v_jultot + v_count;
	  elsif substr(v_yrmon,6,2) = '08' then
		v_augtot := v_augtot + v_count;
	  elsif substr(v_yrmon,6,2) = '09' then
		v_septot := v_septot + v_count;
	  elsif substr(v_yrmon,6,2) = '10' then
		v_octtot := v_octtot + v_count;
	  elsif substr(v_yrmon,6,2) = '11' then
		v_novtot := v_novtot + v_count;
	  else then
		v_dectot := v_dectot + v_count;
	  end if;
	exception
	  when others then
		dbms_output.put_line(sqlerrm)
		dbms_output.put_line('.on schema: '||v_owner)
	end;
  end loop;

Select allOpen in new window

The simple script seems like a better idea......

Just get one query working for one table, stick it in the script and it will put them all together.

@johnsone
That is what I am about to do!!!!

@Mark
I have verified that I have data but this script is returning no data found for everything. View attachment
Capture.PNG

Or if you add these five lines in the "exception" section, after the line: "dbms_output.put_line('.on schema: '||v_owner)", they will show us the exact SQL command that failed (and the procedure will stop after the first error because of the "raise" line there, if you want it to - otherwise don't include the "raise;" line there).  That would give us a clue to a possible syntax error.

		dbms_output.put_line(substr(full_text,1,75);
		dbms_output.put_line(substr(full_text,76,150);
		dbms_output.put_line(substr(full_text,151,225);
		dbms_output.put_line(substr(full_text,226,300);
		raise;

Select allOpen in new window

That should give you a SQL command that you could copy into SQL*Plus and execute manually.  (You may have to copy it first to a text editor and remove the line-feed characters, then optionally add line feed characters manually that don't break up words).  If you are confident that each schema has records for this time period, then Oracle should not return a "NO_DATA_FOUND" error.

No data found ... see attachment

I thought we handled this?
Capture.PNG

Please post the code as it is now.

And if you copy the text of that SQL query into a text editor to clean up the line breaks that break up words, then past that query into SQL*Plus (and add a trailing semi-colon after the “order by 1”) what does that return?

I just realized that this PL\SQL approach uses a combination I've never actually tried before: a dynamic query that returns two column values for *MULTIPLE ROWS*.   I don't think that will work exactly as I suggested when multiple rows of output are expected from the dynamic query.  Here is a link that describes how that can be done, using: BULK COLLECT, a type variable, and a PL\SQL table of those "type" values.  
https://livesql.oracle.com/apex/livesql/file/content_C3WO8BUQ7WYBOE21UQ6ZZN91R.html
But then you would need to process that array to retrieve and display the individual monthly totals, and add them to your overall totals by month.

So maybe this task will simplest in SQL, using a query of dba_tab_columns to create the "select..." statements for each schema, then running those.  Getting combined totals for each month (across all schemas) may still be challenging though in SQL.

I just thought of another option that would make this problem very easy to solve in PL\SQL: create a view that uses a “union all” query with multiple sections to select the values from all schemes.

You can get multiple rows from dynamic SQL by using a cursor.  Not sure if you can do that without DBMS_SQL though, which opens a big can of worms.

How would a view be dynamic enough to account for a new schema being added?

The script approach that I posted a very long time ago does the build all the queries with a UNION ALL.  Not fancy, not all in SQL, but it should work and seems a lot easier to follow.

and the "simplest" is a single SELECT.

Another potential issue with the code approach that isn't covered in this question:
How will the output of this report be consumed?  
What is the actual end-game for the data?

If getting the data is only step 1 in a multi step process, then a code approach can become more complicated than it already is.

Natavia,

The more you can tell us about the requirements, the better and more accurate our advice.

I would still like the table definitions I asked for several posts ago.  I'm still betting the XML SQL is the best solution.  Just need to figure out the error you are seeing.

I could not get the exceptions to work and was against the clock so I had to do the simple select 65 times.

But before I go I have to give a  HUGE shout out to @Mark Geerlings, @johnsone and @slightwv.  They really got involved and this is not  the first time that these three have come through for me.  They really know what they are doing and I like the fact that they help me to understand what I am doing.  I am more of a .NET developer so this new role that I have taken on with the database at my company it is good to have people like this near.

Thank you Mark, johnsone and slightwv for EVERYTHING! And thanks for being there. You have no ideal how much you are truly appreciated.... Keep up the good work!!!!

The questions from slightwv are good questions.  And usually if SQL can get the job done, that is more efficient than using PL\SQL.  But in this case, where counts by month within each schema are desired along with totals for each month across all schemas, that may be difficult to accomplish in a single SQL query, based on a single pass through all of the data.

So, I would like to see this tested both ways: the query from slightwv that uses XML SQL, and a PL\SQL procedure that uses a single query through a view, so no dynamic SQL is required.

True, the view would not dynamically handle new schemas, but it is easy to do a "create or replace..." command on the view definition to add a new schema when necessary.

(No I didn't see Natavia's post of 30 minutes ago before I posted this.  I had started writing this, then got interrupted, so by time I posted it, it looks like I wrote this after her post, but that's not true.  I edited this to add these lines in parenthesis after I saw her post.)

That is why I don't like the view solution.  Someone has to remember to change the view when a new schema is added.  I have to say that I got into a nasty recovery situation because there was a hard coded backup script and new files weren't added to it.  The more someone has to remember the more error prone it is.

Now that you mention that you are familiar with .NET, I would recommend giving building the giant SQL a try.  Take a look at the script that I posted here.  The idea is that you get the query to work with one schema.  Then have a loop that gets all the owners of that table and build the SQL with UNION ALL in between each one.  Then fire it off.  Not pretty.  Not a fancy one query solution, but it should be as fast or faster than some of the other solutions.  Scripts/programs that write their own code or queries and execute them are a good thing to know.  You can build very efficient code and queries in a controlled environment and not have to jump through all the hoops.

You're welcome for all the help.  The three of us have been on this site for a long time, and I know at least I like to help people learn how to do it themselves.  All of us like these quirky things to try to figure out.

Just remember, the more information you post up front the easier it is to get a solution.  As much detail as you can and sample data and expected results go a long way to getting help.  What you already tried is very helpful as well.  Sample data in the form of runable SQL statements is always much appreciated, sometimes people that are trying to help don't have the time to build your test case if they have to figure a way to load your data.

Thank you both. I am not a quitter so I am going to continue to get both of your methods to work.  I can't stop now because I know this will come along again.

both?  My money is still on the XML SQL.

@slightwv,
I am working on that one now.  And it works!!!! Thank you.

Is it possible to have it group by schema then by month (like it is doing now)?

@slightwv, you are the $$$$.  It took me over an hour to do this yesterday. They both work!! This is exactly what I needed....

Always happy to help.

To do this with a view:

create or replace view schema_month_qty
(schema_name, display_date)
as
select 'AMERIPRISE', display_date
from AMERIPRISE.ep_pay_stubs
union all
select 'AMER_GREET', display_date
from AMER_GREET.ep_pay_stubs
union all
...
[repeat the "select..." query for each schema,
with "union all" between each one]
...
select 'LAST_SCHEMA', display_date
from LAST_SCHEMA.ep_pay_stubs;

Then your report becomes a very simple SQL query:

select schema_name, to_char(display_date,'YYYY-MM-MON'), count(*) Qty
from schema_month_qty
where display_date BETWEEN '&start date' AND '&end_date'
group by schema_name, to_char(display_date,'YYYY-MM-MON')
order by schema_name, to_char(display_date,'YYYY-MM-MON');

This doesn't get you the totals for all months across all schemas, but that is easy to do in PL\SQL with this query instead of the more-complex dynamic query we tried earlier, that required "execute_immediate" (using the view avoids that).

to slightwv:
I'm not clear on what value XML SQL adds for this problem.  I admit, I haven't used XML SQL.  Does that support getting totals by month for each schema plus overall totals in just one pass through the data like this view plus a PL\SQL procedure can do?  Or does that just make it easier to execute a dynamic query in each schema?

@Mark,

It should only access each table only once.  It will take the string provided to getxmltype and execute it.  To confirm, you would need to trace the session and look.

@Slightwv
"It should only access each table only once".

OK, the view plus PL\SQL procedure option also accesses each table just once.  But the procedure can also accumulate totals by month across all schemas during that one pass through the data.  Does your query also get these totals by month across all schemas in just one pass?

@Mark, I still would like to get the procedure that we were working on yesterday to work as well.

The easiest way to get a procedure similar to that to work, requires creating a view like I described an hour ago.  Then the procedure can use just one query from this view with no "execute immediate..." complexity needed.  To use the procedure without the view will require adding quite a bit of complexity to the procedure (bulk collect, types, PL\SQL tables of types, stepping though the PL\SQL table for each schema to extract the individual monthly totals, etc.).  I don't recommend adding that level of complexity to the procedure.  I always prefer the KISS (Keep It Simple Smarty) approach.  (Yes, some people use a different, six-letter, non-complimentary adjective that starts with "s" there.)

To me, one view and a simple query in the procedure is as simple as we can get.

>>Does your query also get these totals by month across all schemas in just one pass?

I assume so?

The output from the last one I posted against my test case was:

SCHEMA_1                       2019-JAN                1
SCHEMA_2                       2019-JAN                1
SCHEMA_3                       2019-JAN                1
SCHEMA_1                       2019-FEB                2
SCHEMA_2                       2019-MAR                3
SCHEMA_3                       2019-APR                4
SCHEMA_1                       2019-MAY           600000

Select allOpen in new window

You "assume so?"

I'm disappointed,  I expected to hear a definite "yes" or "no" from you.  And in the output you posted, I only see totals by month for individual schemas.  I don't see overall totals by month across all schemas.

lol!!!

I'm not 100% confident that I'm meeting all the requirements so, no definitive statement...

I think the overall total for all schemas requirement changed in #a42978819

My original SQL (#a42978078) totaled by ALL schemas based on the original requirement.

That output was:

2019-JAN                3
2019-FEB                2
2019-MAR                3
2019-APR                4
2019-MAY           600000

Select allOpen in new window

OK, that makes sense.  Your query may be the most-efficient way to meet the original requirement: a total for each month, within each schema.  And your query is dynamic, so if new schemas are added, they get included automatically.  Is it a simple or easy-to-understand query?  I wouldn't describe it that way.  But, I don't know of a simple way to do this job across all schemas in just one SQL query.

I think a "union" view across all schemas, and a (fairly simple) PL\SQL procedure that can return a total for each month within each schema plus accumulate overall totals by month, all in one pass through the data, is the easiest-to-understand approach, and may also be the most efficient way to meet the composite requirements.

(I could write a PL\SQL procedure that could dynamically recreate the view when needed, that includes new schemas, if any.  If this was a business problem in our system, that is likely the approach I would use.)

>> I wouldn't describe it that way.

If you've never done anything with XML, it can seem confusing  BUT, it really isn't overly bad once you start picking it apart to see what each piece does.

If you are interested, we can take this offline via PM.

If Natavia doesn't care if we hijack this question, I'll do it here in the open.

Let's see what Natavia says.  I'm not excited about learning to use XML in SQL, but that's partly because I'm now a "short-timer".  I plan to retire at the end of March next year, so I'm not looking to learn lots of new things now.  There are plenty business problems here that I can help solve yet with SQL, PL\SQL and my limited knowledge of Bash shell scripts.

@slightwv, I would love to learn more about this and no I do not mind if you hijack this problem. When can we set up a PM?

>>When can we set up a PM?

If you don't mind, I would say I explain it here in the question so it might help others in the future.  I just didn't want to SPAM you with stuff you didn't care about.

Life is hectic right now.  I'll try to remember to get back to this in a few days and see what I can come up with.

Thank you @slightwv!!! I am going to take the time to study this weekend. Is it okay if I reach out to you if I have any questions? I truly appreciate your support and the time that you took to do this.

Feel free to contact me in this question of via PM.