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

On Fri, 23 Mar 2007 22:13:09 -0400, roadrunner <kevx_at_tampabay.rr.com> wrote:

>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?

The code provided is incorrect. If you still don't know how to address this, you deserve to fail the final. You don't deserve to succeed the final by cheating, by getting help from this forum. In the big world out there nobody will hold your hand.

-- 
Sybrand Bakker
Senior Oracle DBA