Help needed w/ Explicit Cursors and FETCH STMTS

From: Dan Roberts <Roberts_Daniel_G.PriLVMS3_at_MSMAIL.BMS.COM>
Date: 1996/08/02
Message-ID: <Roberts_Daniel_G.PriLVMS3-0208960702270001_at_riversend.bms.com>#1/1


Hi Netters..I am getting all tangled up with using an explicit cursor.. What I am trying to do is get all the SSN's of employee's from the employee table
and select those those employee's who work for the research dept (DNUMBER and DNO=5, DNAME='Research' )
and take those reserch dept employees..and add these employees to the Works_on table and assing them to project 10 and assign them 20 hours worth of work..I have tried this tons of different ways..and I know I am getting hung up on the use of the explicit cursor..and how to either insert or update he works-on table..>Thanks for any and all help!!!>.>Dan

1 CREATE OR REPLACE PROCEDURE ASSIGNNEWPJT IS

  2  V_SSN      EMPLOYEE.SSN%TYPE;
  3  V_PNO      Works_on.pno%TYPE;
  4  V_Hours    Works_on.hours%TYPE;
  5  C_ResearchEmp_rec Employee%ROWTYPE;
  6  CURSOR C_ResearchEmp_cur IS
  7     SELECT  *
  8     FROM  employee,department
  9     WHERE dno=dnumber AND dname='Research';
 10 BEGIN
 11 LOOP
 12     OPEN  C_ResearchEmp_cur;
 13     FETCH C_ResearchEmp_cur
 14      INTO C_ResearchEmp_rec;
 15     UPDATE works_on
 16             SET     V_SSN= C_ResearchEmp_rec.ssn,
 17                     V_PNO=10,V_HOURS=20
 18  WHERE dname='Research' and DNO=Dnumber and Employee.ssn=works_ssn;         
 19     EXIT WHEN C_ResearchEmp_cur%NOTFOUND;
 20 END LOOP;
 21 COMMIT;
 22* END assignnewpjt;
SQL> / Warning: Procedure created with compilation errors.

SQL> show errors;
Errors for PROCEDURE ASSIGNNEWPJT:

LINE/COL ERROR

-------- -----------------------------------------------------------------
13/2     PLS-00394: wrong number of values in the INTO list of a FETCH
         statement

13/2     PL/SQL: SQL Statement ignored
15/2     PLS-00417: table or view or column does not exist
15/2     PL/SQL: SQL Statement ignored

SQL> SQL> desc employee;
 Name                            Null?    Type

------------------------------- -------- ----
FNAME NOT NULL CHAR(15) MINIT CHAR(1) LNAME NOT NULL CHAR(15) SSN NOT NULL CHAR(9) BDATE DATE ADDRESS CHAR(30) SEX CHAR(1) SALARY NUMBER(7) SUPERSSN CHAR(9) DNO NUMBER(1) SQL> desc works_on; Name Null? Type
------------------------------- -------- ----
ESSN NOT NULL CHAR(9) PNO NOT NULL NUMBER(2) HOURS NUMBER(4,1) SQL> desc department; Name Null? Type
------------------------------- -------- ----
DNAME NOT NULL CHAR(15) DNUMBER NOT NULL NUMBER(1) MGRSSN CHAR(9) MGRSTARTDATE DATE

S

-- 
Dan Roberts
BRISTOL-MYERS SQUIBB PHARM. RES. CENTER      "Nature is last at Bats"
PRINCETON, NEW JERSEY U.S.A. PLANET EARTH, MILKY-WAY GALAXY
<<<<INTERNET ADDRESS>>>>Roberts_Daniel_G.PriLVMS3_at_MSMAIL.BMS.COM
Received on Fri Aug 02 1996 - 00:00:00 CEST

Original text of this message