Скачать или смотреть How to Display Count of Tables in Oracle Database using SQL*Plus

Learn how to accurately display the count of multiple tables in an Oracle database through SQL*Plus. Follow our step-by-step guide to fix common issues.
---
This video is based on the question https://stackoverflow.com/q/72465809/ asked by the user 'Scott Zuris' ( https://stackoverflow.com/u/19112658/ ) and on the answer https://stackoverflow.com/a/72466208/ provided by the user 'Justin Cave' ( https://stackoverflow.com/u/10397/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: Display count of several tables in an Oracle database via SQL*Plus

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Displaying Counts of Tables in Oracle Database via SQL*Plus

When working with Oracle databases, it’s common to find a need to obtain the count of records from numerous tables that share similar names. Doing this efficiently can save time and provide valuable insights into your data. However, if you're using SQL*Plus, you may face challenges when trying to execute the required commands correctly. Let’s explore how you can accurately display the count of these tables by breaking down the process step by step.

Understanding the Problem

A user attempted to display the counts of all tables that contained names like %REF% or %CNF% in their Oracle database. They wrote a PL/SQL block, but they encountered an issue where they couldn’t see the expected output in SQL*Plus. The challenge lies in properly using the DBMS_OUTPUT functionality along with the PL/SQL commands.

Step-by-Step Solution

To resolve this problem, follow these steps to adjust your code properly:

1. Set Up Environment for Output

Before running your PL/SQL block, you have to enable the output for DBMS_OUTPUT. This is done using the SET SERVEROUTPUT ON command but must be executed outside of the PL/SQL block. Here’s how to do it:

[[See Video to Reveal this Text or Code Snippet]]

2. Correct the PL/SQL Block

After you've enabled the DBMS_OUTPUT, you need to modify the PL/SQL block to retrieve counts correctly. The original code had a minor syntax issue in how it utilized the EXECUTE IMMEDIATE statement. Here’s the corrected version:

[[See Video to Reveal this Text or Code Snippet]]

3. Explanation of Changes

DBMS_OUTPUT: SET SERVEROUTPUT must be outside the PL/SQL block for the command to work appropriately.

EXECUTE IMMEDIATE Syntax: The EXECUTE IMMEDIATE statement is specified correctly to store the count directly into the ctr variable.

Conclusion

By following the steps above, you can efficiently retrieve and display the count of multiple tables in your Oracle database using SQL*Plus. Remember to always ensure that SET SERVEROUTPUT ON is executed before your PL/SQL blocks to avoid missing output that you expect to see.

These adjustments not only rectify the issue you encountered but also enhance the overall readability and functionality of your code. Happy querying!