Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2004
    Location
    London
    Posts
    76

    Unanswered: Procedures in Oracle...Loop in VB

    Hi there, I am trying to learn a little on writing procedures in Oracle.
    I have the following procedure, however it seems to be going in an infinitive loop or just seems to hang, there are only 500 records to insert but this seems to be taking a while...can someone help? I am very new to writing procedures and have extracted parts of other procedures to put mine together so if someone can help in what I have done wrong and the correct syntax...it will be most appreciated.

    I am trying to get a list of salespersons that exist in one database and not in the other. I then want to insert these records into a table in the database where they dont exist...I understand I have to use the LOOP functionality

    CREATE OR REPLACE PROCEDURE "SP_INSERT_SALESPERSON"

    as

    TYPE ref_cur_type IS REF CURSOR;

    ispid number(10);
    vspname varchar(20);
    v_count number;

    inv_cur ref_cur_type;

    BEGIN


    OPEN inv_cur FOR
    SELECT ve.userid, ve.firstname|| ' ' ||ve.LASTNAME
    FROM vemployee ve, sales_person sp
    where ve.USERID = sp.SP_ID (+)
    and ve.ACTIVE = 1
    and sp.SP_ID is null;

    FETCH inv_cur INTO ispid, vspname;

    LOOP

    EXIT WHEN inv_cur%NOTFOUND;

    INSERT INTO SALES_PERSON_TMP (sp_id, sp_name)
    values (ispid, vspname);

    END LOOP;

    close inv_cur;

    commit;


    end;
    /

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    You need to move the FETCH inside the loop:
    Code:
    CREATE OR REPLACE PROCEDURE "SP_INSERT_SALESPERSON" 
    as
    
    TYPE ref_cur_type IS REF CURSOR;
    
    ispid number(10);
    vspname varchar(20);
    v_count number;
    
    inv_cur ref_cur_type;
    
    BEGIN
    
    
    OPEN inv_cur FOR
    SELECT ve.userid, ve.firstname|| ' ' ||ve.LASTNAME 
    FROM vemployee ve, sales_person sp
    where ve.USERID = sp.SP_ID (+)
    and ve.ACTIVE = 1
    and sp.SP_ID is null;
    
    LOOP
      
      FETCH inv_cur INTO ispid, vspname;
    
      EXIT WHEN inv_cur%NOTFOUND;
    
      INSERT INTO SALES_PERSON_TMP (sp_id, sp_name)
      values (ispid, vspname);
    
    END LOOP;
    
    close inv_cur;
    
    commit;
    
    end;
    /
    Better still, don't use a loop at all:
    Code:
    CREATE OR REPLACE PROCEDURE "SP_INSERT_SALESPERSON" 
    as
    BEGIN
      INSERT INTO SALES_PERSON_TMP (sp_id, sp_name)
      SELECT ve.userid, ve.firstname|| ' ' ||ve.LASTNAME 
      FROM vemployee ve, sales_person sp
      where ve.USERID = sp.SP_ID (+)
      and ve.ACTIVE = 1
      and sp.SP_ID is null;
    
      COMMIT;
    end;
    /
    Actually, best practice would be to remove that COMMIT too: committing is something the caller should do when/if happy to do so.

  3. #3
    Join Date
    Feb 2004
    Location
    London
    Posts
    76
    Thanks for your help, got it working!

Posting Permissions

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