Subscribe

Share

Database, SQL, and PL/SQL

Setting Parameters for Dynamic Productivity

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

By Melanie Caffrey

September/October 2017

This article is the 11th 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, “Meta-Access and Repetitive Composition,” 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 reduce SQL statement writing. Last, we walked through examples of how substitution variables can be reused and reset.

In this article, you will

  • Learn how to run a script with a parameter in SQL*Plus
  • Discover how to change the settings of the SQL*Plus environment within a script
  • See how to generate HTML reports in SQL*Plus
  • Get an introduction to dynamic SQL

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 schemas used for this article’s examples. (View the script in a text editor for execution instructions.)

Parameters of Change

Recall from “Meta-Access and Repetitive Composition” that you can use substitution variables to provide different input values to your scripts each time they are executed. The example in Listing 1 shows a SQL script that uses a substitution variable. The example in Listing 2 changes the SQL script in Listing 1 to use a parameter (also referred to as an argument) instead of a substitution variable. The only difference between these two examples is that the substitution variable is named &v_table and the parameter is numbered &1. Both prompt users for input when they encounter the ampersand character (&).

Code Listing 1: Use a query with a substitution variable

SQL> set lines 10000
SQL> select table_name, num_rows, 
to_char(last_analyzed, 'DD-MON-YYYY HH24:MI:SS') 
last_analyzed
  2    from user_tables
  3   where table_name = '&v_table';
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

Code Listing 2: Change the query to use a parameter instead

SQL> select table_name, num_rows, 
to_char(last_analyzed, 'DD-MON-YYYY HH24:MI:SS') 
last_analyzed
  2    from user_tables
  3   where table_name = '&1';
Enter value for 1: EMPLOYEE
old   3:  where table_name = '&1'
new   3:  where table_name = 'EMPLOYEE'

TABLE_NAME              NUM_ROWS LAST_ANALYZED
————————————————————— —————————— ————————————————————
EMPLOYEE                      16 13-MAY-2017 14:46:32
Now compare the result in Listing 2 with that in Listing 3, which places the SQL statement from Listing 2 into a script file—parameterize_your_script.sql—and calls the file from a SQL*Plus command prompt. Note that the substitution in Listing 3 is done without prompting and that the substitution is done positionally. Positional replacement means that, in order from left to right, any parameter values passed to the parameterize_your_script.sql file at the SQL*Plus command prompt are passed as substitution values to any numbered parameters. In Listing 3, the parameterize_your_script.sql file has just one numbered parameter, &1. Listing 4 expands the parameterize_your_script.sql script created in Listing 3 to further demonstrate positional notation in parameter value replacement.

Code Listing 3: Place the parameterized statement in a script and execute it from the SQL*Plus command prompt

--Create a database script called 
--parameterize_your_script.sql
select table_name, num_rows, to_char(last_analyzed, 
'DD-MON-YYYY HH24:MI:SS') last_analyzed
  from user_tables
 where table_name = '&1';

--Execute the script with a parameter
SQL> @parameterize_your_script.sql 'EMPLOYEE'
old   3:  where table_name = '&1'
new   3:  where table_name = 'EMPLOYEE'

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

The expanded parameterize_your_script.sql file demonstrates how the first command-line parameter value, 'EMPLOYEE', is passed to the &1 parameter, and the second parameter value, 10, is passed to the &2 parameter. Note that this kind of notation works only if your parameterized substitution variable is a number from 1 to 9. Additionally, the script file in Listing 4 shows how, just as with the named substitution variables, you need to remember to place single quotes around any parameters to which you expect to pass string values. The example in Listing 4 also demonstrates how the double-line substitution variable replacement display, shown in Listings 1 through 3, can be suppressed with the

set verify off 

SQL*Plus command.

Code Listing 4: Add a second parameter and turn off substitution variable replacement display

--Alter parameterize_your_script.sql to include a 
--second parameter and turn off substitution variable 
--replacement display
set verify off

select table_name, num_rows, to_char(last_analyzed, 
'DD-MON-YYYY HH24:MI:SS') last_analyzed
  from user_tables
 where table_name like '&1'||'%'
   and num_rows > &2;

SQL> @parameterize_your_script.sql 'EMPLOYEE' 10

TABLE_NAME           NUM_ROWS LAST_ANALYZED
—————————————————— —————————— ——————————————————————
EMPLOYEE                   16 13-MAY-2017 14:46:32
EMPLOYEE_IDENTITY          16 04-JAN-2017 01:00:32
EMPLOYEE_SUBSET            16 11-MAY-2017 21:48:06

Customizing Your Return on Input

Even though you know how to write and execute individual and multiple SQL statements within script files, developers and DBAs incorporate SQL*Plus commands into their scripts to change the settings of their SQL*Plus environment and, therefore, customize how their scripts execute and return results. The script you used to create the tables for the SQL_201 schemas used for this article’s examples incorporates a couple of SQL*Plus commands, including the SPOOL command. The example in Listing 5 demonstrates the utility of the SPOOL command.

Code Listing 5: Spool the results of your script file to a results file

--Incorporate the SQL*Plus SPOOL command into 
--your parameterize_your_script.sql file
spool user_table_last_analyzed.lst
set verify off

select table_name, num_rows, to_char(last_analyzed, 
'DD-MON-YYYY HH24:MI:SS') last_analyzed
  from user_tables
 where table_name like '&1'||'%'
   and num_rows > &2;

spool off

--Run the script at the SQL*Plus command prompt
SQL> @parameterize_your_script.sql 'EMPLOYEE' 10

TABLE_NAME         NUM_ROWS LAST_ANALYZED
———————————————— —————————— ——————————————————————
EMPLOYEE                 16 13-MAY-2017 14:46:32
EMPLOYEE_IDENTITY        16 04-JAN-2017 01:00:32
EMPLOYEE_SUBSET          16 11-MAY-2017 21:48:06

--View the contents of your spooled output file
TABLE_NAME         NUM_ROWS LAST_ANALYZED
———————————————— —————————— ——————————————————————
EMPLOYEE                 16 13-MAY-2017 14:46:32
EMPLOYEE_IDENTITY        16 04-JAN-2017 01:00:32
EMPLOYEE_SUBSET          16 11-MAY-2017 21:48:06

The SQL*Plus SPOOL command, used together with a filename of your choosing, spools the results of any SQL*Plus or SQL commands issued subsequently to your chosen file. You issue the SPOOL OFF command to stop spooling and save and close your results file. To append spooled results to a file you’ve already created, you can add the APPEND syntax option, such as

spool user_table_last_analyzed.lst append

If you do not add the APPEND keyword to your SPOOL command, any existing file with the same name as that used in your SPOOL command will be overwritten with each execution of your script file.

Listing 6 includes several additional SQL*Plus commands in the parameterize_your_script.sql script file. The LINESIZE (or LINES) command limits the number of characters displayed on one line before the output begins a new line. PAGESIZE (or PAGES) defaults to a value whereby it repeats the display of column headings every 14 lines. Setting the PAGESIZE value to a large number repeats the display of column headings fewer times, and setting this value to 0 suppresses the display of column headings altogether. TERMOUT (or TERM) controls the output display generated by the script commands.

Code Listing 6: Add more SQL*Plus commands to your database script file

--Incorporate additional SQL*Plus commands 
--into your parameterize_your_script.sql file
spool user_table_last_analyzed.lst
set verify off
set linesize 60
set pagesize 100
set termout on
set feedback on

select table_name, num_rows, to_char(last_analyzed, 
'DD-MON-YYYY HH24:MI:SS') last_analyzed
  from user_tables
 where table_name like '&1'||'%'
   and num_rows > &2;

spool off

--Run the script at the SQL*Plus command prompt
SQL> @parameterize_your_script.sql 'EMPLOYEE' 10

TABLE_NAME       NUM_ROWS  LAST_ANALYZED
——————————————  —————————  ————————————————————
EMPLOYEE_SUBSET        16  11-MAY-2017 21:48:06
EMPLOYEE_IDENTITY      16  04-JAN-2017 01:00:32
EMPLOYEE               16  13-MAY-2017 14:46:32

3 rows selected.

--View the contents of your spooled output file
TABLE_NAME          NUM_ROWS     LAST_ANALYZED
———————————————   ——————————     ————————————————————
EMPLOYEE_SUBSET           16     11-MAY-2017 21:48:06
EMPLOYEE_IDENTITY         16     04-JAN-2017 01:00:32
EMPLOYEE                  16     13-MAY-2017 14:46:32

3 rows selected.
The OFF setting suppresses the output to the screen if the commands are executed from a script file. Finally, FEEDBACK (or FEED) returns a count of the records returned by a query or statement. Setting this value to 0 or OFF suppresses the feedback. Additionally, if you are using Oracle Database 12c Release 2 (12.2.0.1.0), you can use the ONLY option to display feedback but not results. This might come in handy if you are performing any sort of troubleshooting on the performance of your SQL statement and its execution plan.

Although the default spooled output is formatted as fixed-character-length plain-text reports, you can also generate your output as HTML by inserting the following SQL* Plus command into your database script files: SET MARKUP HTML ON.

Listing 7 adds this SQL*Plus command to the parameterize_your_script.sql file and displays the resultant HTML file markup and an approximation of the file’s appearance in a web browser. (Remember to change your output file extension to one that a web browser can read.) Additionally, the HTML output will look better when SQL*Plus FEEDBACK is set to OFF. To go back to spooling plain-text output, run the SET MARKUP HTML OFF command.

Code Listing 7: Change your output file from a text file to an HTML file

--Incorporate the SQL*Plus command into  
--your parameterize_your_script.sql file  
--to output an HTML file
spool user_table_last_analyzed.html
set markup html on spool on
set verify off
set linesize 60
set pagesize 100
set feedback off
set termout on

select table_name, num_rows, to_char(last_analyzed, 
'DD-MON-YYYY HH24:MI:SS') last_analyzed
  from user_tables
 where table_name like '&1'||'%'
   and num_rows > &2;

spool off

--Run the script at the SQL*Plus command prompt
SQL> @parameterize_your_script.sql 'EMPLOYEE' 10
<p>
<table border='1' width='90%' align='center' 
summary='Script output'>
<tr>
<th scope="col">
TABLE_NAME
</th>
<th scope="col">
NUM_ROWS
</th>
<th scope="col">
LAST_ANALYZED
</th>
</tr>
<tr>
<td>
EMPLOYEE_SUBSET
</td>
<td align="right">
        16
</td>
<td>
11-MAY-2017 21:48:06
</td>
</tr>
<tr>
<td>
EMPLOYEE_IDENTITY
</td>
<td align="right">
        16
</td>
<td>
04-JAN-2017 01:00:32
</td>
</tr>
<tr>
<td>
EMPLOYEE
</td>
<td align="right">
        16
</td>
<td>
13-MAY-2017 14:46:32
</td>
</tr>
</table>
<br>

SQL>

--View the contents of your spooled HTML output file
TABLE_NAME NUM_ROWS LAST_ANALYZED
EMPLOYEE_SUBSET 16 11-MAY-2017 21:48:06
EMPLOYEE_IDENTITY 16 04-JAN-2017 01:00:32
EMPLOYEE 16 13-MAY-2017 14:46:32
 

Building on the Move

In addition to creating and running static SQL statements, you can create SQL at runtime by generating dynamic SQL. Dynamic SQL generates additional SQL, which is useful in situations such as table and other database object maintenance and reporting, where generating multiple statements on the fly is less time-consuming and potentially less error-prone than writing each individual SQL statement by hand. Listing 8 demonstrates a dynamic SQL statement that generates individual SQL statements for adding audit columns to every table in the USER_TABLES data dictionary view. The text literal

 'alter table' 

is concatenated with each table name retrieved from the USER_TABLES data dictionary view. The result of this concatenation is then further concatenated with the text literal

'add (created_on date, created_by number, 
updated_on date, updated_by number); ' 

Code Listing 8: Use dynamic SQL to create ALTER TABLE statements

SQL> set lines 60
SQL> select 'alter table '||table_name||' add (created_on 
date, created_by number, updated_on date, updated_by number); '
  2    from user_tables;

'ALTERTABLE'||TABLE_NAME||'ADD(CREATED_ONDATE,…);'
———————————————————————————————————————————————————————————————
alter table EMPLOYEE add (created_on date, created_by number, 
updated_on date, updated_by number);
alter table DEPARTMENT add (created_on date, created_by number, 
updated_on date, updated_by number);
alter table EMPLOYEE_IDENTITY add (created_on date, created_by 
number, updated_on date, updated_by number);
alter table EMPLOYEE_CTAS add (created_on date, created_by 
number, updated_on date, updated_by number);
alter table EMPLOYEE_EXTRA add (created_on date, created_by 
number, updated_on date, updated_by number);
alter table ANNUAL_REVIEW add (created_on date, created_by 
number, updated_on date, updated_by number);
alter table EMPLOYEE_SUBSET add (created_on date, created_by 
number, updated_on date, updated_by number);

7 rows selected.

The database script in Listing 9 includes this dynamic SQL query and spools the SQL output to a SQL script—create_audit_columns.sql. Note that instead of spooling to a file with an .lst, .txt, or .html file extension, you spool to a file with a .sql extension to create the dynamically generated SQL command script. Listing 10 demonstrates how you can run the create_audit_columns.sql script to execute the SQL statements.

Code Listing 9: Use a database script that creates ALTER TABLE statements with dynamic SQL

--Place the dynamic SQL statement in a database script file
spool create_audit_columns.sql
set linesize 60
set pagesize 100
set feedback off
set termout on

select 'alter table '||table_name||' add (created_on date, 
created_by number, updated_on date, updated_by number); '
 from user_tables;

spool off

--Run the script at the SQL*Plus command prompt
SQL> @dynamic_audit_column_create.sql

'ALTERTABLE'||TABLE_NAME||'ADD(CREATED_ONDATE,…);'
————————————————————————————————————————————————————————————
alter table EMPLOYEE add (created_on date, created_by number, 
updated_on date, updated_by number);
alter table DEPARTMENT add (created_on date, created_by number, 
updated_on date, updated_by number);
alter table EMPLOYEE_IDENTITY add (created_on date, created_by 
number, updated_on date, updated_by number);
alter table EMPLOYEE_CTAS add (created_on date, created_by 
number, updated_on date, updated_by number);
alter table EMPLOYEE_EXTRA add (created_on date, created_by 
number, updated_on date, updated_by number);
alter table ANNUAL_REVIEW add (created_on date, created_by 
number, updated_on date, updated_by number);
alter table EMPLOYEE_SUBSET add (created_on date, created_by 
number, updated_on date, updated_by number);

--View the contents of your spooled output file
'ALTERTABLE'||TABLE_NAME||'ADD(CREATED_ONDATE,…);'                      
——————————————————————————————————————————————————————————————
alter table EMPLOYEE add (created_on date, created_by number, 
updated_on date, updated_by number);                     
alter table DEPARTMENT add (created_on date, created_by number, 
updated_on date, updated_by number);                   
alter table EMPLOYEE_IDENTITY add (created_on date, created_by 
number, updated_on date, updated_by number);            
alter table EMPLOYEE_CTAS add (created_on date, created_by 
number, updated_on date, updated_by number);                
alter table EMPLOYEE_EXTRA add (created_on date, created_by 
number, updated_on date, updated_by number);               
alter table ANNUAL_REVIEW add (created_on date, created_by 
number, updated_on date, updated_by number);                
alter table EMPLOYEE_SUBSET add (created_on date, created_by 
number, updated_on date, updated_by number);

Code Listing 10: Use the output file from a spooled dynamic SQL statement execution

--Remove the header from the script, 
--create_audit_columns.sql, and run the script 
SQL> @create_audit_columns.sql
SQL> desc employee
 Name                            Null? Type
 ———————————————————————————— ———————— ——————————————
 EMPLOYEE_ID                  NOT NULL NUMBER
 FIRST_NAME                            VARCHAR2(30)
 LAST_NAME                             VARCHAR2(30)
 HIRE_DATE                             DATE
 SALARY                                NUMBER(9,2)
 MANAGER                               NUMBER
 DEPARTMENT_ID                         NUMBER
 WAGE_INCREASE_WORTHINESS              VARCHAR2(40)
 EMP_FULL_NAME                         VARCHAR2(70)
 CREATED_ON                            DATE
 CREATED_BY                            NUMBER
 UPDATED_ON                            DATE
 UPDATED_BY                            NUMBER

Finishing the Hat

Once you have a dynamically generated SQL statement file such as create_audit_columns.sql, it is a good idea to save it and add comment lines to the file similar to those illustrated in the example in Listing 11. The double-hyphen lines at the top of the create_audit_columns.sql file denote single-line comments. Alternatively, you can use the REMARK (or REM) command (as demonstrated in the script you used to create the tables for the SQL_201 schemas used for this article’s examples) or the /* … */ single or multiline comment delimiters. Similar comment lines should also be added to your dynamic SQL scripts for reusability and documentation purposes.

Code Listing 11: Add descriptive comment lines to the new SQL statement file

-- Script Name:   create_audit_columns.sql
-- Created Date: <Insert creation date here>
-- Created By:   <Insert author name here>
-- Script Use:   Used to create audit columns for all of the 
tables owned by the script author.

alter table EMPLOYEE add (created_on date, created_by number, 
updated_on date, updated_by number);
alter table DEPARTMENT add (created_on date, created_by number, 
updated_on date, updated_by number);
alter table EMPLOYEE_IDENTITY add (created_on date, created_by 
number, updated_on date, updated_by number);
alter table EMPLOYEE_CTAS add (created_on date, created_by 
number, updated_on date, updated_by number);
alter table EMPLOYEE_EXTRA add (created_on date, created_by 
number, updated_on date, updated_by number);
alter table ANNUAL_REVIEW add (created_on date, created_by 
number, updated_on date, updated_by number);
alter table EMPLOYEE_SUBSET add (created_on date, created_by 
number, updated_on date, updated_by number);

Conclusion

This article taught you how to pass parameters to database script files. You learned how to save the results from a database script execution to an .lst or .txt file with the SQL*Plus SPOOL command. Additionally, you saw how to save database script execution results to an .HTML file by using the SQL*Plus SET MARKUP HTML command functionality. You learned how to control aspects of the SQL*Plus execution environment with various common SET commands. Last, you explored how to generate SQL statements at runtime with dynamic SQL and how to document such scripts with comments.

In the next article in this series, you’ll learn how to create and alter users, privileges, roles, and synonyms.

Next Steps

READ SQL 101, Parts 1–12.

LEARN more about relational database design and concepts.

DOWNLOAD the sample script for this article.

READ more Beyond SQL 101.