Oracle PL/SQL/PL SQL/Table of Date

Storing and retreiving a nested table with non-sequential keys

 
SQL>
SQL> CREATE OR REPLACE TYPE DateTab AS
  2    TABLE OF DATE;
  3  /
Type created.
SQL> CREATE TABLE MyDate (
  2    key        VARCHAR2(100) PRIMARY KEY,
  3    date_list  DateTab)
  4    NESTED TABLE date_list STORE AS dates_tab;
Table created.
SQL>
SQL> create or replace PROCEDURE Print(p_Dates IN DateTab) IS
  2      v_Index BINARY_INTEGER := p_Dates.FIRST;
  3  BEGIN
  4      WHILE v_Index <= p_Dates.LAST LOOP
  5        DBMS_OUTPUT.PUT("  " || v_Index || ": ");
  6        DBMS_OUTPUT.PUT_LINE(TO_CHAR(p_Dates(v_Index), "DD-MON-YYYY"));
  7        v_Index := p_Dates.NEXT(v_Index);
  8      END LOOP;
  9  END Print;
 10  /
Procedure created.
SQL> show error
No errors.
SQL>
SQL> DECLARE
  2    v_Dates DateTab := DateTab(TO_DATE("04-JUL-1776", "DD-MON-YYYY"),
  3                               TO_DATE("12-APR-1861", "DD-MON-YYYY"),
  4                               TO_DATE("05-JUN-1968", "DD-MON-YYYY"),
  5                               TO_DATE("26-JAN-1986", "DD-MON-YYYY"),
  6                               TO_DATE("01-JAN-2001", "DD-MON-YYYY"));
  7
  8  BEGIN
  9    v_Dates.DELETE(2);
 10
 11    DBMS_OUTPUT.PUT_LINE("Initial value of the table:");
 12    Print(v_Dates);
 13
 14    INSERT INTO MyDate (key, date_list) VALUES ("Dates in American History", v_Dates);
 15
 16    SELECT date_list INTO v_Dates FROM MyDate WHERE key = "Dates in American History";
 17
 18    DBMS_OUTPUT.PUT_LINE("Table after INSERT and SELECT:");
 19    Print(v_Dates);
 20  END;
 21  /
Initial value of the table:
1: 04-JUL-1776
3: 05-JUN-1968
4: 26-JAN-1986
5: 01-JAN-2001
Table after INSERT and SELECT:
1: 04-JUL-1776
2: 05-JUN-1968
3: 26-JAN-1986
4: 01-JAN-2001
PL/SQL procedure successfully completed.
SQL>
SQL> drop table MyDate;
Table dropped.
SQL>
SQL>
SQL>