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
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: 25039
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
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
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
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
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


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
Previous Topic: Inserting value from varchar2 to date field
Next Topic: Cursor already open Error
Goto Forum:
  


Current Time: Mon Dec 05 20:53:45 CST 2016

Total time taken to generate the page: 0.22557 seconds