Meta-Access and Repetitive Composition

Part 10 in a second series on the basics of the relational database and SQL

July/August 2017

This article is the 10th in a series that helps you build on the fundamentals you learned in the 12-part SQL 101 series in Oracle Magazine. The previous Beyond SQL 101 article, “The Cost of Data Retrieval,” introduced bitmap indexes. You discovered how to create them and when they might be an appropriate index choice. You also learned how bitmap indexes should not be used in a database with regular transaction activity but instead in a data warehouse with occasional bulk loads of data. You were introduced to Oracle Database’s Oracle Optimizer program, SQL statement processing operations, and parsing. You learned what a bind variable is and how it can help reduce parsing activity. Finally, you learned about statistics and the creation and display of execution plans.

In this article, you will

• Learn the difference between static and dynamic data dictionary views
• Discover the USER_, ALL_, DBA_, and V$ views
• Receive an introduction to Oracle SQL script writing
• See how substitution variables are implemented

To try out the examples in this series, you need access to an Oracle Database instance. If necessary, download and install an Oracle Database edition for your operating system. I recommend installing Oracle Database, Enterprise Edition 12c Release 2 (12.2.0.1.0). If you install the Oracle Database software, choose the installation option that enables you to create and configure a database. A new database, including sample user accounts and their associated schemas, will be created for you. (Note that SQL_201 is the user account to use for the examples in this series; it’s also the schema in which you’ll create database tables and other objects.) When the installation process prompts you to specify schema passwords, enter and confirm passwords for the SYS and SYSTEM users and make a note of them.

Finally—whether you installed the database software from scratch or have access to an existing Oracle Database instance—download, unzip, and execute the SQL script to create the tables for the SQL_201 schema that are used for this article’s examples. (View the script in a text editor for execution instructions.)

Recall from a previous Beyond SQL 101 article, “Defining, Constraining, and Manipulating Your Entities,” that the data dictionary is an internally owned and controlled set of tables and views that contains information about your database’s metadata. Oracle Database uses the data dictionary internally for many purposes, such as

• Storing statistics about a table (statistics that will be used by Oracle Optimizer)
• Checking whether a column is indexed
• Examining which constraints exist and whether they are enabled
• Determining the validity of table and column names during SQL statement parsing
• Retrieving the privileges of a particular user

The data dictionary includes both dynamic and static sets of tables and views. The dynamic views are used primarily to examine database performance. The static views store details about database objects such as tables, indexes, and views and are generally categorized into one of three prefixed categories: USER_, ALL_, and DBA_.

Whenever a new database object is created or altered, metadata about the object is recorded in a static data dictionary view. Static views can also be used by Oracle Database to retrieve information about indexed columns and referential integrity constraints. The USER_ views display information about objects belonging to the currently logged-in user. The ALL_ views return the same information as the USER_ views, but they also display information about any objects granted to the currently logged-in user by another user and display any public objects, such as public synonyms. (You will learn about synonyms and how to grant, receive, and revoke access privileges and roles in subsequent articles in this series.) The DBA_ views provide information about every object in the database. To query any of the DBA_ views, you need to have special database administrator privileges, such as the DBA role or the SELECT ANY DICTIONARY (as in data dictionary) privilege, granted to you. Consider the query in Listing 1.

Code Listing 1: Query the USER_TABLES static data dictionary view

SQL> set lines 10000
SQL> select table_name, num_rows, to_char(last_analyzed, 
'DD-MON-YYYY HH24:MI:SS') last_analyzed
  2    from <strong>user_tables</strong>
  3  order by table_name;

TABLE_NAME                       NUM_ROWS LAST_ANALYZED
—————————————————————————————— —————————— ————————————————————
ANNUAL_REVIEW                          15 05-JUL-2016 01:00:23
DEPARTMENT                              4 05-JUL-2016 01:00:24
EMPLOYEE                               16 04-JAN-2017 01:00:32
EMPLOYEE_CTAS                           2 11-SEP-2016 16:21:22
EMPLOYEE_EXTRA                          2 26-OCT-2016 01:00:32
EMPLOYEE_IDENTITY                      16 04-JAN-2017 01:00:32
6 rows selected.

Query the USER_TABLES static data dictionary view to display the names of the tables owned by you (or the currently logged-in user), the number of rows each table contains, and the last time statistics were gathered for each table. Run the DESCRIBE command on the USER_TABLES data dictionary view to display all the other columns that constitute the view, as the example in Listing 2 demonstrates. For a description of every USER_TABLES column and the type of data it contains, refer to the documentation.

Code Listing 2: A partial result from the DESCRIBE command executed against the USER_TABLES static data dictionary view

SQL> describe user_tables
 Name                                     Null?     Type
 ————————————————————————————————————————————————————————————————
 TABLE_NAME                               NOT NULL  VARCHAR2(128)
 .
 .
 .
 NUM_ROWS                                           NUMBER
 .
 .
 .
 LAST_ANALYZED                                      DATE
 .
 .
 .

The dynamic data dictionary views are typically used by a database administrator to monitor the database and its performance. These views have a V$ prefix (which is why they are commonly referred to as “V-dollar tables”) and are continuously updated by Oracle Database background processes rather than database users. Much as with the DBA_ views, you need to have special database administrator privileges, such as the DBA role or the SELECT ANY DICTIONARY privilege, granted to you to give you access to the dynamic data dictionary views. Alternatively, a database administrator can grant you privileges to access specific individual dynamic data dictionary views you’d like to query. To learn more about the data dictionary and dynamic performance views, refer to the documentation.

Although you know how to write and execute individual SQL statements, it is common for developers and DBAs to insert multiple SQL statements and SQL*Plus commands into a single script file for executing en masse. The script you used to create the tables for the SQL_201 schema for this article’s examples—SQL_201_objects_setup_script_Install_1.sql—contains data definition language (DDL) statements for creating tables and data manipulation language (DML) statements to insert the data, among other statements. Scripts are useful for tasks that require you to run a set of SQL statements, for example, for the following types of activities:

• Performing ad hoc tasks
• Performing environment setup activities
• Performing environment refreshes, such as readying a production environment once the script has already been tested and executed in development and testing environments

Consider the example in Listing 3. The test_this_script.sql script contains two SQL statements: one that creates a table called EMPLOYEE_SUBSET and one that selects data from the newly created table. You can create this example script in an editor of your choice and save it with the name test_this_script.sql. To avoid the error in Listing 4 when you run the script, either navigate to the directory in which you’ve saved the script before you log in to Oracle Database or fully qualify the directory location of your database script. It is good practice to fully qualify the directory location of your database script. Listing 5 shows the result of running the test_this_script.sql database script with SQL*Plus as the SQL execution environment. Note that the line that invokes and executes the script,

@test_this_script.sql

begins with an @ symbol. When followed by a filename, this symbol acts as a SQL*Plus command to execute, or run, the commands inside the file. Alternatively, you could use the keyword START, as in

START test_this_script.sql

Code Listing 3: The test_this_script.sql database script contents

create table employee_subset (employee_id, 
                              employee_name, 
                              create_date)
as
select employee_id, first_name||' '||last_name, sysdate
  from employee;
select employee_id, employee_name, create_date
  from employee_subset
order by employee_id;

Code Listing 4: Error received when the SQL environment cannot find your script

SQL> @test_this_script.sql
SP2-0310: unable to open file "test_this_script.sql"

Code Listing 5: The result of running the test_this_script.sql database script

SQL> @test_this_script.sql
Table created.

       EMPLOYEE_ID  EMPLOYEE_NAME                 CREATE_DATE
       ———————————  ————————————————————————————— ————————————————————
                 1  Don Rose                      11-MAY-2017 21:48:06
                21  Gerald Sowell                 11-MAY-2017 21:48:06
                28  Emily Eckhard                 11-MAY-2017 21:48:06
                37  Frances Newton                11-MAY-2017 21:48:06
              1234  Donald Newton                 11-MAY-2017 21:48:06
              6567  Roger Friedli                 11-MAY-2017 21:48:06
              6568  Betsy James                   11-MAY-2017 21:48:06
              6569  michael peterson              11-MAY-2017 21:48:06
              6570  mark leblanc                  11-MAY-2017 21:48:06
              6571  Thomas Jeffrey                11-MAY-2017 21:48:06
              6572  Theresa Wong                  11-MAY-2017 21:48:06
              6573  Lori Dovichi                  11-MAY-2017 21:48:06
              6574  Marcy Tamra                   11-MAY-2017 21:48:06
              6575  Sasha Meyer                   11-MAY-2017 21:48:06
              6576  Mary Streicher                11-MAY-2017 21:48:06
              7895  Matthew Michaels              11-MAY-2017 21:48:06
16 rows selected.

The next few lines in the output in Listing 5 illustrate that the first statement was executed; the table, EMPLOYEE_SUBSET, was successfully created; then the second statement was executed; and the result of the query against the EMPLOYEE_SUBSET data was returned.

You might have a requirement to execute a SQL statement over and over again, but one part of the statement—the input to the WHERE clause—must change for different executions. In such a case, that input is variable and is substituted by a different input value each time the statement is executed. You can rewrite this type of statement by using a substitution variable. A substitution variable consists of a variable name of your choosing, prefixed with an ampersand (&). The example in Listing 6 illustrates the use of a substitution variable, &v_salary, to input a salary value that is compared with salary values in the EMPLOYEE table. By using a substitution variable, you can reduce the amount of writing required for similar SQL statements that are run often.

Code Listing 6: A substitution variable for salary values

SQL> select first_name||' '||last_name employee_name
  2    from employee
  3   where salary < &v_salary
  4  order by employee;
<strong>Enter value for v_salary: 100000</strong>
<strong>old   3:  where salary < &v_salary</strong>
<strong>new   3:  where salary < 100000</strong>

EMPLOYEE
—————————————————————————————————————————————————————
Betsy James
Don Rose
Donald Newton
Frances Newton
Matthew Michaels
Roger Friedli
Sasha Meyer
Theresa Wong
mark leblanc
michael peterson

10 rows selected.

The query in Listing 7 demonstrates how to re-execute the Listing 6 statement that is now in the SQL*Plus buffer. When you use a forward-slash character (/), the SQL statement is reissued to the SQL execution environment and you are prompted for a value for the substitution variable, without having to copy and paste the original SQL statement. Additionally, Listing 8 shows how the use of substitution variables is not limited to the WHERE clause of a statement.

Code Listing 7: Re-executing the statement with the forward-slash character (/)

SQL> /
Enter value for v_salary: 90000
old   3:  where salary < &v_salary
new   3:  where salary < 90000

EMPLOYEE
——————————————————————————————————————————————————————
Betsy James
Donald Newton
Frances Newton
Matthew Michaels
Roger Friedli
Sasha Meyer
Theresa Wong
mark leblanc
8 rows selected.

Code Listing 8: Substitution variables, not just for WHERE clauses

SQL> select &v_col1, &v_col2, &v_col3
  2    from employee;
Enter value for v_col1: first_name
Enter value for v_col2: last_name
Enter value for v_col3: hire_date
old   1: select &v_col1, &v_col2, &v_col3
new   1: select first_name, last_name, hire_date

FIRST_NAME                LAST_NAME                HIRE_DATE
———————————————           ————————————————         —————————
Marcy                     Tamra                    04-MAY-16
Sasha                     Meyer                    04-MAY-16
Gerald                    Sowell                   29-OCT-16
Emily                     Eckhardt                 07-JUL-04
Frances                   Newton                   14-SEP-05
Donald                    Newton                   24-SEP-06
Matthew                   Michaels                 16-MAY-07
Roger                     Friedli                  16-MAY-07
Betsy                     James                    16-MAY-07
Michael                   peterson                 03-NOV-08
Mark                      leblanc                  06-MAR-09
Thomas                    Jeffrey                  27-FEB-10
Theresa                   Wong                     27-FEB-10
Lori                      Dovichi                  07-JUL-11
Mary                      Streicher                16-MAY-16
Don                       Rose                     26-OCT-16

16 rows selected.

SQL> select first_name||' '||last_name, salary, hire_date
  2    from employee
  3  order by &v_order;
Enter value for v_order: salary desc
old   3: order by &v_order
new   3: order by salary desc

FIRST_NAME||''||LAST_NAME                     SALARY HIRE_DATE
—————————————————————————————————————————— ————————— —————————
Marcy Tamra                                          04-MAY-16
Lori Dovichi                                         07-JUL-11
Thomas Jeffrey                                300000 27-FEB-10
Mary Streicher                                200000 16-MAY-16
Emily Eckhardt                                110000 07-JUL-04
Gerald Sowell                                 100000 29-OCT-16
Don Rose                                       95000 26-OCT-16
michael peterson                               90000 03-NOV-08
Sasha Meyer                                    85000 04-MAY-16
Frances Newton                                 82500 14-SEP-05
Donald Newton                                  80000 24-SEP-06
Matthew Michaels                               70000 16-MAY-07
Theresa Wong                                   70000 27-FEB-10
mark leblanc                                   65000 06-MAR-09
Roger Friedli                                  60000 16-MAY-07
Betsy James                                    60000 16-MAY-07

16 rows selected.

Be aware that the SQL*Plus environment almost always reads the ampersand as a substitution variable. The queries in Listing 9 show the kind of error you might run into when you include a literal ampersand value or both a literal ampersand value and a substitution variable in your script, followed by a couple of workarounds.

Code Listing 9: When you want a literal text ampersand returned in your result

SQL> select 'This & That'
  2    from dual;
Enter value for that: 
old   1: select 'This & That'
new   1: select 'This '

'THIS
—————
This

<strong>--Create a database script called handle_the_ampersand.sql set define off</strong>
select 'This & That'
  from dual;
<strong>--Execute the script</strong>
SQL> @handle_the_ampersand.sql

'THIS&THAT'
———————————
This & That
<strong>--Mix substitution variables with literal text ampersands</strong>
<strong>--Create a database script called variables_and_ampersands.sql set define on</strong>
select 'This '||'&'||' That'
  from &v_table;
<strong>--Execute the script</strong>
SQL> @variables_and_ampersands.sql
Enter value for v_table: dual
old   2:   from &v_table
new   2:   from dual

'THIS'||'&'
———————————
This & That

The first line of the database script handle_the_ampersand.sql is

set define off

This is a SQL*Plus command for disabling the prompting and replacement of substitution variables within the SQL*Plus execution environment. When this command is issued, ampersands are treated as literal values. However, there might be instances when you want to use both substitution variables and literal ampersand values within your statements or scripts. In such cases, you might not want to disable substitution variables. The database script variables_and_ampersands.sql sets the SQL*Plus environment back to accepting and replacing substitution variables and uses string concatenation to ensure that any literal ampersand is correctly read as a literal string value. Note that if you run the SET DEFINE OFF SQL*Plus command, any attempt to access your substitution variable as originally written in your script will result in an error message similar to the following:

SQL> @variables_and_ampersands.sql
SP2-0552: Bind variable "V_TABLE" not declared.

One of the ways to handle this error is to ensure that you run the SET DEFINE ON SQL*Plus command—the first line of the variables_and_ampersands.sql script.

Occasionally you might want to use a named substitution variable more than once in your database script. If your input to the variable is the same for the duration of the script run, you can avoid being prompted for a value each time the variable is encountered by employing double ampersand (&&) notation. Listing 10 shows a script with a substitution variable used in multiple statements where the input is potentially the same for each variable invocation. Listing 11 demonstrates a similar script that uses double ampersand notation with the substitution variable.

Code Listing 10: Substitution variables requiring value input each time they are invoked

<strong>--Create a database script called resubstitute.sql</strong>
select table_name, num_rows, to_char(last_analyzed, 
'DD-MON-YYYY HH24:MI:SS') last_analyzed
  from user_tables
 where table_name = <strong>'&v_table';</strong>
select table_name, index_name, column_name, column_position
  from user_ind_columns
 where table_name = <strong>'&v_table';</strong>
<strong>--Execute the script</strong>
SQL> @resubstitute.sql
Enter value for v_table: EMPLOYEE
old   3:  where table_name = '&v_table'
new   3:  where table_name = 'EMPLOYEE'

TABLE_NAME                               NUM_ROWS LAST_ANALYZED
—————————————————————————————————————— —————————— ————————————————————
EMPLOYEE                                       16 13-MAY-2017 14:46:32

Enter value for v_table: EMPLOYEE
old   3:  where table_name = '&v_table'
new   3:  where table_name = 'EMPLOYEE'

TABLE_NAME INDEX_NAME                  COLUMN_NAME              COLUMN_POSITION
—————————— ——————————————————————————— ———————————————————————— ———————————————
EMPLOYEE   EMP_HIRE_DATE_I             HIRE_DATE                              1
EMPLOYEE   DEP_WAGE_INCREASE_I         DEPARTMENT_ID                          1
EMPLOYEE   DEP_WAGE_INCREASE_I         WAGE_INCREASE_WORTHINESS               2
EMPLOYEE   EMPLOYEE_PK                 EMPLOYEE_ID                            1
EMPLOYEE   EMP_DEPT_FK                 DEPARTMENT_ID                          1
EMPLOYEE   EMPLOYEE_WAGE_INC_WORTH_BMI WAGE_INCREASE_WORTHINESS               1

6 rows selected.

Code Listing 11: Using && to avoid repeated prompted user input for substitution variables

<strong>--Create a database script called substitute_and_reuse.sql</strong>
select table_name, num_rows, to_char(last_analyzed, 
'DD-MON-YYYY HH24:MI:SS') last_analyzed
  from user_tables
 where table_name = <strong>'&&v_table';</strong>
select table_name, index_name, column_name, column_position
  from user_ind_columns
 where table_name = <strong>'&&v_table';</strong>
<strong>--Execute the script</strong>
SQL> @substitute_and_reuse.sql
Enter value for v_table: EMPLOYEE
old   3:  where table_name = '&&v_table'
new   3:  where table_name = 'EMPLOYEE'

TABLE_NAME                                  NUM_ROWS LAST_ANALYZED
————————————————————————————————————————— —————————— ————————————————————
EMPLOYEE                                          16 13-MAY-2017 14:46:32
<strong>old   3:  where table_name = '&&v_table'</strong>
<strong>new   3:  where table_name = 'EMPLOYEE'</strong>

TABLE_NAME INDEX_NAME                  COLUMN_NAME              COLUMN_POSITION
—————————— ——————————————————————————— ———————————————————————— ———————————————
EMPLOYEE   EMP_HIRE_DATE_I             HIRE_DATE                              1
EMPLOYEE   DEP_WAGE_INCREASE_I         DEPARTMENT_ID                          1
EMPLOYEE   DEP_WAGE_INCREASE_I         WAGE_INCREASE_WORTHINESS               2
EMPLOYEE   EMPLOYEE_PK                 EMPLOYEE_ID                            1
EMPLOYEE   EMP_DEPT_FK                 DEPARTMENT_ID                          1
EMPLOYEE   EMPLOYEE_WAGE_INC_WORTH_BMI WAGE_INCREASE_WORTHINESS               1

6 rows selected.

If you want to reset the value of your substitution variable when using double ampersand notation, delete the current value with the UNDEFINE SQL*Plus command. For example, to delete the current value of the v_table substitution variable in SQL*Plus, use the command

undefine v_table

This article introduced you to the data dictionary, from the USER_, ALL_, and DBA_ static data dictionary views to the dynamic performance views (the V$ tables.) You saw database scripts combine multiple statements in a sequence into one file. You learned what a substitution variable is and how it can help in reducing SQL statement writing. Last, you learned how substitution variables can be reused and reset.

In the next article in this series, you’ll learn more about SQL statement scripting and SQL*Plus commands and get an introduction to dynamic SQL.

Photography by Ricardo Gomez, Unsplash