PL/SQL Show/List Databases

Listing all schemas in an Oracle Database can be a common requirement for database administrators and developers. PL/SQL doesn’t have a direct command for this task but by querying system views, we can achieve this goal. Below is the method which helps us to Show or List the Databases:

1. Using Querying the ALL_USERS View
2. Using Querying the DBA_USERS View

Syntax:

DECLARE
  v_schema_name VARCHAR2(100);                                      -- Declare a variable to store the schema name
BEGIN
  FOR user_rec IN (SELECT username FROM all_users) LOOP            -- Iterate over the result set of usernames from all_users view
    v_schema_name := user_rec.username; -- Assign the username to the variable
    DBMS_OUTPUT.PUT_LINE('Schema: ' || v_schema_name);              -- Output the schema name
  END LOOP;
END;
/

Explanation:

1. DECLARE keyword is used to the declare variables and other PL/SQL constructs.
2. v_schema_name is a variable declared to store schema name and retrieved from query result.
3. BEGIN and END keywords are mark the beginning and end of the PL/SQL block.
4. FOR user_rec IN (SELECT username FROM all_users) LOOP is a loop that constructs the iterates over the result set returned by query i.e. SELECT username FROM all_users. Each iteration will be retrieves a username and store it in variable i.e. v_schema_name.
5. DBMS_OUTPUT . PUT_LINE (‘Schema: ‘ || v_schema_name); line will be output the schema name using DBMS_OUTPUT . PUT_LINE procedure. The ” || ” operator is used to concatenation.
6. ” / “symbol is used indicate the end of the PL/SQL block and it will required to be execute the PL/SQL code in the SQL *Plus or SQL Developer.

Managing databases is a fundamental aspect of database administration and development. In Oracle Database, schemas represent logical containers for database objects like tables, views, procedures and functions. PL/SQL is the procedural extension of and used in Oracle Database and provides powerful capabilities for database programming and management.

In this article, We will learn about How to Show or List Databases by understanding the various methods with the help of examples and so on.