Re: Help needed w/ Explicit Cursors and FETCH STMTS

Dan 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

Seems to me Dan that the problem lies in the Select statement of your declared cursor c_researchemp_Cur in that because you are selecting * you are making it return all columns from both tables and as you are fetching it into a rowtype of employee table only it hasnt got enough columns defined. You could try selecting Employee columns only if thats all you need for processing, I believe you can use an alias for the table like this - but I'm willing to be contradicted !!!

select emp.*
from employee emp, department dept

-- 
Regards

 
Julian Rees

jrees_at_twnjewel.demon.co.uk

Tel: 0850 764764