PL/SQL Tables 2

[BJCooperIT]

I have been trying to teach myself a bit about using PL/SQL tables. So, with the help of Google searches, I wrote a script to generate the days of the current month. This script correctly populates a PL/SQL table with 1 through 31 for October.

DECLARE
    TYPE        dom_tabtyp IS TABLE OF NUMBER;
    dom_tab     dom_tabtyp;
    CURSOR get_days IS
        SELECT ROWNUM
        FROM   all_objects 
        WHERE  ROWNUM BETWEEN 1 
                      AND to_char(last_day(SYSDATE),'dd');
BEGIN
    -- Populate PL/SQL table
    OPEN get_days;
    LOOP
        FETCH get_days BULK COLLECT INTO dom_tab LIMIT 10000;
        EXIT WHEN get_days%NOTFOUND;
    END LOOP;
    CLOSE get_days;
    -- Display Beginning and Ending Day with total # of rows
    dopl(dom_tab(1) ||'-'||
         dom_tab(dom_tab.COUNT) ||', rows='||
         dom_tab.COUNT);
END;
/
OUTPUT: 1-31, rows=31

What I find confusing is that if I change the LIMIT to 31, I receive a "subscript beyond count" error. Since the cursor only returns 31 rows, why does the limit need to be larger?


^{Beware of false knowledge; it is more dangerous than ignorance. ~George Bernard Shaw}
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
My website: www.EmuProductsPlus.com

 

[Thargy]

BJ,

I believe it has to do with the number of rows you're retrieving (31) versus the index on the array. Usually one starts at zero and goes to 31, but the other may start at 1 and go to 32 (or vv).

Regards

T

Grinding away at things Oracular

 

[LKBrwnDBA]

Try changing this statement:

    TYPE        dom_tabtyp IS TABLE OF NUMBER
                [b][red]INDEX BY BINARY_INTEGER[/red][/b];

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[BJCooperIT]

Changed the code above to a limit of 32 and it worked.

Added the INDEX BY BINARY_INTEGER with a limit of 31 and received a "no data found" error message. However it works with a limit of 32.

Both scenarios work without a limit. The only explanation for bulk collect LIMIT I have found is

Limiting Rows Retrieved with BULK COLLECT Oracle provides a LIMIT clause for BULK COLLECT that allows you to limit the number of rows fetched from the database. The syntax is:
FETCH cursor BULK COLLECT INTO ... [LIMIT rows];
where rows can be any literal, variable, or expression that evaluates to an integer (otherwise, Oracle will raise a VALUE_ERROR exception).

which does not address the plus 1 issue. I will settle for your explanation, thargtheslayer. Thanks for your suggestion too, LKBrwnDBA.


By the way, for anyone peeking at my code, DOPL is a function that issues a DBMS_OUTPUT.PUT_LINE of the string passed in. Save keystrokes when you can - that is my motto!

 

[karluk]

The problem with your code is that you don't need (and shouldn't have) your bulk fetch embedded in a loop. One execution of the bulk fetch populates all 31 rows in the table. Your code should look like

BEGIN
    -- Populate PL/SQL table
    OPEN get_days;
    FETCH get_days BULK COLLECT INTO dom_tab LIMIT 31;
    CLOSE get_days;
    -- Display Beginning and Ending Day with total # of rows
    dopl(dom_tab(1) ||'-'||
         dom_tab(dom_tab.COUNT) ||', rows='||
         dom_tab.COUNT);
END;

The specific reason that you are getting the "ORA-06533: Subscript beyond count" error has to do with the exit condition on the loop. With limit set to 31, get_days%NOTFOUND is not true after the first bulk fetch, so the loop continues and does another fetch, this time returning no rows because they were all returned during the first fetch. The loop then exits with an empty dom_tab, leading to the subscript error when you try to display your results.

 

[BJCooperIT]

Thanks Karluk, That is what I get for copy pasting from the example!

PS - I meant DOPL is a PROCEDURE not a FUNCTION.

^{Beware of false knowledge; it is more dangerous than ignorance. ~George Bernard Shaw}
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
My website: www.EmuProductsPlus.com