Home » SQL & PL/SQL » SQL & PL/SQL » Update cannot do in procedure
Update cannot do in procedure [message #432147] Sun, 22 November 2009 01:48 Go to next message
Messages: 48
Registered: April 2009
Location: malaysia

Hi all guru's, i got a doubt that cant be answered here... i need to update a table in a procedure but unexpectedly it cant be done... hre are the code:

cursor c14 is
select  a.contact_name, b.scr_name v_name, b.emplid
from PS_TEST a, PS_TAKE b where b.emplid = a.emplid
for update of a.contact_name;   

        for rec in c014 loop
        select scr_last_name v_mname1, scr_middle_name v_lname1, scr_first_name v_fname1
        , (scr_first_name ||' '||scr_last_name||' '||scr_middle_name) v_name1
        into v_mname1, v_lname1, v_fname1, v_name1
        from PS_TAKE sample(1) where rownum = 1 ; 
        set contact_name = v_name1
        --where contact_name = rec.contact_name;
        where current of c014;
      exit when c014%notfound;        
      end loop;

Above are the cursor and the code that related. This code cant be done in procedure, it ill show this error :

ERROR at line 1:
ORA-00001: unique constraint (SYSADM.PS_TEST) violated
ORA-06512: at line 1

But, what weird me is when i try to do direct update...

Update PS_TEST
set contact_name = 'Testing'

i can be done..

Kindly need all of your sggestion and opinion on this. An advance, really appreciate on it.
Re: Update cannot do in procedure [message #432149 is a reply to message #432147] Sun, 22 November 2009 02:23 Go to previous message
Michel Cadot
Messages: 65085
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ORA-00001: 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.

Whatever you say this is true.

Check you reach the same object inside the procedure and outside it.

Previous Topic: Parallel Degree on a table
Next Topic: basic PL/SQL question about recursive procedure..
Goto Forum:

Current Time: Mon Jul 24 12:14:17 CDT 2017

Total time taken to generate the page: 0.08528 seconds