Creating Simple Reports

You can use temporary and defined variables in a SQL*Plus script. This allows you to create scripts that prompt the user for entry of variables that can then be used to generate reports. You ll find the SQL*Plus scripts referenced in this section in the Zip file you can download from this book s web site.

Tip	Bear in mind that SQL*Plus was not specifically designed as a reporting tool. If you have complex reporting requirements, you should use software like Oracle Reports.

Using Temporary Variables in a Script

The following script report1.sql uses a temporary variable named product_id_var in the WHERE clause of a SELECT statement:

 -- suppress display of the statements and verification messages SET ECHO OFF SET VERIFY OFF SELECT product_id, name, price FROM products WHERE product_id = &product_id_var;

The SET ECHO OFF command stops SQL*Plus from displaying the SQL statements and commands in the script. SET VERIFY OFF suppresses display of the verification messages. I put these two commands in to minimize the number of extra lines displayed by SQL*Plus when you run the script.

You can run report1.sql in SQL*Plus using the @ command. For example:

 SQL>  @ C:\SQL\report1.sql  Enter value for product_id_var:  2  PRODUCT_ID NAME PRICE ---------- ------------------------------ ----------  2 Chemistry 30

You can give this script to another user and they can run it without them having to know SQL.

Using Defined Variables in a Script

The following script (named report2.sql ) uses the ACCEPT command to define a variable named product_id_var :

 SET ECHO OFF SET VERIFY OFF ACCEPT product_id_var NUMBER FORMAT 99 PROMPT 'Enter product id: ' SELECT product_id, name, price FROM products WHERE product_id = &product_id_var; -- clean up UNDEFINE product_id_var

Notice that a user-friendly prompt is specified for the entry of product_id_var , and that product_id_var is removed at the end of the script ”this makes the script cleaner.

You can run the report2.sql script using SQL*Plus:

 SQL>  @ C:\SQL\report2.sql  Enter product id:  4  PRODUCT_ID NAME PRICE ---------- ------------------------------ ----------  4 Tank War 13.95

Passing a Value to a Variable in a Script

You can pass a value to a variable when you run your script. When you do this, you reference the variable in your script using a number. The following script report3.sql shows an example of this; notice the variable is identified using & 1 :

 SET ECHO OFF SET VERIFY OFF SELECT product_id, name, price FROM products WHERE product_id = &1;

When you run report3.sql , you supply the variable s value after the script name. The following example passes the value 3 to report3.sql :

 SQL>  @ C:\SQL\report3.sql 3  PRODUCT_ID NAME PRICE ---------- ------------------------------ ----------  3 Supernova 25.99

You can add any number of parameters, with each value specified on the command line corresponding to the matching number in the file. The first parameter corresponds to & 1 , the second to & 2 , and so on. The following script report4.sql shows an example of this:

 SET ECHO OFF SET VERIFY OFF SELECT product_id, product_type_id, name, price FROM products WHERE product_type_id = &1 AND price > &2;

The following example run of report4.sql shows the addition of two values for & 1 and & 2 , which are set to 1 and 9.99, respectively:

 SQL>  @ C:\SQL\report4.sql 1 9.99  PRODUCT_ID PRODUCT_TYPE_ID NAME PRICE ---------- --------------- ------------------------------ ----------  1 1 Modern Science 19.95  2 1 Chemistry 30

Because & 1 is set to 1, the product_type_id column in the WHERE clause is set to 1. Also, & 2 is set to 9.99, so the price column in the WHERE clause is set to 9.99. Therefore, rows with a product_type_id of 1 and a price greater than 9.99 are displayed.

Adding a Header and Footer

You add a header and footer to your report using the TTITLE and BTITLE commands. The following script report5.sql shows this:

 TTITLE 'Product Report' BTITLE 'Thanks for running the report' SET ECHO OFF SET VERIFY OFF SET PAGESIZE 30 SET LINESIZE 70 CLEAR COLUMNS COLUMN product_id HEADING ID FORMAT 99 COLUMN name HEADING 'Product Name' FORMAT A20 WORD_WRAPPED COLUMN description HEADING Description FORMAT A30 WORD_WRAPPED COLUMN price HEADING Price FORMAT .99 SELECT product_id, name, description, price FROM products; CLEAR COLUMNS

The following example shows a run of report5.sql :

 SQL>  @ C:\SQL\report5.sql  Fri May 16 page 1  Product Report  ID Product Name Description Price --- -------------------- ------------------------------ -------  1 Modern Science A description of modern .95  science  2 Chemistry Introduction to Chemistry .00  3 Supernova A star explodes .99  4 Tank War Action movie about a future .95  war  5 Z Files Series on mysterious .99  activities  6 2412: The Return Aliens return .95  7 Space Force 9 Adventures of heroes .49  8 From Another Planet Alien from another planet .99  lands on Earth  9 Classical Music The best classical music .99  10 Pop 3 The best popular music .99  11 Creative Yell Debut album .99  12 My Front Line Their greatest hits .49  Thanks for running the report

Computing Subtotals

You can add a subtotal for a column using a combination of the BREAK ON and COMPUTE commands. BREAK ON causes SQL*Plus to break up output based on a change in a column value, and COMPUTE causes SQL*Plus to compute a value for a column.

The following script report6.sql shows how to compute a subtotal for products of the same type:

 BREAK ON product_type_id COMPUTE SUM OF price ON product_type_id SET ECHO OFF SET VERIFY OFF SET PAGESIZE 50 SET LINESIZE 70 CLEAR COLUMNS COLUMN price HEADING Price FORMAT 9.99 SELECT product_type_id, name, price FROM products ORDER BY product_type_id; CLEAR COLUMNS

The following example shows a run of report6.sql :

 SQL>  @ C:\SQL\report6.sql  PRODUCT_TYPE_ID NAME Price --------------- ------------------------------ --------  1 Modern Science .95  Chemistry .00 *************** -------- sum .95  2 Supernova .99  Tank War .95  Z Files .99  2412: The Return .95 *************** -------- sum 4.88  3 Space Force 9 .49  From Another Planet .99 *************** -------- sum .48  4 Classical Music .99  Pop 3 .99  Creative Yell .99 *************** -------- sum .97  My Front Line .49 *************** -------- sum .49

Notice that whenever a new value for product_type_id is encountered , SQL*Plus breaks up the output and computes a sum for the price columns for the rows with the same product_type_id . The product_type_id value is only shown once for rows with the same product_type_id . For example, Modern Science and Chemistry are both books and have a product_type_id of 1, and 1 is shown once for Modern Science. The sum of the prices for these two books is $49.95. The other sections of the report contain the sum of the prices for products with different product_type_id values.