Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2004
    Posts
    22

    Unanswered: Pl/sql Proc Using Bulk Bind

    I have developed the following procedure. I have used the bulkbinding for improving the performance.
    how can i use the loop containing FORALL statement to commit after every 50000 rows ???
    Please let me know if this is correct. My tech lead is gone for the vacation. I have doubt about DELETE FROM query in FORALL .

    Also, is this procedure recommended for the delete purpose cause it might use whole lot of memory
    as its using a cursor and a PL/SQL table as well. Transaction_header table is very very huge.

    -----------------------

    /* The following procedure accepsts two date parameters.
    procedure deletes the rows from transaction_header table
    for the trasactions occured between 'p_date1' AND 'p_date2' */

    CREATE OR REPLACE PROCEDURE del_data(p_date1 IN DATE,p_date2 IN DATE)
    AS

    -- Declare a counter
    l_count NUMBER := 0;

    -- Declaring variables for error handling
    err_num NUMBER;
    err_msg VARCHAR2(100);

    -- creating a cursor to delete the data for the specific intervals of date
    CURSOR cur_th IS
    SELECT * FROM mw.transaction_header@CRMP_LINK
    WHERE transaction_date BETWEEN p_date1 AND p_date2;

    -- creating a PL/SQL table based on cursor type
    TYPE trnhtabtyp IS TABLE OF cur_th%ROWTYPE
    INDEX BY BINARY_INTEGER;

    trnh_tab trnhtabtyp;

    BEGIN
    FORALL i IN trnh_tab.FIRST .. trnh_tab.LAST
    DELETE FROM transaction_header
    WHERE transaction_date = trnh_tab.transaction_date(i);

    l_count := l_count + 1;

    -- commit after every 50000 rows
    IF MOD(l_count/50000) = 0 THEN
    COMMIT;
    END IF;

    COMMIT;

    EXCEPTION

    WHEN OTHERS THEN
    err_num := SQLCODE;
    err_msg := SUBSTR(SQLERRM, 1, 100);
    DBMS_OUTPUT.put_line('ERRNUM= ' || err_num || ',ERRMSG= '|| err_msg );

    END;

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

    Re: Pl/sql Proc Using Bulk Bind

    You haven't populated the trnh_tab collection, so nothing will be deleted yet anyway.

    The FORALL statement runs as a single execution, so there is no opportunity to commit every 50000 rows or whatever, unless you put some kind of LOOP...END LOOP structure around it like this:
    PHP Code:
    v_start := 1;
    LOOP
      
    EXIT WHEN v_start trnh_tab.last;
      
    v_end := LEAST(v_start 50000trnh_tab.last);
      
    FORALL i in v_start..v_end
        DELETE FROM transaction_header
        WHERE transaction_date 
    trnh_tab.transaction_date(i);
        
    COMMIT;
      
    v_start := v_start 50000;
    END LOOP
    (It is not considered good practice to commit every N rows, by the way!)

    What is the point of all your exception handling? All it does is potentially prevent the user from seeing the error message (if SERVEROUT isn't on). Best to remove it all completely.

    Even with the periodic commits, you could rewrite without all the bulk processing like this:

    PHP Code:
    CREATE OR REPLACE PROCEDURE del_data(p_date1 IN DATE,p_date2 IN DATE)
    AS
    BEGIN
      LOOP
        DELETE FROM transaction_header
        WHERE transaction_date BETWEEN p_date1 
    AND p_date2
        
    AND ROWNUM <= 50000;
        EXIT 
    WHEN SQL%ROWCOUNT 0;
        
    COMMIT;
      
    END LOOP;
    END

  3. #3
    Join Date
    Apr 2004
    Posts
    22

    Thumbs up Re: Pl/sql Proc Using Bulk Bind

    YOU ARE AWESOME !!!
    thanks million for your solution.

    Take care.

    ~ Deepsi

    Originally posted by andrewst
    You haven't populated the trnh_tab collection, so nothing will be deleted yet anyway.

    The FORALL statement runs as a single execution, so there is no opportunity to commit every 50000 rows or whatever, unless you put some kind of LOOP...END LOOP structure around it like this:
    PHP Code:
    v_start := 1;
    LOOP
      
    EXIT WHEN v_start trnh_tab.last;
      
    v_end := LEAST(v_start 50000trnh_tab.last);
      
    FORALL i in v_start..v_end
        DELETE FROM transaction_header
        WHERE transaction_date 
    trnh_tab.transaction_date(i);
        
    COMMIT;
      
    v_start := v_start 50000;
    END LOOP
    (It is not considered good practice to commit every N rows, by the way!)

    What is the point of all your exception handling? All it does is potentially prevent the user from seeing the error message (if SERVEROUT isn't on). Best to remove it all completely.

    Even with the periodic commits, you could rewrite without all the bulk processing like this:

    PHP Code:
    CREATE OR REPLACE PROCEDURE del_data(p_date1 IN DATE,p_date2 IN DATE)
    AS
    BEGIN
      LOOP
        DELETE FROM transaction_header
        WHERE transaction_date BETWEEN p_date1 
    AND p_date2
        
    AND ROWNUM <= 50000;
        EXIT 
    WHEN SQL%ROWCOUNT 0;
        
    COMMIT;
      
    END LOOP;
    END

Posting Permissions

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