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!

Help In Oracle Collections

Discussion in 'SQL PL/SQL' started by venkateshguru, Dec 22, 2014.

  1. venkateshguru

    venkateshguru Active Member

    Messages:
    30
    Likes Received:
    1
    Trophy Points:
    110
    Program:

    DECLARE
    TYPE TEST IS TABLE OF NUMBER; --Nested table

    TESTX TEST := TEST ();

    TYPE TEST1 IS TABLE OF NUMBER
    INDEX BY PLS_INTEGER; --Associative array or Index By Table

    TESTY TEST1;
    BEGIN
    FOR I IN 1 .. 100 --Nested table
    LOOP
    TESTX.EXTEND (I);
    TESTX (I) := I;
    END LOOP;

    DBMS_OUTPUT.PUT_LINE ('Nested Table Count Before 1 delete-' || TESTX.COUNT);
    TESTX.Delete (5);
    DBMS_OUTPUT.PUT_LINE ('Nested Table Count After 1 delete-' || TESTX.COUNT);

    FOR I IN 1 .. 100 --Associative array or Index By Table
    LOOP
    TESTY (I) := I;
    END LOOP;

    DBMS_OUTPUT.PUT_LINE ('Nested Table Count Before 1 delete-' || TESTY.COUNT);
    TESTY.Delete (5);
    DBMS_OUTPUT.PUT_LINE ('Associative array Count After 1 delete-' || TESTY.COUNT);

    END;

    Output :
    Nested Table Count After 1 delete-5050
    Nested Table Count After 1 delete-5049
    Nested Table Count After 1 delete-100
    Associative array Count After 1 delete-99

    As per my expectation it should be

    Nested Table Count After 1 delete-100
    Nested Table Count After 1 delete-99
    Nested Table Count After 1 delete-100
    Associative array Count After 1 delete-99

    Can somebody help me out what is the issue?

    Thanks,
    Venkatesh g
     
  2. sambuduk

    sambuduk Forum Advisor

    Messages:
    242
    Likes Received:
    73
    Trophy Points:
    455
    Location:
    Hyderabad , Telangana
    Hi Venkatesh,

    Use TESTX.EXTEND() instead of TESTX.EXTEND (I);

    Regards
    Sambasiva Reddy.K;
     
    venkateshguru likes this.
  3. venkateshguru

    venkateshguru Active Member

    Messages:
    30
    Likes Received:
    1
    Trophy Points:
    110
    Yes its working. What is the reason and why its not working when i give the extend(i).

    Thanks,
    Venkatesh G
     
  4. sambuduk

    sambuduk Forum Advisor

    Messages:
    242
    Likes Received:
    73
    Trophy Points:
    455
    Location:
    Hyderabad , Telangana
    EXTEND - Appends a single null element to the collection.
    EXTEND(n) - Appends n null elements to the collection.
     
    venkateshguru likes this.
  5. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    As Samba mentioned, the call of EXTEND makes provision or reserves for only 1 number while EXTEND(i) does a bulk allocation of "i" number.

    In your loop (from 1 .. 100), when you sum up all the numbers from 1 to 100 (i.e. 1+2+3...100) you end up reserving 5050 "rooms" for numbers and that's why your count gives 5050.
     
    venkateshguru likes this.
  6. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
  7. venkateshguru

    venkateshguru Active Member

    Messages:
    30
    Likes Received:
    1
    Trophy Points:
    110
    Thanks to all.