create a collection with 2 columns and insert all the rows at once PL/SQL

Question

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

Answer 1

There is an another alternative where you can basically create a schema level object i.e OBJECT type and TABLE Type and then call it in the plsql block as shown below. Hope this helps too.

--Create object type 
CREATE OR REPLACE TYPE table_info
IS
  OBJECT
  (
    table_name  VARCHAR2(50),
    join_column VARCHAR2(50))
/

--Create table type on Object type    
CREATE OR REPLACE TYPE config_tables_type
IS
  TABLE OF table_info
  /


--PLSQL block of code
DECLARE
  config_tables config_tables_type;
BEGIN
  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')                                                        
                                                           );
END;
/

Answer 2

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;

Let me know how it works out for you.

For any further clarifications don't hesitate to ask me.

Ted.