After completing this lesson, you should be able to do the following: Produce queries that require a substitution variable Customize the iSQL*Plus environment Produce more readable output Create and execute script files | Producing Readable Output with iSQL*Plus Schedule: Timing Topic 35 minutes Lecture 35 minutes Practice 70 minutes Total Objectives After completing this lesson, you should be able to do the following: Produce queries that require a substitution variable Customize the iSQL*Plus environment Produce more readable output Create and execute script files Lesson Aim In this lesson, you will learn how to include iSQL*Plus commands to produce more readable SQL output. You can create a command file containing a WHERE clause to restrict the rows displayed. To change the condition each time the command file is run, you use substitution variables. Substitution variables can replace values in the WHERE clause, a text string, and even a column or a table name. Substitution Variables I want to query different values. . salary = ? department_id = ? . last_name = ? . User Substitution Variables The examples so far have been hard-coded. In a finished application, the user would trigger the | Producing Readable Output with iSQL*Plus Schedule: Timing Topic 35 minutes Lecture 35 minutes Practice 70 minutes Total Objectives After completing this lesson, you should be able to do the following: Produce queries that require a substitution variable Customize the iSQL*Plus environment Produce more readable output Create and execute script files Lesson Aim In this lesson, you will learn how to include iSQL*Plus commands to produce more readable SQL output. You can create a command file containing a WHERE clause to restrict the rows displayed. To change the condition each time the command file is run, you use substitution variables. Substitution variables can replace values in the WHERE clause, a text string, and even a column or a table name. Substitution Variables I want to query different values. . salary = ? department_id = ? . last_name = ? . User Substitution Variables The examples so far have been hard-coded. In a finished application, the user would trigger the report, and the report would run without further prompting. The range of data would be predetermined by the fixed WHERE clause in the iSQL*Plus script file. Using iSQL*Plus, you can create reports that prompt the user to supply their own values to restrict the range of data returned by using substitution variables. You can embed substitution variables in a command file or in a single SQL statement. A variable can be thought of as a container in which the values are temporarily stored. When the statement is run, the value is substituted. Instructor Note Demo: , Purpose: To illustrate returning all rows and using a case-insensitive query with substitution variables. With iSQL*Plus , there is a bug when using &substitution and wildcards (%) for character values. This bug has been reported. The will produce an error in iSQL*Plus, but the concept is important for students continuing classes using other products (such as Forms, Reports). You may
