Dec 22nd, 2015 at 02:41 PM

Substitution Variables

Entering a value at script execution time rather than giving the value at the script itself. To do this you have to declare a variable at the script is called substitution variable (&substitution_var). Example: See the following table and script. Suppose you don't want to select any column name at the script as follows: Table Name: student

ID	Name	Email	Phone
1	Jahid	jahid@yahoo.com	90983043
2	Razu	rizu@yahoo.com	90983033

SQL>select id, name,&unknown_col from student where id=&unknown_id
SQL> save my_script

Here &unknown_col and &unknown_id are substitution variables Now run the command file "my_script" and respond as shown below to the prompts for values:

SQL>  @my_script
Enter value for unknown_col: phone
old   1: SELECT   &unknown_col,
new 1: SELECT   phone,
Enter value for unknown_id: 2
old   1: WHERE   &unknown_id,
new 1: WHERE   2,

If you add a command as shown below before your query.

SQL> SET VERIFY OFF
SQL>select id, name,&unknown_col from student where id=&unknown_id
SQL> save my_script

Than command file respond to the prompts for values as shown below:

SQL>  @my_script
Enter value for unknown_col: phone
Enter value for unknown_id: 2

SQL*Plus displays the following output: ID name phone -------- --------- ------------- 2 Jahid 90983043 Take a look at your script (select id, name,&unknown_col from student where id=&unknown_id) Here you did not specified phone column and value for your id but at the execution time of the script. To be continue ...