Problem of cursors
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Problem of cursors

  1. #1
    Join Date
    Oct 2002
    Posts
    109

    Problem of cursors

    Hi Friends
    I want to insert/update data from one table to another table which is on different server.
    There are two server having one database on each.
    One databse is clientdb another is Jcastdb.
    I have made a database link from clientdb to jcastdb say jcastdb.
    I have written a Procedure to update data from clientdb to jcastdb.But when I am executing it, getting error Invalid cursor.
    I am new to Pl/Sql can you help me to solve it.Code of procedure is below
    *****************************
    create or replace procedure updatejcastdb is
    no1 number;
    no2 number;
    var1 varchar2(70);
    var2 varchar2(70);
    cursor c1 is select * from tblcompany;
    cursor c2 is select * from tblcompany@jcastdb;
    begin
    open c2;
    open c1;
    loop
    fetch c2 into no2,var2;
    exit when c2% notfound;
    loop
    fetch c1 into no1,var1;
    exit when c1% notfound;
    if no2=no1 then
    update tblcompany@jcastdb
    set companyname= var1
    where companyid=no2;
    end if;
    end loop;
    close c1;
    commit;
    end loop;
    end updatejcastdb;


    regards
    Praveen

  2. #2
    Join Date
    Feb 2001
    Location
    UAE
    Posts
    304
    Qualify your tablename with schema name as follows:

    cursor c2 is select * from schemaName.tblcompany@jcastdb;
    Agasimani
    OCP(10g/9i/8i/8)

  3. #3
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Why do u write PL/SQL to transfer data.

    Use Import/Export utility or use COPY command or use any other tool like informatica

    Regards
    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  4. #4
    Join Date
    Nov 2002
    Posts
    39
    Can you try to use the implicit for loop?
    Instead of open c1;loop;fetch c1 into...;close c1;end loop;,
    you can use for i in c1;loop...end loop;
    Sathish

Posting Permissions

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


Click Here to Expand Forum to Full Width