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
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.
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:32Now 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.
READ more Beyond SQL 101.
DISCLAIMER: We've captured these popular historical magazine articles for your reference. Links etc contained within these article possibly won't work. These articles are provided as-is with no guarantee that the information within them is the best advice for current versions of the Oracle Database.