I normally blog about table scans on Oracle native data but Oracle also supports a wide variety of features for scanning external tables and I need to cover these too. One of the ways I learn new things is being sent a bug report and saying to myself “Oh! I didn’t know you could do that”. So today I’m going to start with the grammar:
Alter Table <xt> Project Columns [ All | Referenced ]
Unfortunately associative arrays still require more “coding”:
we still can’t use “indices of” or “values of” in simple FOR(though they are available for FORALL for a long time), don’t have convinient iterators and even function to get all keys…
That’s why I want to show my templates for such things like iterator and keys function. You can adopt these functions and create them on schema level.
declare
type numbers is table of number;
type anumbers is table of number index by pls_integer;
a anumbers;
i pls_integer;
function iterate( idx in out nocopy pls_integer, arr in out nocopy anumbers)
return boolean
as pragma inline;
begin
if idx is null
then idx:=arr.first;
else idx:=arr.next(idx);
end if;
return idx is not null;
end;
function keys(a in out nocopy anumbers) return numbers as
res numbers:=numbers();
idx number;
pragma inline;
begin
while iterate(idx,a) loop
res.extend;
res(res.count):=idx;
end loop;
return res;
end;
begin
a(1):=10;
a(3):=30;
a(5):=50;
a(8):=80;
-- iterate:
while iterate(i,a) loop
dbms_output.put_line(a(i));
end loop;
-- keys:
for i in 1..keys(a).count loop
dbms_output.put_line(a(keys(a)(i)));
end loop;
end;