Home » SQL & PL/SQL » SQL & PL/SQL » ORA-02055 ORA-00001and :new (oracle 9i)
ORA-02055 ORA-00001and :new [message #313387] Fri, 11 April 2008 10:22 Go to next message
alexaoracle
Messages: 36
Registered: October 2007
Member
Hi,

I have a trigger for insert and updating other tables in another db, so I have a dblink for this.
The problem I have is that I get the following errors

ORA-02055: distributed update operation failed; rollback required
ORA-00001: unique constraint (OPS$db2.PK ) violated
ORA-02063: preceding line from mydblink
ORA-06512: at "db1.trigger", line
ORA-04088: error during execution of trigger 'db1.trigger'

when in my trigger I use :new.name refering a value on the insert

my insert is something like insert into client(id,name) values(a.nextval,concat('1_','peter')

but if instead of :new, in order to test the trigger, I put 'peter' it works fine the first time, because its not violating the unique constraint, the second time, trigger raise its hand like it should.
it is an after insert or update trigger

Its really weird why is not taking :new correctly

Report message to a moderator

Re: ORA-02055 ORA-00001and :new [message #313388 is a reply to message #313387] Fri, 11 April 2008 10:29 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & FOLLOW posting guidelines as stated in URL above

Report message to a moderator

Re: ORA-02055 ORA-00001and :new [message #313391 is a reply to message #313388] Fri, 11 April 2008 11:03 Go to previous messageGo to next message
alexaoracle
Messages: 36
Registered: October 2007
Member
insert on table_db1

INSERT INTO mat(mat, NC, D,..,) VALUES(S.NEXTVAL,'L01',88,...)



CREATE OR REPLACE TRIGGER I
AFTER INSERT OR DELETE OR UPDATE ON TABLE_db1
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
HIST NUMBER;
CAD VARCHAR(20);
BEGIN
   IF INSERTING THEN

     SELECT COUNT(*) INTO HIST FROM TABLE_db2 WHERE (SACTI=CONCAT('DL_', TO_CHAR(:NEW.NC)) AND  SFOR='DL' AND IDP= :NEW.D);
     IF HIST = 0 THEN
       CAD := CONCAT('DL_', TO_CHAR(:NEW.NC));
       --HIS
       INSERT INTO SHT (IDO, SHET, SACTI, SFOR, IDP, ENT)
--     VALUES ('5', '000', NULL, CONCAT('DL_', TO_CHAR(:NEW.NC)), 'DL',:NEW.D, 1); --DOES NOT WORK, BUT IF I CHANGE FOR  	
     --VALUES ('5', '000', NULL, CAD, 'DL',:NEW.D, 1);	                   --DOES NOT WORK, BUT IF I CHANGE FOR  	
    -- VALUES ('5', '000', NULL, 'DL_L01', 'DL',:NEW.D', 1);   -- THIS ONE, IT WORKS FINE, OF COURSE 1RST TIME
-- 	
    END IF;
END IF;
END;


unique constraint is over IDO, SACTI, SFOR and ENT.
Also to notice, If I do inserts directly on table_db2 like this

INSERT INTO SHT (IDO, SHET, SACTI, SFOR, IDP, ENT)
VALUES ('5', '000', NULL, 'DL_L01', 'DL','L01', 1);

it inserts on this table

and
s.nextval


works fine

Report message to a moderator

Re: ORA-02055 ORA-00001and :new [message #313392 is a reply to message #313391] Fri, 11 April 2008 11:10 Go to previous messageGo to next message
alexaoracle
Messages: 36
Registered: October 2007
Member
insert on table_db1

INSERT INTO mat(mat, NC, D,..,) VALUES(S.NEXTVAL,'L01',88,...)


CREATE OR REPLACE TRIGGER I
AFTER INSERT OR DELETE OR UPDATE ON TABLE_db1
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
HIST NUMBER;
CAD VARCHAR(20);
BEGIN
   IF INSERTING THEN

     SELECT COUNT(*) INTO HIST FROM TABLE_db2 WHERE 
        (SACTI=CONCAT('DL_', TO_CHAR(:NEW.NC)) AND  SFOR='DL' AND IDP= :NEW.D);
     IF HIST = 0 THEN
       CAD := CONCAT('DL_', TO_CHAR(:NEW.NC));
       --HIS
       INSERT INTO SHT (IDO, SHET, SACTI, SFOR, IDP, ENT)
     --next line DOES NOT WORK
     --VALUES ('5', '000', NULL, CONCAT('DL_', TO_CHAR(:NEW.NC)), 'DL',:NEW.D, 1); 
     --next line DOES NOT WORK	
     --VALUES ('5', '000', NULL, CAD, 'DL',:NEW.D, 1);	    
     -- but THIS ONE, IT WORKS FINE, OF COURSE 1RST TIME                	
     -- VALUES ('5', '000', NULL, 'DL_L01', 'DL',:NEW.D', 1);   
-- 	
    END IF;
END IF;
END;

unique constraint is over IDO, SACTI, SFOR and ENT.
Also to notice, If I do inserts directly on table_db2 like this

INSERT INTO SHT (IDO, SHET, SACTI, SFOR, IDP, ENT)
VALUES ('5', '000', NULL, 'DL_L01', 'DL','L01', 1);

it inserts


s.nextval
works fine

Report message to a moderator

Re: ORA-02055 ORA-00001and :new [message #313396 is a reply to message #313392] Fri, 11 April 2008 12:06 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Quote:

INSERT INTO SHT (IDO, SHET, SACTI, SFOR, IDP, ENT)
--next line DOES NOT WORK
--VALUES ('5', '000', NULL, CONCAT('DL_', TO_CHAR(:NEW.NC)), 'DL',:NEW.D, 1);
--next line DOES NOT WORK
--VALUES ('5', '000', NULL, CAD, 'DL',:NEW.D, 1);
-- but THIS ONE, IT WORKS FINE, OF COURSE 1RST TIME
-- VALUES ('5', '000', NULL, 'DL_L01', 'DL',:NEW.D', 1);
INSERT INTO SHT (IDO, SHET, SACTI, SFOR, IDP, ENT)
VALUES ('5', '000', NULL, 'DL_L01', 'DL','L01', 1);

Are you sure above insert works correctly ? I don't think so because you are giving 6 columns in your list and trying to supply 7 values.
SQL> insert into t (col1, col2, col3)
  2  values ('a','b','c','d');
insert into t (col1, col2, col3)
            *
ERROR at line 1:
ORA-00913: too many values


SQL> desc t;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 COL1                                               VARCHAR2(100)
 COL2                                               VARCHAR2(100)
 COL3                                               VARCHAR2(100)


Regards

Raj

Report message to a moderator

Re: ORA-02055 ORA-00001and :new [message #313400 is a reply to message #313396] Fri, 11 April 2008 12:36 Go to previous messageGo to next message
alexaoracle
Messages: 36
Registered: October 2007
Member
yes, you are right.
Since there were too many values, I shorten the insert statement, 

CREATE OR REPLACE TRIGGER I
AFTER INSERT OR DELETE OR UPDATE ON TABLE_db1
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
HIST NUMBER;
CAD VARCHAR(20);
BEGIN
   IF INSERTING THEN

     SELECT COUNT(*) INTO HIST FROM TABLE_db2 WHERE 
        (SACTI=CONCAT('DL_', TO_CHAR(:NEW.NC)) AND  SFOR='DL' AND IDP= :NEW.D);
     IF HIST = 0 THEN
       CAD := CONCAT('DL_', TO_CHAR(:NEW.NC));
       --HIS
       INSERT INTO SHT (IDO, SHET, SACTI, SFOR, IDP, ENT)
     --next line DOES NOT WORK
     --VALUES ('5', '000', CONCAT('DL_', TO_CHAR(:NEW.NC)), 'DL',:NEW.D, 1); 
     --next line DOES NOT WORK	
     --VALUES ('5', '000', CAD, 'DL',:NEW.D, 1);	    
     -- but THIS ONE, IT WORKS FINE, OF COURSE 1RST TIME                	
     -- VALUES ('5', '000','DL_L01', 'DL',:NEW.D', 1);   
-- 	
    END IF;
END IF;
END;



and also 

INSERT INTO SHT (IDO, SHET, SACTI, SFOR, IDP, ENT)
VALUES ('5', '000', 'DL_L01', 'DL','L01', 1)


hope this time, I did not forget anything

Report message to a moderator

Re: ORA-02055 ORA-00001and :new [message #313402 is a reply to message #313400] Fri, 11 April 2008 12:47 Go to previous message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
 
SQL> insert into t (col1, col2, col3)
  2  values ('a','b','c','d');
insert into t (col1, col2, col3)
            *
ERROR at line 1:
ORA-00913: too many values

We would like to see something like what I did above to explain the problem rather than explaining or giving comments around the code saying this code is not working , this code works for the first time. It is much easier for us replicate. Is there by any chance your Caps lock is stuck to your keyboard while you are writing some code?

Regards

Raj

Report message to a moderator

Previous Topic: Inserting value from varchar2 to date field
Next Topic: Cursor already open Error
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Fri Aug 29 09:09:14 CDT 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.