Home  • Database • Oracle

Substitution Variables

oralce logo 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 ...

Comments 0


Share