Another way to make an iSQL*Plus report more flexible is by using substitution variables. A substitution variable is a string preceded by either an ampersand (&) or a double ampersand (&&) in an iSQL*Plus script that will prompt the user for its value when the script is run.
Substitution Variable A string literal with no embedded spaces, preceded by & or &&, that will prompt the user for a value when an iSQL*Plus script containing one of these variables is executed. A substitution variable preceded by & will not prompt the user for a value if the same substitution variable, preceded by &&, exists earlier in the script.
A substitution variable preceded by a single ampersand will prompt for a value every time it is encountered in a script. A substitution variable preceded by a double ampersand will prompt for a value once and will save that value. Once saved, if the same substitution variable preceded by a single ampersand is encountered, it will use the value saved when the substitution variable with the double ampersand was encountered.
Janice is reviewing the script she has been working on all day and realizes that sooner or later, the boss will want to run that script for any list of departments, not just departments 30 and 60. She realizes that substitution variables would be useful in this situation, and she changes her script as follows to allow iSQL*Plus to prompt for the department numbers before the query runs:
The only change is the replacement of the specific department numbers in the original script with the substitution variable DeptList. When Janice clicks the Execute button in iSQL*Plus, she is prompted for the value of DeptList.
The script runs as before, except this time a different group of departments is returned from the query.
Notice that iSQL*Plus, by default, will show the substitutions that occurred before presenting the results. This can be turned off with the SET VERIFY OFF command.
As you may have noticed, Janice is somewhat of a perfectionist, and she thinks that the report would look even better if the report header contained the list of departments in the report. This gives Janice a good opportunity to use the double ampersand in her substitution variable, so that she will not need to enter the department list twice when she runs the script. Her revised script now looks like this:
She changed the TTITLE command to include the substitution variable &&DeptList. When this script is run, the prompt for DeptList occurs only once.
However, the substitution is performed twice. The first substitution variable &&DeptList has a double ampersand, and therefore its value is retained when &DeptList is encountered later in the script.
Oracle DBA Related Interview Questions
|Oracle 10g Interview Questions||Oracle 9i Interview Questions|
|Oracle 8i Interview Questions||Oracle 11g Interview Questions|
|Oracle apps Interview Questions||Oracle Apps ERP Interview Questions|
|Oracle 7.3 Interview Questions||Oracle ADF Interview Questions|
|Oracle Application Framework Interview Questions||Oracle Apps Functional Interview Questions|
|Oracle Apps DBA Interview Questions||Oracle Workflow Interview Questions|
Oracle Dba Tutorial
Relational Database Concepts
Sql*plus And Isql*plus Basics
Oracle Database Functions
Restricting, Sorting, And Grouping Data
Using Multiple Tables
Advanced Sql Queries
Installing Oracle And Creating A Database
Creating And Maintaining Database Objects
Users And Security
Making Things Run Fast (enough)
Saving Your Stuff (backups)
All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.