Newbie PL/SQL

From: Patrick Hatcher <pathat_at_home.com>
Date: Sat, 10 Feb 2001 19:41:17 GMT
Message-ID: <xPgh6.78857$R5.3784299_at_news1.frmt1.sfba.home.com>


Howdy,
I'm trying to do my first PL/SQL, and I'm getting stuck. I have two tables (MBM and BCP) that are identical except BCP doesn't have a constraint. I use this table to SQLLDR a flat file.
In my process, I want to add a row to MBM if it doesn't exist and update if it does exist. The BCP table contains about 13k rows, so as a test, I only

wanted to do 10.  However, when I run the process, it appears the loop only
happens 4 times.  I confirmed this by displaying a field from my cursor
after each loop.

Below is the code I wrote. Besides the error I'm getting, I believe the code is mighty inefficient and would appreciate any help I could get.

TIA
Patrick Hatcher

------START-------
cl scr
Set Serveroutput ON
DECLARE
 CURSOR upc_cursor IS
 Select *
 from bcp_acceptance;

 upc_record upc_cursor%Rowtype;

 v_upc bcp_acceptance.upc_number%TYPE;

BEGIN   FOR upc_record in upc_cursor LOOP
   FETCH upc_cursor INTO upc_record;

   DBMS_OUTPUT.PUT_LINE ('UPC from cursor.....'||upc_record.upc_number);

   EXIT WHEN upc_cursor%ROWCOUNT>10 or upc_cursor%NOTFOUND;

 Begin

  /* check if the record exists. If it does update it otherwise add it */   Select upc_number into v_upc from mbm_acceptance where upc_number = upc_record.upc_number;

  if v_upc IS NOT NULL THEN
   update mbm_acceptance
   Set appl_id = upc_record.appl_id;
    DBMS_OUTPUT.PUT_LINE ('Record updated.....');   else
   insert into mbm_acceptance (Company_ID,UPC_number, appl_id)     values(upc_record.company_id,upc_record.upc_number,upc_record.appl_id);       DBMS_OUTPUT.PUT_LINE ('....Record added');   end if;

 EXCEPTION
     WHEN NO_DATA_FOUND THEN
  insert into mbm_acceptance (Company_ID,UPC_number, appl_id)    values(upc_record.company_id,upc_record.upc_number,upc_record.appl_id);   DBMS_OUTPUT.PUT_LINE ('Exception Record updated.....'||upc_record.upc_number);
 END;
END LOOP; END;
/ Received on Sat Feb 10 2001 - 20:41:17 CET

Original text of this message