How to Duplicate multiple rows (Oracle)

Question

I'm trying to make a Procedure that will duplicate multiple rows of a table (or only one single row) and incrementing the ID for each row insertion. My problem is that inside my procedure I used a cursor to select the rows to duplicate, when i select all rows without WHERE condition in that cursor everything works fine. But when i set a WHERE condition to select only one row... nothing happens Here is my procedure ``` CREATE OR REPLACE PROCEDURE DuplicateEmployee (p_EmployeeID IN Employee.id%TYPE) AS p_New_EmployeeID Employee.id%TYPE; CURSOR c_DuplicateEmployee IS SELECT * FROM Employee WHERE Employee.id = p_EmployeeID; -- if this line is deleted all content is duplicated row_Employee c_DuplicateEmployee%ROWTYPE; BEGIN FOR myEmployee IN c_DuplicateEmployee LOOP p_New_EmployeeID := employee_seq.NEXTVAL; INSERT INTO Employee(id, first_name, last_name, start_date, end_date, salary, city, description) VALUES(p_New_EmployeeID, myEmployee.first_name, myEmployee.last_name, myEmployee.start_d…

Accepted Answer

Why do you need a cursor? You can do this with SQL directly:

```
INSERT INTO Employee(id, first_name, last_name, 
                     start_date, end_date, 
                     salary, city, description)
SELECT employee_seq.NEXTVAL, e.first_name, e.last_name,
       e.start_date, e.end_date, 
       e.salary, e.city, e.description
FROM Employee e
WHERE e.id = p_EmployeeID;
```

Anyway, the actual problem is that your `ID` is a `VARCHAR2(4)`, whereas you think it is a `NUMBER`. You actually do not have an employee with `ID = 5`, but you do have one with `ID = '05'`. So without changing anything, your procedure already works:

```
BEGIN
  employeepackage.duplicateemployee('05');
END;
```

Of course, it would make sense to change the data type of `ID`.

How to Duplicate multiple rows (Oracle)

Solution

Problem