In need of a SQL expert for a procedure.

OK, I have a procedure where Employees working on a PASSED Project Name have their salaries increased by a PASSED percentage. The kicker is I CAN NOT USE A CURSOR and that I must output the old and updated salaries via PUT_LINE. I fugured using indexed types of a table and populating them via filtering on rownum would do this for the SELECT statement that I use to populate the output. The rownum I filter on is a variable that I iterate on via a for loop.

Now that I have confused you, here's my code:

CREATE OR REPLACE PROCEDURE RaiseSalary (

   p_PjtName IN Project.PName%TYPE,
   p_Percent IN Employee.Salary%TYPE) AS

  /*Procedure will take in a project name and a percentage to increase the salary*/

  /*Defined param PjtName with type for attribute PName from table Project above and p_Percent with

    type for attribute Salary from table Employee.*/

   --Declaring Types for output
   TYPE t_OldSalary IS TABLE of Employee.Salary%TYPE INDEX BY BINARY_INTEGER;    TYPE t_NewSalary IS TABLE of Employee.Salary%TYPE INDEX BY BINARY_INTEGER;   --Initializing variables for types and defining indexes   v_NewSalary t_NewSalary;

  v_OldSalary t_OldSalary;

  v_SalaryIndex BINARY_INTEGER;

  v_Idx BINARY_INTEGER;

  --Delcaring variable for loop boundary   v_RecordCount Number;

  --Delcaring variable for exception catching    e_InvalidProjectName EXCEPTION;

BEGIN
--Initializing indexes

 v_SalaryIndex := 1;

  v_Idx := 1;

  --Query w/ count for loop below
 SELECT DISTINCT COUNT(Salary)
  INTO v_RecordCount
 FROM Employee, Project, Works_On
  WHERE works_on.pno = (SELECT project.pnumber FROM project WHERE project.pname = p_PjtName)
  AND employee.ssn = works_on.essn;

--Loop queries rownum..placing 1 value into indexed tables for each
iteration
  FOR free_variable IN 1..v_RecordCount LOOP

    SELECT DISTINCT Salary, Salary * ((p_Percent/100) + 1)     INTO v_OldSalary(v_SalaryIndex), v_NewSalary(v_SalaryIndex)     FROM Employee, Project, Works_On
    WHERE employee.ssn = (SELECT works_on.essn FROM (SELECT works_on.essn, ROWNUM FROM project, works_on

    WHERE works_on.pno = (SELECT project.pnumber FROM project WHERE project.pname = p_PjtName))

    WHERE ROWNUM = v_SalaryIndex);

    v_SalaryIndex := v_SalaryIndex + 1;

  END LOOP;
----Update query

 UPDATE Employee SET Salary = Salary * ((p_Percent/100) + 1)  WHERE SSN IN (SELECT ESSN FROM works_on, project WHERE   works_on.pno = (SELECT project.pnumber FROM project WHERE project.pname = p_PjtName));

--Exception catching

 IF SQL%NOTFOUND THEN
            RAISE e_InvalidProjectName;
  END IF;
--For loop for output

 FOR v_Counter IN 1..v_RecordCount LOOP

    DBMS_OUTPUT.PUT_LINE('Old Salary = ' || v_OldSalary(v_Idx) || ' New Salary = ' || v_NewSalary(v_Idx));

    v_Idx := v_Idx + 1;

 END LOOP; EXCEPTION
   WHEN e_InvalidProjectName THEN

        RAISE_APPLICATION_ERROR (-2002, 'A Project with that name does not exist');

END RaiseSalary;

The procedure compiles, but I receive the following error upon execution:

SQL> exec RaiseSalary('ProductY', 15);
BEGIN RaiseSalary('ProductY', 15); END;

*
ERROR at line 1:

ORA-01722: invalid number
ORA-06512: at "SCM24.RAISESALARY", line 47
ORA-06512: at line 1

Line 47 is for the 2nd 'select' query, and so where am I going wrong? The tables are described below:

SQL> desc employee;

 Name                                                  Null?    Type


 ----------------------------------------------------- -------- ------------

------------------------
 FNAME                                                 NOT NULL VARCHAR2(15)
 MINIT                                                 NOT NULL CHAR(1)
 LNAME                                                 NOT NULL VARCHAR2(15)
 SSN                                                   NOT NULL CHAR(9)
 BDATE                                                          DATE
 ADDRESS                                               NOT NULL VARCHAR2(30)
 SEX                                                            CHAR(1)
 SALARY                                                         NUMBER(5)
 SUPERSSN                                                       CHAR(9)
 DNO                                                            NUMBER(1)

SQL> desc works_on;
 Name                                                  Null?    Type


 ----------------------------------------------------- -------- ------------

------------------------
 ESSN                                                           CHAR(9)
 PNO                                                            NUMBER(2)
 HOURS                                                          NUMBER(4,1)

SQL> desc project;
 Name                                                  Null?    Type


 ----------------------------------------------------- -------- ------------

------------------------
 PNAME                                                 NOT NULL VARCHAR2(20)
 PNUMBER                                                        NUMBER(2)
 PLOCATION                                             NOT NULL VARCHAR2(12)
 DNUM                                                  NOT NULL NUMBER(1)

Last but not least, can you also tell me the best place to put the exception 'catching' statement at it appears to not be working.

Thanks in advance.

Steve Received on Fri Aug 08 2003 - 18:49:35 CDT