Re: Help needed w/ Explicit Cursors and FETCH STMTS

Roberts_Daniel_G.PriLVMS3_at_MSMAIL.BMS.COM (Dan Roberts) wrote:
>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
>

Instead of defining

    C_ResearchEmp_rec Employee%ROWTYPE
try defining it as

    C_ResearchEmp_rec C_ResearchEmp_cur%ROWTYPE and place this definition after the Cursor defintion.The problem with this is that Employee%Rowtype is restricted only to the Employee table

Good luck

Ramesh
Rkrishna_at_us.oracle.com Received on Tue Aug 06 1996 - 00:00:00 CEST