Results 1 to 5 of 5

Thread: update counter

  1. #1
    Join Date
    Oct 2002
    Posts
    11

    Unanswered: update counter

    Hi
    I am trying to put a counter in cursor but it is giving me all the records which it read through the cursor . this is the code i wrote


    DECLARE
    CURSOR OLDCUR IS SELECT * FROM OLD;
    CURSOR NEWCUR IS SELECT * FROM NEW;
    UPD NUMBER:=0;
    INS NUMBER:=0;
    BEGIN
    FOR NEWREC IN NEWCUR loop
    if (OLD_NO = NEWREC.NEW_NO) OR (OLD_NAME != NEWREC.NEW_NAME)) then
    UPD := UPD + 1 ;
    end if;
    UPDATE OLD SET
    OLD_NAME = NEWREC.NEW_NAME,
    OLD_CITY = NEWREC.NEW_CITY,
    OLD_NO = NEWREC.NEW_NO
    WHERE OLD_NO = NEWREC.NEW_NO
    OR OLD_NAME = NEWREC.NEW_NAME;
    --OR OLD_CITY = NEWREC.NEW_CITY;
    --DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT);
    if sql%rowcount = 0 then
    --dbms_output.put_line('INSERT.');
    INSERT INTO OLD (OLD_NO,OLD_NAME,OLD_CITY)
    VALUES (NEWREC.NEW_NO,NEWREC.NEW_NAME,NEWREC.NEW_CITY);
    INS := INS + 1 ;
    end if;
    commit;
    END LOOP;
    dbms_output.put_line('UPDATES '||(UPD - INS));
    dbms_output.put_line('INSERTS '||INS);
    EXCEPTION
    WHEN OTHERS THEN
    dbms_output.put_line('Procedure Error '||sqlerrm(sqlcode));
    END;
    /


    SQL> /
    if (OLD_NO = NEWREC.NEW_NO) OR (OLD_NAME != NEWREC.NEW_NAME)) then
    *
    ERROR at line 8:
    ORA-06550: line 8, column 63:
    PLS-00103: Encountered the symbol ")" when expecting one of the following:
    * & = - + < / > at in mod not rem then <an exponent (**)>
    <> or != or ~= >= <= <> and or like between overlaps is null
    is not || is dangling year DAY_





    i need to compare new and old tables . If record is in old table and if any change in corresponding field then i need to update the oldtable.

    else i need to insert new record into old table.I want to print updates and inserts at the end of procedure. any changes please suggest.


    Thanks.
    SAN

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: update counter

    Your error message is due to unmatched parentheses (), but that is not the only thing wrong with your code. You never open cursor OLD_CUR, so there is no OLD_NO to compare, etc.

    Are you on 9i? If so you can use MERGE:

    merge into OLD
    using NEW
    on ( NEW.NEW_NO = OLD.OLD_NO )
    when matched then
    update set OLD.OLD_NAME = NEW.NEW_NAME, ...;
    when not matched then
    insert ( OLD_NO, OLD_NAME, ... )
    values ( NEW.NEW_NO, NEW.NEW_NAME, ... );

    If not on 9i, you either need to open OLD_CUR using NEW_NO and fetch to see if the row exists, or you can do this:

    FOR r IN NEW_CUR LOOP
    BEGIN
    INSERT INTO old (...) VALUES (...);
    EXCEPTION
    WHEN DUP_VAL_ON_INDEX THEN
    -- Record already exists
    UPDATE old SET ....;
    END;
    END LOOP;

  3. #3
    Join Date
    Oct 2002
    Posts
    11

    8i

    we are using 8i

    i will try this and let you know..
    Thanks.
    san

  4. #4
    Join Date
    Oct 2002
    Posts
    11

    Re: 8i

    DECLARE
    CURSOR OLDCUR IS SELECT * FROM OLD;
    CURSOR NEWCUR IS SELECT * FROM NEW;
    UPD NUMBER:=0;
    INS NUMBER:=0;
    BEGIN
    FOR NEWREC IN NEWCUR loop
    if old_no != NEWREC.NEW_NO
    and OLD_NAME != NEWREC.NEW_NAME
    and old_city != NEWREC.NEW_CITY then
    INSERT INTO OLD (OLD_NO,OLD_NAME,OLD_CITY)
    VALUES (NEWREC.NEW_NO,NEWREC.NEW_NAME,NEWREC.NEW_CITY);
    INS := INS + 1 ;
    else
    UPDATE OLD SET
    OLD_NAME = NEWREC.NEW_NAME,
    OLD_CITY = NEWREC.NEW_CITY,
    OLD_NO = NEWREC.NEW_NO;
    UPD := UPD + 1 ;
    end if;
    commit;
    END LOOP;
    dbms_output.put_line('UPDATES '||UPD );
    dbms_output.put_line('INSERTS '||INS);
    EXCEPTION
    WHEN OTHERS THEN
    dbms_output.put_line('Procedure Error '||sqlerrm(sqlcode));
    END;
    /

    DECLARE
    *
    ERROR at line 1:
    ORA-06550: line 8, column 6:
    PLS-00201: identifier 'OLD_NO' must be declared
    ORA-06550: line 8, column 1:
    PL/SQL: Statement ignored

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: 8i

    As I said in my previous answer: "Your error message is due to unmatched parentheses (), but that is not the only thing wrong with your code. You never open cursor OLD_CUR, so there is no OLD_NO to compare, etc." That is the error you are seeing now.

    You have not corrected anything in your program except the unmatching parentheses. I suggest you re-read my previous answer for suggestions on how to re-write your program.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •