Hi all!
I'm learning PL/SQL on Steve Bobrovsky's book (specified below sample is from it) and I've a question.
In the procedure of specified below program used an integer variable
currentElement to get reference to the row of nested table of %ROWTYPE datatype.
Meanwhile, the program itself uses a common FOR .. LOOP counter
i.
DECLARE
TYPE partsTable IS TABLE OF parts%ROWTYPE;
tempParts partsTable := partsTable();
CURSOR selectedParts IS
SELECT * FROM parts ORDER BY id;
currentPart selectedParts%ROWTYPE;
currentElement INTEGER;
PROCEDURE printParts(p_title IN VARCHAR2, p_collection IN partsTable) IS
BEGIN
DBMS_OUTPUT.PUT_LINE(' ');
DBMS_OUTPUT.PUT_LINE(p_title || ' elements: ' || p_collection.COUNT);
currentElement := p_collection.FIRST;
FOR i IN 1 .. p_collection.COUNT
LOOP
DBMS_OUTPUT.PUT('Element #' || currentElement || ' is ');
IF tempParts(currentElement).id IS NULL THEN DBMS_OUTPUT.PUT_LINE('an empty element.');
ELSE DBMS_OUTPUT.PUT_LINE('ID: ' || tempParts(currentElement).id || ' DESCRIPTION: ' || tempParts(currentElement).description);
END IF;
currentElement := p_collection.NEXT(currentElement);
END LOOP;
END printParts;
BEGIN
FOR currentPart IN selectedParts
LOOP
tempParts.EXTEND(2);
tempParts(tempParts.LAST) := currentPart;
END LOOP;
printParts('Densely populated', tempParts);
FOR i IN 1 .. tempParts.COUNT
LOOP
IF tempParts(i).id is NULL THEN tempParts.DELETE(i);
END IF;
END LOOP;
FOR i IN 1 .. 50
LOOP
DBMS_OUTPUT.PUT('-');
END LOOP;
printParts('Sparsely populated', tempParts);
END;
/
When I've substituted an INTEGER global variable with such FOR .. LOOP counter, an APEX have returned an error "ORA-01403: no data found".
DECLARE
TYPE partsTable IS TABLE OF parts%ROWTYPE;
tempParts partsTable := partsTable();
CURSOR selectedParts IS
SELECT * FROM parts ORDER BY id;
currentPart selectedParts%ROWTYPE;
PROCEDURE printParts(p_title IN VARCHAR2, p_collection IN partsTable) IS
BEGIN
DBMS_OUTPUT.PUT_LINE(' ');
DBMS_OUTPUT.PUT_LINE(p_title || ' elements: ' || p_collection.COUNT);
FOR i IN 1 .. p_collection.COUNT
LOOP
DBMS_OUTPUT.PUT('Element is ');
IF tempParts(i).id IS NULL THEN DBMS_OUTPUT.PUT_LINE('an empty element.');
ELSE DBMS_OUTPUT.PUT_LINE('ID: ' || tempParts(i).id || ' DESCRIPTION: ' || tempParts(i).description);
END IF;
END LOOP;
END printParts;
BEGIN
FOR currentPart IN selectedParts
LOOP
tempParts.EXTEND(2);
tempParts(tempParts.LAST) := currentPart;
END LOOP;
printParts('Densely populated', tempParts);
FOR i IN 1 .. tempParts.COUNT
LOOP
IF tempParts(i).id is NULL THEN tempParts.DELETE(i);
END IF;
END LOOP;
FOR i IN 1 .. 50
LOOP
DBMS_OUTPUT.PUT('-');
END LOOP;
printParts('Sparsely populated', tempParts);
END;
/
When I've tried to handle this code in SQL*Plus, the following picture have appeared:
Densely populated elements: 10
Element is an empty element.
Element is ID: 1 DESCRIPTION: Fax Machine
Element is an empty element.
Element is ID: 2 DESCRIPTION: Copy Machine
Element is an empty element.
Element is ID: 3 DESCRIPTION: Laptop PC
Element is an empty element.
Element is ID: 4 DESCRIPTION: Desktop PC
Element is an empty element.
Element is ID: 5 DESCRIPTION: Scanner
--------------------------------------------------
Sparsely populated elements: 5
DECLARE
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 14
ORA-06512: at line 35
What's wrong in code(or what I have not understood)? Help please!