Home » SQL & PL/SQL » SQL & PL/SQL » Primary key violation (9.0.4.0)
Primary key violation [message #335614] Wed, 23 July 2008 00:23 Go to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

Hi I wrote following PL/SQL procedure. I am getting Primary key Viloation error .Please look into the code and do the modification if required. In the table I am having only one records Then also I am getting the same issue.
CREATE OR REPLACE PROCEDURE POPULATE_ITEMS IS
cursor c_commodity_id is
select distinct nco.commodity_id
  from non_contents_order nco,commodity cd
 where nco.commodity_id=cd.commodity_id
   and nco.status<>'C'
   and nco.order_type='P'
   and nco.stocking_point_id in (
                                select stocking_point_id 
                                  from stocking_point sp
                                 where substr(sp.stocking_point_name,2,4) in (select TIER1_VIRTUAL_WH
                                                                                from MULTI_TIER_WH
                                                                               where status='A')
                                    OR substr(sp.stocking_point_name,2,4) in (select TIER2_VIRTUAL_WH
                                                                                from  MULTI_TIER_WH
                                                                               where status='A')
                              );

cursor c_item_tier(P_commodity_id commodity.commodity_id%type) is
Select cd.commodity_code item,substr(sp.stocking_point_name,2,4) loc,sp.tier
  from non_contents_order nco, stocking_point sp,commodity cd
 where nco.stocking_point_id  = sp.stocking_point_id
   and nco.commodity_id = P_commodity_id
   and nco.commodity_id = cd.commodity_id
   and nco.status<>'C'
   and nco.order_type='P'
   and rownum=1
order by nco.release_date,nco.timestamp desc;

cursor c_item(P_item  multi_tier2_item.item%type) is
select item 
  from multi_tier2_item amti
 where amti.item=P_item;

L_commodity_rec    c_commodity_id%rowtype;
L_item_tier_rec    c_item_tier%rowtype;
L_item             c_item%rowtype;

L_program    VARCHAR2(62) := 'POPULATE_ITEMS';


BEGIN

open c_commodity_id;

loop 

fetch c_commodity_id into L_commodity_rec;

exit when c_commodity_id%notfound;

  open c_item_tier(L_commodity_rec.commodity_id);

  loop

  fetch c_item_tier into L_item_tier_rec;
                 
     if c_item_tier%Found then

        if L_item_tier_rec.tier=2 then

            open c_item(L_item_tier_rec.item);
                       
            fetch c_item into L_item;
                    
            if c_item%notfound then
            
             insert into argos_aip_multi_tier2_item 
                values (L_item_tier_rec.loc,L_item_tier_rec.item); 
            else
            
            null;
           
            end if;
            
            
            close c_item;
         
        else
            open c_item(L_item_tier_rec.item);
                       
            fetch c_item into L_item;
             
                       
            if c_item%found then
          
               delete from multi_tier2_item amti
                where amti.item=L_item_tier_rec.item;
                
            else
            
            null;
                     
            end if;
                        
            close c_item;
              
        end if ;
          
     else
          
       exit;
                       
     end if;   

  end loop;

  close c_item_tier;

end loop;
close c_commodity_id;       

END POPULATE_ITEMS;


Thanks in advance
Re: Primary key violation [message #335628 is a reply to message #335614] Wed, 23 July 2008 00:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why do you get a primary key violation?

By the way, remove the useless empty lines from your code, it makes your code longer for nothing but leading to be harder to read.

Regards
Michel
Re: Primary key violation [message #335631 is a reply to message #335628] Wed, 23 July 2008 00:52 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

I have only one record in the table. But I use LOOP.. Hope that might one cause. please let me know where I have to do modifications... I Have deleted the LOOP and End Loop . Still I am facing the same issue.
Re: Primary key violation [message #335634 is a reply to message #335631] Wed, 23 July 2008 00:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I have only one record in the table.

Which one?

Put dbms_output at each line in your code, execute it and post the result. In short, debug.

Regards
Michel
Re: Primary key violation [message #335635 is a reply to message #335614] Wed, 23 July 2008 01:02 Go to previous message
himang
Messages: 282
Registered: March 2005
Location: Bangalore
Senior Member

Unless you have some test data to depict the scenario it is difficult to come to a conclusion. What is your primary key in argos_aip_multi_tier2_item table? You are checking for item value in multi_tier2_item table whereas inserting into argos_aip_multi_tier2_item?

Is your logic correct?
Previous Topic: How to know a package is being called
Next Topic: Execution Monitoring
Goto Forum:
  


Current Time: Thu Dec 08 21:47:24 CST 2016

Total time taken to generate the page: 0.15851 seconds