Subsequent reference to the variable (even in the same command) using either "&" or If a double ampersand reference causes SQL*Plus to prompt you for a value, then SQL*Plusĭefines the variable as that value (that is, the value is stored until you exit). SQL> select employee_id from &mytable where last_name = 'Jones' Different values can beĮntered at each prompt: SQL> prompt Querying table &mytable Twice, even in the same statement, then you are prompted twice. Statement the variable is discarded and remains undefined.
Immediately after the value is substituted in the If a single ampersand prefix is used with an undefined variable, the value Not previously defined then SQL*Plus prompts you for a value before doing the Substitution variable name in a statement. SQL*Plus prompts you for a value for the variable "loc".īoth single ampersand (&) and double ampersand (&) can prefix a Here the text "& loc" in the comment is interpreted as a variable For example: SQL> select department_id, location_id /* get dept & loc */ from departments Text in ANSI "/* */" or "-" comments that looks like a substitution "reports" then the command: SQL> spool &myfile.log If you want to append a period immediately after a substitution variable Is interpreted as SELECT SALARY FROM EMP_DETAILS_VIEW WHERE EMPLOYEE_ID='205' SELECT SALARY FROM EMP_DETAILS_VIEW WHERE EMPLOYEE_ID='&X.5' If you wish to append characters immediately after a substitution variable, use a period to separate the variable from the character. Name can be entered each time the script is run. If these two commands are stored in a SQL*Plus script, a different last
SQL> select employee_id from employees where last_name = '&myv' SQL*Plus displays the following output: JOB_ID MAXIMUMĪ more practical use of substitution variables is to prompt for a valueīefore referencing the variable: SQL> accept myv char prompt 'Enter a last name: ' Now run the script STATS: respond to the prompts for values as shown: Enter value for group_col: JOB_ID You can suppress this listing by setting the SET command variable VERIFY to OFF. SQL*Plus reads your response from the keyboard or standard input.Īfter you enter a value at the prompt, SQL*Plus lists the line containing the substitution variable twice: once before substituting the value you enter and once after substitution. If the SQL command containing the reference should have quote marks around the variable and you do not include them there, the user must include the quotes when prompted. You can enter any string at the prompt, even one containing blanks and punctuation. When SQL*Plus encounters an undefined substitution variable in a command, SQL*Plus prompts you for the value. You can use substitution variables anywhere in SQL and SQL*Plus commands, except as the first word entered.
0 kommentar(er)
