Mega Code Archive
Storing and retreiving a nested table with non-sequential keys
<![CDATA[
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>
]]>