This will give a couple more examples of using tables and also an array:

On the first one, I followed the associative array or table model that we have looked at in class. I defined a cursor to contain the names of the donors from the donor database table.

Then I set up an array table to hold the names. Here I am taking in the names and putting them in the cursor, then I am processing them and creating the table or array.

SET SERVEROUTPUT ON

DECLARE

CURSOR donor_name is

SELECT name from donor;

TYPE name_type is TABLE OF donor.name%TYPE

INDEX BY BINARY_INTEGER;

t_name_type name_type;

v_ct integer :=0;

BEGIN

FOR info in donor_name LOOP

v_ct := v_ct + 1;

t_name_type(v_ct) := info.name;

dbms_output.put_line(t_name_type(v_ct) || ' ' || v_ct);

END LOOP;

END;

/

SET SERVEROUTPUT OFF

SQL> @ tablecursor

Stephen Daniels 1

Jennifer Ames 2

Carl Hersey 3

Susan Ash 4

Nancy Taylor 5

Robert Brooks 6

PL/SQL procedure successfully completed.

Then I added a line to retrieve a specific record from the table/array:

SET SERVEROUTPUT ON

DECLARE

CURSOR donor_name is

SELECT name from donor;

TYPE name_type is TABLE OF donor.name%TYPE

INDEX BY BINARY_INTEGER;

t_name_type name_type;

v_ct integer :=0;

BEGIN

FOR info in donor_name LOOP

v_ct := v_ct + 1;

t_name_type(v_ct) := info.name;

dbms_output.put_line(t_name_type(v_ct) || ' ' || v_ct);

END LOOP;

dbms_output.put_line('the name retrieved with a pointer/index of 2 is ' || t_name_type(2));

END;

/

SET SERVEROUTPUT OFF

SQL> @ tablecursorget

Stephen Daniels 1

Jennifer Ames 2

Carl Hersey 3

Susan Ash 4

Nancy Taylor 5

Robert Brooks 6

the name retrieved with a pointer/index of 2 is Jennifer Ames

PL/SQL procedure successfully completed.

On the second one, I am using a different version of the table that does not predefine the index. The line before the type also initializes the table, without that the table is null and will not work. Notice also the extend which lets the size of the table grow as I put elements in.

SET SERVEROUTPUT ON

DECLARE

CURSOR donor_name is

SELECT name from donor;

TYPE name_type is TABLE OF donor.name%TYPE;

t_name_type name_type := name_type();

v_ct integer :=0;

BEGIN

FOR info in donor_name LOOP

v_ct := v_ct + 1;

t_name_type.EXTEND;

t_name_type(v_ct) := info.name;

dbms_output.put_line(t_name_type(v_ct) || ' ' || v_ct);

END LOOP;

END;

/

SET SERVEROUTPUT OFF

SQL> @ tablenest

Stephen Daniels 1

Jennifer Ames 2

Carl Hersey 3

Susan Ash 4

Nancy Taylor 5

Robert Brooks 6

PL/SQL procedure successfully completed.

Now I will add the code to retrieve the 5^th record after the loop is complete.

SQL> edit tablenestget

SET SERVEROUTPUT ON

DECLARE

CURSOR donor_name is

SELECT name from donor;

TYPE name_type is TABLE OF donor.name%TYPE;

t_name_type name_type := name_type();

v_ct integer :=0;

BEGIN

FOR info in donor_name LOOP

v_ct := v_ct + 1;

t_name_type.EXTEND;

t_name_type(v_ct) := info.name;

dbms_output.put_line(t_name_type(v_ct) || ' ' || v_ct);

END LOOP;

dbms_output.put_line('Retrieving 5th: ' || t_name_type(5));

END;

/

SET SERVEROUTPUT OFF

SQL> @ tablenestget

Stephen Daniels 1

Jennifer Ames 2

Carl Hersey 3

Susan Ash 4

Nancy Taylor 5

Robert Brooks 6

Retrieving 5th: Nancy Taylor

PL/SQL procedure successfully completed.

Finally, I am going to do this using varray. I am setting it up for 6 entries. Below, I will change that to 5 and will get an out of range message because there are 6 records in the donor database table.

SET SERVEROUTPUT ON

DECLARE

CURSOR donor_name is

SELECT name from donor;

TYPE name_type is VARRAY(6) OF donor.name%TYPE;

t_name_type name_type := name_type();

v_ct integer :=0;

BEGIN

FOR info in donor_name LOOP

v_ct := v_ct + 1;

t_name_type.EXTEND;

t_name_type(v_ct) := info.name;

dbms_output.put_line(t_name_type(v_ct) || ' ' || v_ct);

END LOOP;

END;

/

SET SERVEROUTPUT OFF

SQL> @ varray1

Stephen Daniels 1

Jennifer Ames 2

Carl Hersey 3

Susan Ash 4

Nancy Taylor 5

Robert Brooks 6

PL/SQL procedure successfully completed.

After I went back in and changed the varray size to 5.

SQL> @ varray1

Stephen Daniels 1

Jennifer Ames 2

Carl Hersey 3

Susan Ash 4

Nancy Taylor 5

DECLARE

*

ERROR at line 1:

ORA-06532: Subscript outside of limit

ORA-06512: at line 10

Now I am going to retrieve the 6^th record:

SET SERVEROUTPUT ON

DECLARE

CURSOR donor_name is

SELECT name from donor;

TYPE name_type is VARRAY(6) OF donor.name%TYPE;

t_name_type name_type := name_type();

v_ct integer :=0;

BEGIN

FOR info in donor_name LOOP

v_ct := v_ct + 1;

t_name_type.EXTEND;

t_name_type(v_ct) := info.name;

dbms_output.put_line(t_name_type(v_ct) || ' ' || v_ct);

END LOOP;

dbms_output.put_line('Retrieve 6th record: ' || t_name_type(v_ct));

END;

/

SET SERVEROUTPUT OFF

SQL> @ varray1get

Stephen Daniels 1

Jennifer Ames 2

Carl Hersey 3

Susan Ash 4

Nancy Taylor 5

Robert Brooks 6

Retrieve 6th record: Robert Brooks

PL/SQL procedure successfully completed.

And then the first record.

SET SERVEROUTPUT ON

DECLARE

CURSOR donor_name is

SELECT name from donor;

TYPE name_type is VARRAY(6) OF donor.name%TYPE;

t_name_type name_type := name_type();

v_ct integer :=0;

BEGIN

FOR info in donor_name LOOP

v_ct := v_ct + 1;

t_name_type.EXTEND;

t_name_type(v_ct) := info.name;

dbms_output.put_line(t_name_type(v_ct) || ' ' || v_ct);

END LOOP;

dbms_output.put_line('Retrieve 1st record: ' || t_name_type(1));

END;

/

SET SERVEROUTPUT OFF

SQL> @ varray1get1

Stephen Daniels 1

Jennifer Ames 2

Carl Hersey 3

Susan Ash 4

Nancy Taylor 5

Robert Brooks 6

Retrieve 1st record: Stephen Daniels

PL/SQL procedure successfully completed.