Home » SQL & PL/SQL » SQL & PL/SQL » key voilation
key voilation [message #234779] Thu, 03 May 2007 02:39 Go to next message
rzkhan
Messages: 345
Registered: March 2005
Senior Member
hi ..the following error is generated by the procedure. and actually the such key does not exisits.

The procedure

 (mp IN char, yp IN number)
   as
   cursor c1 is select member_id,sum(mess_amount) as aa
   from a
   where to_char(a_date,'MON')=mp
   and  to_char(a_date,'YYYY')=yp
   and flag=1
   group by member_id;
  x c1%rowtype;
  cursor c2 is select * from extra
   where to_char(ext_date,'MON')=mp
   and  to_char(ext_date,'YYYY')=yp;
  x2 c2%rowtype;
   begin
  dbms_output.put_line('inserting mess amount in bill');
  for x in c1 loop
  insert into bill (member_id,item_id,amount,m,y)
  values(x.member_id,15,x.aa,mp,yp);
  dbms_output.put_line(x.member_id||' '||x.aa);
  end loop;
  dbms_output.put_line('inserting extra items and amount in bill');
  for x2 in c2 loop
  insert into bill   (member_id,item_id,amount,m,y) values (x2.member_id,x2.item_id,x2.amount,mp,yp);
  end loop;
   end;


which generates the error

SQL> exec ub('MAR',2007);
inserting mess amount in bill
BEGIN ub('MAR',2007); END;

*
ERROR at line 1:
ORA-00001: unique constraint (MESS.UK_BILL) violated
ORA-06512: at "MESS.UB", line 17
ORA-06512: at line 1



while such key does not exist

  1* select constraint_name,status,table_name from user_constraints
SQL> /

CONSTRAINT_NAME                STATUS   TABLE_NAME
------------------------------ -------- ------------------------------
FK_RECD_HIST_MEM               ENABLED  RECD_HIST
FK_RECD_MEM                    ENABLED  RECD
FK_EXTRA_MEM                   ENABLED  EXTRA
FK_BILL_MEM                    ENABLED  BILL
PK_ATTENDANCE                  ENABLED  ATTENDANCE
PK_DAILY_EXPENSE               ENABLED  DAILY_EXPENSE
UN_ELEC_RT                     ENABLED  ELEC_RT
PK_EXTRA                       ENABLED  EXTRA
PK_FIXED_RT                    ENABLED  FIXED_RT
PK_GAS_RT                      ENABLED  GAS_RT
PK_ITEMS                       ENABLED  ITEMS
PK_LOC                         ENABLED  LOCATIONS
PK_MEMBERS                     ENABLED  MEMBERS
PK_MEM_TYPES                   ENABLED  MEMBER_TYPES
PK_MONTHS                      ENABLED  MONTHS
UK_NET                         ENABLED  NET

16 rows selected.



Please help

Thank you,
rz
Re: key voilation [message #234781 is a reply to message #234779] Thu, 03 May 2007 02:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Are you MESS?

Regards
Michel
Re: key voilation [message #234823 is a reply to message #234779] Thu, 03 May 2007 04:25 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
declare
  x c1%rowtype;
  ...
begin
  ...
for x in c1 loop


This is a bug.
Re: key voilation [message #234830 is a reply to message #234779] Thu, 03 May 2007 04:45 Go to previous messageGo to next message
rzkhan
Messages: 345
Registered: March 2005
Senior Member
yes, I should not declare this variable. But the cause is not this one. I think...am I right?

Thanks
Re: key voilation [message #234832 is a reply to message #234823] Thu, 03 May 2007 04:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Frank,

I don't think so:
SQL> declare
  2    cursor c1 is select * from emp;
  3    x c1%rowtype;
  4  begin
  5    for x in c1 loop
  6      dbms_output.put_line(x.ename);
  7    end loop;
  8  end;
  9  /
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER

PL/SQL procedure successfully completed.

or do you mean the declaration is useless:
SQL> declare
  2    cursor c1 is select * from emp;
  3  begin
  4    for x in c1 loop
  5      dbms_output.put_line(x.ename);
  6    end loop;
  7  end;
  8  /
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER

PL/SQL procedure successfully completed.

Regards
Michel
Re: key voilation [message #234834 is a reply to message #234832] Thu, 03 May 2007 04:53 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
I mean the latter.
The problem is that there will not be an error if you address x outside of the cursor loop, but that x will not have a value.
Re: key voilation [message #234843 is a reply to message #234834] Thu, 03 May 2007 05:23 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
True. Dramatically true.

Regards
Michel
Previous Topic: create a trigger execute in particular date.
Next Topic: Qeury on Rownum.
Goto Forum:
  


Current Time: Sat Dec 03 04:12:08 CST 2016

Total time taken to generate the page: 0.09322 seconds