1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Query regarding Collections - Index by tables

Discussion in 'SQL PL/SQL' started by venu57, Oct 30, 2014.

  1. venu57

    venu57 Active Member

    Messages:
    23
    Likes Received:
    0
    Trophy Points:
    100
    Location:
    Bangalore
    Hi,

    I wrote a query to get first n number of records from table employees using plsql associate arrays (index by tables). After fetching the n records, displayed all n records and then deleted 5th entry from the collection.

    I tried to get the remaining n-1 records using loops. But i'm not getting n-1 records after deleting 5th record.

    if i pass n as 5, then i'll get 4 records.

    Please advice.

    For Ex:

    DECLARE
    TYPE emp_type IS TABLE OF employees%ROWTYPE
    INDEX BY BINARY_INTEGER;
    emp_det emp_type;

    l_indx NUMBER := &Enter_No_Rows_To_Retrieve;
    l_cnt NUMBER;

    BEGIN
    FOR i IN 1 .. l_indx
    LOOP
    SELECT *
    INTO emp_det (i)
    FROM employees
    WHERE sno = i;
    END LOOP;

    DBMS_OUTPUT.put_line (
    'Total no of employees recieved : ' || emp_det.COUNT);

    FOR i IN emp_det.FIRST .. emp_det.LAST
    LOOP
    DBMS_OUTPUT.put_line (i || ' ' || emp_det (i).last_name);
    END LOOP;

    -- Deleting 5h entry from the emp_det collection
    emp_det.delete (5);

    l_indx := emp_det.FIRST;
    l_cnt := emp_det.COUNT;

    DBMS_OUTPUT.put_line('After deleting 5th entry from the collection');

    LOOP
    DBMS_OUTPUT.put_line (
    emp_det (l_indx).last_name || ' ' || emp_det (l_indx).salary);
    EXIT WHEN l_indx = l_cnt;
    l_indx := emp_det.NEXT (l_indx);
    END LOOP;
    END;

    Result 1: Execute above query by giving 5 as an input

    Total no of employees retrieved : 5
    1 King
    2 Kochhar
    3 De Haan
    4 Hunold
    5 Ernst

    After deleting 5th entry from the collection

    King 24000
    Kochhar 17000
    De Haan 17000
    Hunold 9000

    Result 2: If i pass 6 then

    Note: Got an error as "ORA-06502: PL/SQL: numeric or value error: NULL index table key value" and then got the below result.

    Total no of employees retrieved : 6
    1 King
    2 Kochhar
    3 De Haan
    4 Hunold
    5 Ernst
    6 Austin

    After deleting 5th entry from the collection

    King 24000
    Kochhar 17000
    De Haan 17000
    Hunold 9000
    Austin 4800


    Result 3: if i give 7 then

    Total no of employees retrieved : 7
    1 King
    2 Kochhar
    3 De Haan
    4 Hunold
    5 Ernst
    6 Austin
    7 Pataballa

    After deleting 5th entry from the collection

    King 24000
    Kochhar 17000
    De Haan 17000
    Hunold 9000
    Austin 4800

    Note: Here in the above result, i'm not getting "Patabala" after deleting 5th entry from the collection.

    Result 4: If i give 8 then

    Total no of employees retrieved : 8
    1 King
    2 Kochhar
    3 De Haan
    4 Hunold
    5 Ernst
    6 Austin
    7 Pataballa
    8 Lorentz

    After deleting 5th entry from the collection

    King 24000
    Kochhar 17000
    De Haan 17000
    Hunold 9000
    Austin 4800
    Pataballa 4800

    Note: In the above result, again i'm not getting "Lorentz" but i'm getting "Patabala".

    Please tell me what's happening in the above scenario.

    Thanks.
     
  2. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Hi Venu,

    For collections which are filled completely, you can use FOR-LOOPs; but for sparsely filled ones - like the one you have after deleting the 5th row, better to use WHILE FOR -LOOP to handle the case or else you'll get the "... NULL index table key value...".

    In your code, you just have to replace the following part :

    Code (SQL):
    LOOP
    DBMS_OUTPUT.put_line (
    emp_det (l_indx).last_name || ' ' || emp_det (l_indx).salary);
    EXIT WHEN l_indx = l_cnt;
    l_indx := emp_det.NEXT (l_indx);
    END LOOP;
    with:

    Code (SQL):
    WHILE (l_indx IS NOT NULL)
    LOOP
       DBMS_OUTPUT.put_line (emp_det (l_indx).last_name || ' ' || emp_det (l_indx).salary);
       l_indx := emp_det.NEXT (l_indx);
    END LOOP;
     
  3. venu57

    venu57 Active Member

    Messages:
    23
    Likes Received:
    0
    Trophy Points:
    100
    Location:
    Bangalore
    Thanks Rajen.

    It's giving correct results.
     
  4. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Forgot to mention also that it was your Exit condition from the Loop which was incorrect.

    For ex. if input param=6, your count of collection records after delete is 5 and the condition 6 (l_indx) = 5 will never be true to exit from loop after we display 6th record from the collection.

    If we stick to your version, then you could do:

    Code (SQL):
    LOOP
    EXIT WHEN l_indx IS NULL;
    DBMS_OUTPUT.put_line (
    emp_det (l_indx).last_name);
    l_indx := emp_det.NEXT (l_indx);
    END LOOP;