SQL & PL/SQL

Hello Everyone,

What I am trying to accomplish is to duplicate some records in a table based on a condition. I have included a small subset of the program below.

To accomplish this, I am using an INSERT statement with the returning option.

I am inserting a new record in a table and generates the primary key from a sequence. However, I need the value of that primary key after the record is inserted, so that I can update other tables. I thought that the INSERT with RETURNING option could do the job.

But I can't figure out the syntax of the RETURNING option. The code I have below for the INSERT is returning an error "Statement not properly ended" at a different line when I add the INSERT statement. Here is the program subset with the INSERT statement. It looks quite obvious to me but I don't know what I am doing wrong or what I am missing.

Thanks for any help you can provide.

DECLARE
nEmployee_Id NUMBER(16);
CURSOR cuEmployee is SELECT employee.*, ROWID FROM Employee
reg_code = 'C' AND workprovince IN('NU','YT','AB','MB','NT','SK');

BEGIN
FOR REC in cuEmployee LOOP
EXIT WHEN cuEmployee%NotFound;
BEGIN
INSERT INTO Employee (Employee_Id, Employee_Name, reg_code)
(SELECT employee_seq.nextval, Employee_Name, 'PN'
FROM Employee WHERE Employee_Id = rec.Employee_ID AND reg_code = rec.reg_code)
RETURNING Employee_Id INTO nEmployee_Id;
END IF;
-- I need to use the nEmployee_Id of the new record here
--COMMIT;
END;
END LOOP;
END;
/