College finals help! End User input in PL/SQL?

From: roadrunner <kevx_at_tampabay.rr.com>
Date: Fri, 23 Mar 2007 22:13:09 -0400
Message-ID: <46048935$0$19450$4c368faf@roadrunner.com>

Help! I have a final due tomorrow and suddenly they throw in a problem that requires end user input, which we've never covered. They give a hint but it doesn't help much.

Here's what is required:
1.0 Employee Master File/Table
1.1 Design the Employee Table using the following table specifications (10 Points)

Field Name	Data Type	Description
Employee_Code	Text	Primary Key
Employee_SSN	Text	Social Security Number
First_Name	Text	Employee First Name
Last_Name	Text	Employee Last Name
Employee_Doe	Date	Employee Date of Employment
Employee_Dpt	Text	Employee Department
Hourly_Rate	Currency	Employee Wage Hourly Rate
Annual_Salary	Currency	Employee Annual Salary

1.2 Create a BERFORE INSERT trigger that will compute both the Employee Code
and the Annual Salary. Include your program source code with your documentation. (70 Points)

1.3 Write a PL/SQL program that will prompt the end-user to enter some of the
values of the Fields in step 1.1 above. Include your program source code with your documentation. (70 Points)

Example:

Enter Value for Employee First Name………………..……	John
Enter Value for Employee Last Name…………………...…	David
Enter Value for Employee SSN…………………….............	123456789
Enter Value for Employee Employment Date……...............	12/12/2005
Enter Value for Employee Department Name……………..	Engineering
Enter Value for Employee Hourly Rate……………………	20.50

a.	After the end-user has finished inputting the above data for an 

employee, the PL/SQL program will automatically insert the record into the employee Master Table.

b. This record insertion action will trigger a BERFORE INSERT Trigger which will compute both the Employee Code and the Annual Salary

Here is the hint that they give:

SET SERVEROUTPUT ON FEEDBACK OFF
DECLARE
EmployeeFirstName VARCHAR2(25);
BEGIN
                EmployeeFirstName := &Employee_First_Name;         

EXCEPTION

		WHEN OTHERS THEN
			NULL;

END; Running this gives this error:

ERROR at line 4:

ORA-06550: line 4, column 24:
PLS-00201: identifier 'JO' must be declared
ORA-06550: line 4, column 3:

PL/SQL: Statement ignored

Huh? The variable is now named JOE instead of containing Joe? Am I doing something wrong? Wow I'm so bummed over this because I know what to do once I have this but can't figure it out. Anyone? Received on Fri Mar 23 2007 - 21:13:09 CDT