RE: cursor

You were not re-setting v_fi after you update. 
Anyway, there is no need to set a cursor for the censusomc table. You just
need to loop in table carrier_op_class. do it like this:

SET SERVEROUTPUT ON
DECLARE
        cursor c1 is
        SELECT DOT_NUMBER, CLASSIFICATION_ID
        FROM CARRIER_OP_CLASS;
       dot_num c1.dot_number%TYPE := null;
       v_f1 varchar2(12) := null;
begin 
    FOR c1_rec IN c1
         LOOP
            if dot_num is null then
               dot_num := c1_rec.dot_number;
               v_f1 := c1_rec.classification_id;
            else
               if dot_num <> c1_rec.dot_number then
                  update censusomc
                      set master_field = v_f1
                      where dot_number = dot_num;
                    dot_num := c1_rec.dot_number;
                    v_f1 := c1_rec.classification_id;
                else
                   v_f1 := v_f1||c1_rec.classifcation_id;
                end if;
            end if;
         END LOOP;
             if v_f1 is not null then
                  update censusomc
                     set master_field = v_f1
                       where dot_number = dot_num;
             end if;
     END;

-----Original Message-----
From: Natividad Castro [mailto:[email protected]]
Sent: Wednesday, December 04, 2002 7:35 AM
To: Oracle
Subject: [oracle] cursor


hi to all,
I don't know if this the right place to post this question. If it's not, my
aplogies.

Below is the structure of my tables
TABLE CARRIER_OP_CLASS
DOT_NUMBER 		CLASSIFICATION_ID
777				1
777				2
888				3
888				4

TABLE CENSUSOMC
DOT_NUMBER 		MASTER_FIELD
777				12
888				34

As you can see what I'm trying to do is get all classification_id that
belong to the dot_number field on CARRIER_OP_CLASS and append them to the
MASTER_FIELD on CENSUSOMC table where dot_number match.

For some reason my cursors are not working right, what is doing now is will
update the first record with 12, but when it goes to the next record will
add 1234.

It looks like this
dot_number 777 master_field 12
dot_number 888 master_field 1234

Any idea?
Thanks in advance
Nato


SET SERVEROUTPUT ON
DECLARE
        cursor c1 is
        SELECT DOT_NUMBER, CLASSIFICATION_ID
        FROM CARRIER_OP_CLASS;
        v1    VARCHAR2(2);

        cursor c2 is
        SELECT DOT_NUMBER, MASTER_FIELD FROM CENSUSOMC;
        v_f1    VARCHAR2(12);
        FLAG    NUMBER(1) := 1;
BEGIN
    FOR v1 IN c1 LOOP
        --FLAG := 1;    -- just added
        FOR V2 IN c2 LOOP

            IF v1.DOT_NUMBER = V2.DOT_NUMBER then
               IF FLAG = 1 THEN
                  v_f1 := v1.classification_id;
        DBMS_OUTPUT.PUT_LINE('value now is '||v_f1);
              FLAG := 2;
              ELSE
                 v_f1 := v_f1 || v1.classification_id;
                update censusomc set master_field = v_f1 where
censusomc.dot_number =
v1.dot_number;

               end if;
            end if;
         END LOOP;
    END LOOP;
DBMS_OUTPUT.PUT_LINE('value is: '||v_f1);
end;
/


---
Change your mail options at http://p2p.wrox.com/manager.asp or 
to unsubscribe send a blank email to %%email.unsub%%.


---
Change your mail options at http://p2p.wrox.com/manager.asp or 
to unsubscribe send a blank email to [email protected].