Re: pl/sql coding help

open c2;
fetch c2 into postn_id ;
while c2%FOUND LOOP

    BEGIN -- anonymous block

         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;
    EXCEPTION  -- handles errors inside block
        WHEN no_data_found THEN
            NULL ;
            -- since no_data_found has been "handled"
            -- you are still inside the "fetch" loop and will fetch the next

    END ;
    fetch c2 into postn_id ;
END LOOP ;
close c2;

Ed Lufker wrote:

> 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 Thu Feb 11 1999 - 05:19:13 CST