I am trying to iterate 'employees' table insert function. When I try to execute the code it don't ask for input more than once.
here is my code
DECLARE
l_counter NUMBER := 0;
return_message VARCHAR2(100);
is_exit int;
--want_to_continue number;
emp_name VARCHAR2(70);--:='&f_name';
sur_name VARCHAR2(70);--:='&l_name';
dob VARCHAR2(70);--:='&birth_date';
personalid INT;--:=&per_id;
BEGIN
WHILE l_counter > -1
LOOP
emp_name :='&f_name';
sur_name :='&l_name';
dob :='&birth_date';
personalid :='&per_id';
return_message := fn_Insert_Employees(emp_name, sur_name, to_date(dob, 'YYYY-MM-DD'), personalid);
COMMIT;
l_counter := l_counter + 1;
END LOOP;
END;
The primary key violation error occurs on executing above block of code. Detailed error message is as below
Error report - ORA-00001: unique constraint (SYS.SYS_C007404) violated ORA-06512: at "SYS.FN_INSERT_EMPLOYEES", line 12 ORA-06512: at line 21 00001. 00000 - "unique constraint (%s.%s) violated" *Cause: An UPDATE or INSERT statement attempted to insert a duplicate key. For Trusted Oracle configured in DBMS MAC mode, you may see this message if a duplicate entry exists at a different level. *Action: Either remove the unique restriction or do not insert the key.
I believe the above code just re-use the firstly input values in the next iteration as well.
Please help me out to get input variables iterated.
l_counter > -1
and your code only ever increments that counter. - APCl_counter >-1
@APC - Muhammad Waheed"SYS.FN_INSERT_EMPLOYEES"
. Do not use the SYS schema to build your application objects. SYS and SYSTEM are managed by Oracle. Changing either schema could corrupt your database and may invalidate your Support contract. Connect as SYSTEM to create a new user account, then use that user for your application. - APC