Hi everyone,
here's my simple code:
CREATE OR REPLACE TYPE num_array IS TABLE OF NUMBER;
CREATE OR REPLACE PROCEDURE proc1 (p_array IN num_array)
AS
i BINARY_INTEGER :=1;
BEGIN
FOR i IN p_array.first .. p_array.last
LOOP
DBMS_OUTPUT.put_line (p_array (i));
END LOOP;
DBMS_OUTPUT.put_line (p_array.COUNT);
END;
The compilation goes fine but i'm not able to run this procedure!
This is the code i execute:
DECLARE
P_ARRAY NUM_ARRAY := NUM_ARRAY(null);
BEGIN
P_ARRAY(1) := 15;
P_ARRAY(2) := 54;
P_ARRAY(3) := 56;
PROC1(
P_ARRAY => P_ARRAY
);
END;
And the error that raise: ORA-06533: Subscript beyond count
Where do I fail?
Many thanks,
Gaia
Your proc is fine. A nested table type is *similar* to a plsql associative array, but not exactly the same. For a nested table, you need to explicitly grow the table as required to hold elements. Hence
SQL> CREATE OR REPLACE TYPE num_array IS TABLE OF NUMBER;
2 /
Type created.
SQL>
SQL> DECLARE
2 P_ARRAY NUM_ARRAY := NUM_ARRAY(null);
3 BEGIN
4
5 P_ARRAY(1) := 15;
6 P_ARRAY(2) := 54;
7 P_ARRAY(3) := 56;
8
9 END;
10 /
DECLARE
*
ERROR at line 1:
ORA-06533: Subscript beyond count
ORA-06512: at line 6
SQL>
SQL>
SQL> DECLARE
2 P_ARRAY NUM_ARRAY := NUM_ARRAY(null);
3 BEGIN
4 P_ARRAY.extend(3); -- add three elements
5 P_ARRAY(1) := 15;
6 P_ARRAY(2) := 54;
7 P_ARRAY(3) := 56;
8
9 END;
10 /
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> DECLARE
2 P_ARRAY NUM_ARRAY := NUM_ARRAY(null);
3 BEGIN
4 P_ARRAY.extend; -- add 1 element at a time
5 P_ARRAY(1) := 15;
6 P_ARRAY.extend;
7 P_ARRAY(2) := 54;
8 P_ARRAY.extend;
9 P_ARRAY(3) := 56;
10
11 END;
12 /
PL/SQL procedure successfully completed.