I have the following code, which seems to be working. Is there a way I can add a date, timestamp to the type definition? If so, how would reference the columns so I can INSERT data into them. For example, let's say I want to add 10 seconds to SYSDATE and SYSTIMESTAMP for each new row?
create table t ( x int );
declare
type numlist is table of number index by pls_integer;
s numlist;
begin
for i in 1 .. 100000
loop
s(i) := i;
end loop;
forall i in 1 .. 100000
insert into t values (s(i));
end;
/
Using exactly the same method as you did with numbers:
create table t ( x int, y DATE, z TIMESTAMP WITH TIME ZONE );
Then
DECLARE
TYPE numlist IS TABLE OF NUMBER;
TYPE datelist IS TABLE OF DATE;
TYPE timestamplist IS TABLE OF TIMESTAMP WITH TIME ZONE;
xs numlist := numlist();
ys datelist := datelist();
zs timestamplist := timestamplist();
n CONSTANT PLS_INTEGER := 100;
nowd CONSTANT DATE := SYSDATE;
nowts CONSTANT TIMESTAMP WITH TIME ZONE := SYSTIMESTAMP;
BEGIN
xs.EXTEND(n);
ys.EXTEND(n);
zs.EXTEND(n);
FOR i IN 1 .. n LOOP
xs(i) := i;
ys(i) := nowd + (i - 1) * INTERVAL '10' SECOND;
zs(i) := nowts + (i - 1) * INTERVAL '10' SECOND;
END LOOP;
FORALL i IN 1 .. n
INSERT INTO t (x, y, z) VALUES (xs(i), ys(i), zs(i));
END;
/
db<>fiddle here