pl/sql coding help

From: Ed Lufker <elufker_at_swcp.com>
Date: 11 Feb 1999 00:22:41 GMT
Message-ID: <79t7sh$6dj$1@sloth.swcp.com>

Hi All:

        Does anyone have any examples of trapping the ORA-01405 error and just continuing after the error happens. This procedure runs in batch and when I get a no rows found the procedure exits. Below is the code:

Create or replace procedure updtlog as

--

• variables
  --
  cnt number(11,2) :=0; line varchar2(255);

LOCID            VARCHAR2(50);
COMPANY          VARCHAR2(100);
FNAME            VARCHAR2(50);
LNAME            VARCHAR2(50);
ACCOUNT_ID       VARCHAR2(50);
CONTACT_ID       VARCHAR2(15);
POSTN_ID         VARCHAR2(15);
POS_NAME         VARCHAR2(50);
EMP_ID           VARCHAR2(15);
NEW_LOGIN        VARCHAR2(50);

cursor c1 is
        select
                tmp_locid,
                tmp_co_name,
                tmp_fname,
                tmp_lname
        from
                siebel.tmp_ems_load
        where exists(select a.pr_postn_id
              from s_org_ext a, s_emp_postn b
              where a.loc = tmp_locid
              and a.pr_postn_id = b.position_id);

CURSOR C2 IS 
        SELECT PR_POSTN_ID
        FROM S_ORG_EXT
        WHERE locid = LOC;

BEGIN
   dbms_output.put_line('TOP......');
open c1;
loop
 dbms_output.put_line('AFTER LOOP1 ');

  fetch c1 into locid,company,fname,lname;

  exit when c1 %NOTFOUND;

 dbms_output.put_line('FILE ==> '||LOCID);
 dbms_output.put_line('FILE ==> '||COMPANY);
 dbms_output.put_line('FILE ==> '||FNAME);
 dbms_output.put_line('FILE ==> '||LNAME);





open c2;
      fetch c2 into postn_id; 

exit when c2 %NOTFOUND;

     SELECT login into new_login 
     FROM SIEBEL.S_EMP_POSTN emp, 
      SIEBEL.S_EMPLOYEE e,
      SIEBEL.S_POSTN p
      WHERE emp.POSITION_ID = POSTN_ID
      AND emp.EMP_ID = E.ROW_ID
      AND emp.POSITION_ID = p.ROW_ID
      AND (p.NAME LIKE 'N0%' or p.name like 'S0%' or p.name like 'W0%')
      and e.login != 'SADMIN'
      and rownum < 2;

        update tmp_ems_load set login = new_login
        where tmp_locid = locid;
        commit;

close c2;

   dbms_output.put_line('BOTTOM LOOP2');         

  cnt := cnt + 1;
end loop;

   dbms_output.put_line('TOTAL RECORDS UPDATED: ' ||cnt);

close c1;
--EXCEPTION

• WHEN OTHERS THEN
• line := SQLERRM(SQLCODE);
• dbms_output.put_line('TMP_EMS_LOAD ERROR: '||line);
• ROLLBACK;

END;
/

thanks in advance for any help here
eddie lufker Received on Wed Feb 10 1999 - 18:22:41 CST