Marco,
This is a good simple test for intellisense that doesnt come up when using nested types, but test scripts also dont come up when using nested types and you cannot see nested values during debug sessions. If a package includes nested types, you cannot generate test scripts for any of the procedures in the package (even when they dont use nested types). The intellisense, debugging visibility and test script generation features are the most power reasons to use plsqldeveloper and have helped me encourage several clients to purchase enterprise licenses for this tool. The support for nested types is definitely not keeping up with the level of the features in Oracle, and the benefits of using plsql developer go away when using nested types.
Here is an illustration showing the following issues: 1) no intellisense support during coding, 3) no support for generating test scripts-it generates a 'two-task save area overflow error even if the procedure you are testing doesnt include the nested types, and 3) no debug support during testing.
Please let me know when this is resolved as our current architecture depends heavily on nested package types and we have lost the rapid development advantages of plsql developer. Writing this code feels like we are back in notepad and sqlplus instead of the most advanced integrated development environment for Oracle PLSQL that anyone has ever used.
create or replace package nested_type_test is
type t_record is record(
value_vc varchar2(50),
value_n number,
value_d date);
type t_table is table of t_record index by pls_integer;
type t_nested_record is record(
value_scalar varchar2(50),
value_table t_table);
type t_nested_table is table of t_nested_record index by pls_integer;
procedure nested_type_intellisense(p_table in out t_nested_table);
procedure test_nested_types;
function test_non_nested
(
x in number,
y in number
) return number;
end nested_type_test;
create or replace package body nested_type_test is
procedure print_record(rnested in out nocopy t_nested_record) is
i number;
begin
dbms_output.put_line(rnested.value_scalar);
for i in 1 .. rnested.value_table.count loop
dbms_output.put_line(rnested.value_table(i).value_vc);
dbms_output.put_line(rnested.value_table(i).value_n);
dbms_output.put_line(rnested.value_table(i).value_d);
end loop;
end print_record;
procedure print_table(tnested in out nocopy t_nested_table) is
i number;
j number;
begin
for i in 1 .. tnested.count loop
for j in 1 .. tnested(i).value_table.count loop
print_record(tnested(i));
end loop;
end loop;
end print_table;
procedure set_record
(
rnested in out nocopy t_nested_record,
main_index in number
) is
i number;
begin
for i in 1 .. 5 loop
rnested.value_table(i).value_vc := 'sub record ' || i;
rnested.value_table(i).value_n := main_index * i;
rnested.value_table(i).value_d := sysdate - (i + main_index);
end loop;
end set_record;
procedure nested_type_intellisense(p_table in out t_nested_table) is
i number;
begin
for i in 1 .. 5 loop
p_table(i).value_scalar := 'record ' || i;
set_record(p_table(i),
i);
end loop;
print_table(p_table);
end nested_type_intellisense;
procedure test_nested_types is
v_table t_nested_table;
begin
nested_type_intellisense(v_table);
end test_nested_types;
function test_non_nested
(
x in number,
y in number
) return number is
begin
return x * y;
end test_non_nested;
begin
-- initialization
null;
end nested_type_test;
