Project 7: Advanced SQL*Plus Scripting

After completing this lab you should be able to:

• Use variables in a sqlplus script.
• Prompt a user for values in a sqlplus script.
• Call operating system commands from a sqlplus script.
• This type of sqlplus scripting is acceptable for use in the final project.

• Review and/or lookup these commands in your appendix:
  • PROMPT
  • ACCEPT
  • & and &&
  • SET VERIFY
  • COLUMN, HEADING, FORMAT
• Also useful for this lab will be running UNIX shell commands like cls.
  You can put !cls in your script to clear the screen.

1. Your guidelines are to make your script mimic the following screen captures.



Figure 1 - First prompt the user for employee id, name, and title information.



Figure 2 - Next, prompt the user for the department. Also, show all valid departments. This is important since there is a Referential Integrity constraint on the employee table which requires valid department IDs.



Figure 3 - Next, prompt the user for the employee's supervisor. As with department id, show all valid supervisors (assume titles are equal to 'Director' or 'Manager'). Finally, insert the row into the EMPLOYEE table with the information provided by the user.

2. OPTIONAL STEP: Before terminating the script you should ask the user if they want to enter data for another employee. Add to your sqlplus script the commands to restart if the user types Y for yes after an appropriate prompt.