Home » SQL & PL/SQL » SQL & PL/SQL » multiple queries and 1 output window/result (Oracle SQL Developer 19.1.0.094 / Oracle Database 12.2.0.1.0)
icon5.gif  multiple queries and 1 output window/result [message #684293] Mon, 03 May 2021 09:50 Go to next message
abenitez77
Messages: 2
Registered: May 2021
Junior Member
I need to get the counts for multiple tables and I want to get my results into 1 sql worksheet so I can output to 1 excel output and not multiple sql worksheets. How can I get this query to do that?

Select count(*) from Table1;
Select count(*) from Table2;
Select count(*) from Table3;
Select count(*) from Table4;
Select count(*) from Table5;
Select count(*) from Table6;
Select count(*) from Table7;
Select count(*) from Table8;
Select count(*) from Table9;
Select count(*) from Table10;

Re: multiple queries and 1 output window/result [message #684294 is a reply to message #684293] Mon, 03 May 2021 10:24 Go to previous messageGo to next message
Michel Cadot
Messages: 67882
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Welcome to the forum.
Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Indent the code, use code tags and align the columns in result.

There are many solutions, here's (complex) one:
SQL> select column_value table_name,
  2          xmlcast(
  3            xmlquery(
  4              ('count(ora:view("SCOTT","' || column_value || '"))')
  5              returning content)
  6            as int) rows_in_table
  7  from table(sys.odcivarchar2list('EMP','DEPT','BONUS','SALGRADE'))
  8  /
TABLE_NAME      ROWS_IN_TABLE
--------------- -------------
EMP                        15
DEPT                        4
BONUS                       0
SALGRADE                    5

[Updated on: Mon, 03 May 2021 10:27]

Report message to a moderator

Re: multiple queries and 1 output window/result [message #684295 is a reply to message #684293] Mon, 03 May 2021 10:24 Go to previous messageGo to next message
John Watson
Messages: 8559
Registered: January 2010
Location: Global Village
Senior Member
Welcome to the forum.
Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.

You could use UNION ALL to combine them in to one query,
Select count(*) from Table1
union all
Select count(*) from Table2
union all
Select count(*) from Table3;
Re: multiple queries and 1 output window/result [message #684296 is a reply to message #684294] Mon, 03 May 2021 10:29 Go to previous messageGo to next message
abenitez77
Messages: 2
Registered: May 2021
Junior Member
Thank you both. Michael, could you explain what "SCOTT" value is for in this query you provided?

('count(ora:view("SCOTT","' || table_name || '"))')

Thanks!
Alex
Re: multiple queries and 1 output window/result [message #684297 is a reply to message #684294] Mon, 03 May 2021 11:16 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3061
Registered: January 2010
Location: Connecticut, USA
Senior Member
Michel,

Oracle XQuery function ora:view is desupported. Use XQuery functions fn:collection instead:

select  column_value table_name,
        xmlcast(
                xmlquery(
                         ('count(fn:collection("oradb:/SCOTT/' || column_value || '"))')
                         returning content
                        )
                as int
               ) rows_in_table
  from table(sys.odcivarchar2list('EMP','DEPT','BONUS','SALGRADE'))
/

TABLE_NAME                     ROWS_IN_TABLE
------------------------------ -------------
EMP                                       14
DEPT                                       4
BONUS                                      0
SALGRADE                                   5

SQL>
SY.
Re: multiple queries and 1 output window/result [message #684298 is a reply to message #684297] Mon, 03 May 2021 12:07 Go to previous messageGo to next message
Michel Cadot
Messages: 67882
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Thanks for the information, Solomon.

Re: multiple queries and 1 output window/result [message #684299 is a reply to message #684296] Mon, 03 May 2021 12:08 Go to previous message
Michel Cadot
Messages: 67882
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

abenitez77 wrote on Mon, 03 May 2021 17:29
Thank you both. Michael, could you explain what "SCOTT" value is for in this query you provided?

('count(ora:view("SCOTT","' || table_name || '"))')

Thanks!
Alex

SCOTT is the owner of the tables here.

Use Solomon's query instead.

Or John's one if the table list is fixed.

[Updated on: Mon, 03 May 2021 12:09]

Report message to a moderator

Previous Topic: how to get the rows of different values of single column
Next Topic: Optimization of SQL
Goto Forum:
  


Current Time: Tue Jun 15 16:41:22 CDT 2021