In this way, a match will be made even if the user types in lower case letters for the &name. In the above example, the UPPER() function is used to change both the column values of lname and the text the user supplied for the &name variable to all upper case letters. WHERE UPPER(lname) like UPPER('%&name%') PROMPT Type the last name of the employee you are looking forĪCCEPT name PROMPT "Employee last name: " For example, to search for a specific employee by name: JENNIFER WALLACE 291 BERRY, BELLAIRE, TX 4įor variables that are not numeric type, the data type is not required in the ACCEPT command. REMARK Prompt the user and get a department number REMARK of the employees in that departmentĬOLUMN dno FORMAT 99999 HEADING 'Dept.|Number'ĬOLUMN address FORMAT A25 HEADING 'Address'ĬOLUMN fname FORMAT A10 HEADING 'First|Name'ĬOLUMN lname FORMAT A12 HEADING 'Last|Name' REMARK last name, first name, address and department number REMARK input from the user and then displays the REMARK This script accepts a department number as In the following example, additional commands have been added including the SET VERIFY and REMARK commands to improve the script. To suppress the display of this verification, use the SET VERIFY OFF command before running the script or as one of the first commands in the script. The following two lines (starting with old and new) are verifying the values used for the dept variable. Once the user types the department number and presses enter, the variable dept takes on the value. The input should be of type NUMBER, and the PROMPT Department Number: should be displayed. In this case, the variable that will hold the input is called dept. The second command, ACCEPT, accepts input from the user. This indicates that the previous command has been completed and isql is.
Whether you need to write, refactor, or explore database code, find out how SQL Prompt Pro makes it effortless by trying it free for 28 days.
The first PROMPT command simply echoes out a line to the display. The SQL prompt: As shown above, the normal isql prompt for input is the SQL> prompt. As well as autocompleting your code, SQL Prompt Pro takes care of formatting, object renaming, and other distractions, so you can concentrate on how the code actually works. In the above example, the user typed “5” in response to the Department Number: prompt. When this script is executed, the following output is shown: PROMPT Type the department you are looking forĪCCEPT dept NUMBER PROMPT "Department Number: " The following example shows the prompt/accept sequence for a query.
SQL*Plus has several commands that can be used to prompt the user for input, accept input from the user and store it in a variable, and then use that variable in a query.