CODEWITHSUNDEEP
sqloracle-databasefunctionloopsplsql
Muhammad Waheed
Muhammad Waheed

Reputation: 1088

How to iterate input variables using PL/SQL

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.

Upvotes: 0

Views: 370

Answers (1)

Belayer
Belayer

Reputation: 14861

This indicated a sever lack of understanding a couple of issues.

  1. As @APC plsql (and also sql) are non interactive. Going slightly further this results in them both being not able to prompt for input. You need to understand substation variables. You never directly access the database, there is always a layer of software between you and the database (your IDE, sqlplus, sqlcl, development language, etc). It is this layer that notices substitution variable (&...) and prompts for its value. At that point it physically changes that line of code substituting (thus the name) the entered value before submitting to the database. The database never sees the actual code with the substitution variable.
  2. "I do want to work this loop unlimited times". No you don't.. Doing so only has 2 ways of terminating. It will throw an exception when it used up ALL of an necessary resource, be that memory, rollback segments, ... If never exhausts an available resource it will literally run forever, unless killed by the DBA.

Upvotes: 1

Related Questions