CODEWITHSUNDEEP
sqloracle-databaseplsqloracle-sqldeveloper
user3960875
user3960875

Reputation: 1005

PL/SQL compilation error, can't figure out whats broken

So my SQL procedure is following:

create or replace procedure generate_sample_dd_contracts(cnt in pls_integer)
is
begin
  for cur in (select * from (select * from tc_client cli where not exists (select 1 from tc_direct_debit dd where dd.cli_id=cli.id) order by dbms_random.value) where rownum < dbms_random.value(2,100))
    -- select query works fine and gets clients as needed
    loop
      for i in 0..cnt
        loop
          insert into tc_direct_debit (cli_id, ref_number) values (cur.id, tc_ref_num_seq.nextval);
          -- this insert also works separately from procedure, I have autoinc trigger
        end loop;
      end loop;
  commit;
end;
/
begin
  generate_sample_dd_contracts(3);
end;

First for loops select query result enter image description here

Also when I run the procedure tc_direct_debit ID gets updated (into clause) but no data appears in table.

And the error report:

PROCEDURE GENERATE_SAMPLE_DD_CONTRACTS compiled
Error starting at line 19 in command:
begin
  generate_sample_dd_contracts(3);
end;
Error report:
ORA-00001: unique constraint (T.U_DIRECT_DEBIT_CLI_ID) violated
ORA-06512: at "T.GENERATE_SAMPLE_DD_CONTRACTS", line 9
ORA-06512: at line 2
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.

From this I understand that insert tries to add a cli_id that already exists but tc_direct_debit is empty (have truncated it many times to be sure).

tc_direct_debit table

`CREATE TABLE TC_DIRECT_DEBIT
(ID NUMBER NOT NULL
, CLI_ID NUMBER NOT NULL 
, REF_NUMBER VARCHAR2(20) NOT NULL 
, CONSTRAINT TC_DIRECT_DEBIT_PK PRIMARY KEY (ID)
, CONSTRAINT U_DIRECT_DEBIT_CLI_ID UNIQUE (CLI_ID)
, CONSTRAINT TC_DIRECT_DEBIT_CLI_FK FOREIGN KEY (CLI_ID) REFERENCES TC_CLIENT (ID)
);`

Upvotes: 1

Views: 118

Answers (2)

Florin Ghita
Florin Ghita

Reputation: 17643

For every line in the cursor you insert it four times (from 0 to three).

A code doing what I think you want to do is:

create or replace procedure generate_sample_dd_contracts(cnt in pls_integer)
is
declare 
i number;
begin
i:=0;
  for cur in (select * from (select * from tc_client cli where not exists (select 1 from tc_direct_debit dd where dd.cli_id=cli.id) order by dbms_random.value) where rownum < dbms_random.value(2,100))
    loop
      i:=i+1;
      exit when i > cnt;
      insert into tc_direct_debit (cli_id, ref_number) values (cur.id, tc_ref_num_seq.nextval);
    end loop;
  commit;
end;
/

Or better, just changing the cursor code:

create or replace procedure generate_sample_dd_contracts(cnt in pls_integer)
is
begin
  for cur in (select * from (select * from tc_client cli where not exists (select 1 from tc_direct_debit dd where dd.cli_id=cli.id) order by dbms_random.value) where rownum <= cnt)
    loop
      insert into tc_direct_debit (cli_id, ref_number) values (cur.id, tc_ref_num_seq.nextval);
    end loop;
  commit;
end;
/

Upvotes: 2

Exhausted
Exhausted

Reputation: 1885

It seems that the CLI_ID is mentioned as unique key which means the dulicate will not accepted at any cost. wherears in the procedure line 9 has looped insert 

 for i in 0..cnt
   loop
     insert into tc_direct_debit (cli_id, ref_number) values (cur.id, tc_ref_num_seq.nextval);
   -- this insert also works separately from procedure, I have autoinc trigger
 end loop;

removing the unique contraint U_DIRECT_DEBIT_CLI_ID will resolve your issue

Upvotes: 1

Related Questions