I have reference data which I want to use in a PL/SQL package. This is a collection with two columns:
type table_info IS RECORD (
table_name VARCHAR2(50),
join_column VARCHAR2(50)
);
type config_tables_type is table of table_info; -- list of the config tables
I would like to add several rows at once to this collection, I tried this:
config_tables config_tables_type := config_tables_type (table_info('Commands','object_id'),
table_info('Contact_notificationcommands','command_object_id'),
table_info('Contactgroup_members','contact_object_id'),
table_info('Contactgroups','contact_object_id'),
table_info('Contactnotificationmethods','command_object_id'),
table_info('customvariables','object_id'),
table_info('Host_contactgroups','host_id'),
table_info('Host_contacts','host_id'),
table_info('Hostescalation_contactgroups','contactgroup_object_id'),
table_info('Hostescalation_contacts','contact_object_id'),
table_info('Host_parenthosts','parent_host_object_id'),
table_info('Hostdependencies','host_object_id'),
table_info('Hostdependencies','dependent_host_object_id'),
table_info('Hostescalations','host_object_id'),
table_info('Hostgroup_members','host_object_id'),
table_info('Hostgroups','hostgroup_object_id'),
table_info('Hosts','host_object_id'),
table_info('Service_contactgroups','contactgroup_object_id'),
table_info('Service_contacts','contact_object_id'),
table_info('Servicedependencies','service_object_id'),
table_info('Serviceescalation_contactgroups','contactgroup_object_id'),
table_info('Serviceescalation_contacts','contact_object_id'),
table_info('Serviceescalations','service_object_id'),
table_info('Servicegroup_members','service_object_id'),
table_info('Servicegroups','servicegroup_object_id'),
table_info('Services','service_object_id'),
table_info('Timeperiods','timeperiod_object_id')
);
But I have the following compilation error:
PLS-00222: no function with name 'TABLE_INFO' exists in this scope.
This type is declared in the package description and the initialisation of the config_tables collection is done in the package body.
Thanks
The below "hack" should do the trick!
declare
type table_info IS RECORD (
table_name VARCHAR2(50),
join_column VARCHAR2(50)
);
type config_tables_type is table of table_info;
config_tables config_tables_type;
function table_info_constructor(table_name VARCHAR2, join_column VARCHAR2) return table_info
is
t_i table_info;
begin
t_i.table_name := table_name;
t_i.join_column := join_column;
return(t_i);
end;
begin
config_tables := config_tables_type(table_info_constructor('Commands','object_id'),
table_info_constructor('Contact_notificationcommands','command_object_id'),
table_info_constructor('Contactgroup_members','contact_object_id'),
table_info_constructor('Contactgroups','contact_object_id'),
table_info_constructor('Contactnotificationmethods','command_object_id'),
table_info_constructor('customvariables','object_id'),
table_info_constructor('Host_contactgroups','host_id'),
table_info_constructor('Host_contacts','host_id'),
table_info_constructor('Hostescalation_contactgroups','contactgroup_object_id'),
table_info_constructor('Hostescalation_contacts','contact_object_id'),
table_info_constructor('Host_parenthosts','parent_host_object_id'),
table_info_constructor('Hostdependencies','host_object_id'),
table_info_constructor('Hostdependencies','dependent_host_object_id'),
table_info_constructor('Hostescalations','host_object_id'),
table_info_constructor('Hostgroup_members','host_object_id'),
table_info_constructor('Hostgroups','hostgroup_object_id'),
table_info_constructor('Hosts','host_object_id'),
table_info_constructor('Service_contactgroups','contactgroup_object_id'),
table_info_constructor('Service_contacts','contact_object_id'),
table_info_constructor('Servicedependencies','service_object_id'),
table_info_constructor('Serviceescalation_contactgroups','contactgroup_object_id'),
table_info_constructor('Serviceescalation_contacts','contact_object_id'),
table_info_constructor('Serviceescalations','service_object_id'),
table_info_constructor('Servicegroup_members','service_object_id'),
table_info_constructor('Servicegroups','servicegroup_object_id'),
table_info_constructor('Services','service_object_id'),
table_info_constructor('Timeperiods','timeperiod_object_id')
);
end;