mailchimp_pkg.pkb

create or replace package body mailchimp_pkg as 
    
    gc_scope_prefix constant varchar2(31)  := lower($$plsql_unit) || '.'; ----------------- necessary for the logger implementation
    g_url_prefix    constant varchar2(100) := get_env_var (p_var_name => 'url_prefix'); --- your Mailchimp url prefix, in the format 'https://us[XX].api.mailchimp.com/3.0/'
    g_password      constant varchar2(50)  := get_env_var (p_var_name => 'api_key'); ------ this is your API Key (very sensitive - keep to yourself)
    g_wallet_path   constant varchar2(100) := get_env_var (p_var_name => 'wallet_path'); --  the path on to your Oracle Wallet, syntax 'file:[path to your Oracle Wallet]'
    g_https_host    constant varchar2(100) := get_env_var (p_var_name => 'https_host'); --- necessary if you have an Oracle 12.2 database or higher (see instructions)
    g_address1      constant varchar2(500) := get_env_var (p_var_name => 'address1'); ----- the CAN SPAM act requires that you specify the organization's address
    g_city          constant varchar2(500) := get_env_var (p_var_name => 'city'); --------- the CAN SPAM act requires that you specify the organization's address
    g_state         constant varchar2(500) := get_env_var (p_var_name => 'state'); -------- the CAN SPAM act requires that you specify the organization's address
    g_zip           constant varchar2(500) := get_env_var (p_var_name => 'zip'); ---------- the CAN SPAM act requires that you specify the organization's address
    g_county        constant varchar2(500) := get_env_var (p_var_name => 'country'); ------ the CAN SPAM act requires that you specify the organization's address
    g_company_name  constant varchar2(100) := get_env_var (p_var_name => 'company'); ------ whatever your organization is called
    g_reply_to      constant varchar2(100) := get_env_var (p_var_name => 'email'); -------- the email that you've authenticated with Mailchimp
    g_from_name     constant varchar2(100) := get_env_var (p_var_name => 'from_name'); ---- the name your emails will appear to be from
    g_username      constant varchar2(50)  := 'admin'; ------------------------------------ arbitrary - can be anything

-- see package specs
function create_list (p_list_name           in varchar2, 
                      p_permission_reminder in varchar2) 
                      return varchar2
is 
l_scope logger_logs.scope%type := gc_scope_prefix || 'create_list';
l_params logger.tab_param;
l_body         varchar2(1000);
l_response     clob;
l_confirmation varchar2(1000);
l_list_id      varchar2(50);
begin
  logger.append_param(l_params, 'p_list_name', p_list_name);
  logger.append_param(l_params, 'p_permission_reminder', p_permission_reminder);
  logger.log('START', l_scope, null, l_params);

    l_body := '{"name":"'||p_list_name||'","contact":{"company":"'||g_company_name||'","address1":"'||g_address1||'","city":"'||g_city||'","state":"'||g_state||'","zip":"'||g_zip||'","country":"'||g_county||'","phone":""}';
    l_body := l_body||',"permission_reminder":"'||p_permission_reminder||'","campaign_defaults":{"from_name":"'||g_from_name||'","from_email":"'||g_reply_to||'","subject":"","language":"en"},"email_type_option":true}';
    
    logger.log('l_body :', l_scope, l_body);

    l_response := apex_web_service.make_rest_request(
          p_url         => g_url_prefix||'/lists/'
        , p_http_method => 'POST'
        , p_username    => g_username
        , p_password    => g_password
        , p_body        => l_body
        , p_wallet_path => g_wallet_path
        , p_https_host  => g_https_host
    );

    l_list_id := json_value(l_response, '$.id');

    logger.log('list id :'    , l_scope, l_list_id);
    logger.log('l_response : ', l_scope, l_response);

  logger.log('END', l_scope);
  return l_list_id;
exception when others then 
    logger.log_error('Unhandled Exception', l_scope, null, l_params); 
    raise;
end create_list;

-- see package specs
procedure add_subscriber (  p_list_id in varchar2,
                            p_email   in varchar2,
                            p_fname   in varchar2,
                            p_lname   in varchar2,
                            p_success out boolean)
is 
l_scope        logger_logs.scope%type := gc_scope_prefix || 'add_subscriber';
l_params       logger.tab_param;
l_body         varchar2(1000);
l_response     clob;
l_confirmation varchar2(1000);
begin
    logger.append_param(l_params, 'p_list_id', p_list_id);
    logger.append_param(l_params, 'p_email', p_email);
    logger.append_param(l_params, 'p_fname', p_fname);
    logger.append_param(l_params, 'p_lname', p_lname);
    logger.log('START', l_scope, null, l_params);

    l_body := '{"email_address":"'||p_email||'","status":"subscribed","merge_fields":{"FNAME":"'||p_fname||'","LNAME":"'||p_lname||'"}}';

    l_response := apex_web_service.make_rest_request(
                  p_url         => g_url_prefix||'/lists/'||p_list_id||'/members/'
                , p_http_method => 'POST'
                , p_username    => g_username
                , p_password    => g_password
                , p_body        => l_body
                , p_wallet_path => g_wallet_path
                , p_https_host  => g_https_host
            );

    l_confirmation := json_value(l_response, '$.status');
    

    if l_confirmation = 'subscribed' then
        p_success := true;
        logger.log('Success! :', l_scope, l_confirmation);
    else 
        p_success := false;
        logger.log('Failure :', l_scope, l_response);
    end if;

    logger.log('END', l_scope);
exception when others then 
    logger.log_error('Unhandled Exception', l_scope, null, l_params); 
    raise;
end add_subscriber;

-- see package specs
procedure remove_subscriber ( p_list_id in varchar2, 
                              p_email   in varchar2, 
                              p_success out boolean)
is 
l_scope           logger_logs.scope%type := gc_scope_prefix || 'remove_subscriber';
l_params          logger.tab_param;
l_response        clob;
l_subscriber_hash varchar2(200);
l_confirmation    varchar2(1000);
l_count           number;
begin
    logger.append_param(l_params, 'p_list_id', p_list_id);
    logger.append_param(l_params, 'p_email', p_email);
    logger.log('START', l_scope, null, l_params);

    select standard_hash(p_email, 'MD5')
    into l_subscriber_hash
    from dual;

    l_response := apex_web_service.make_rest_request(
                  p_url         => g_url_prefix||'/lists/'||p_list_id||'/members/'||l_subscriber_hash
                , p_http_method => 'DELETE'
                , p_username    => g_username
                , p_password    => g_password
                , p_wallet_path => g_wallet_path
                , p_https_host  => g_https_host
            );

    SELECT count(*)
        into l_count
        from table(mailchimp_pkg.get_list_of_subscribers ( p_list_id => p_list_id))
        where email_address = p_email;
    
    if l_count = 0 then
        p_success := true;
    else 
        p_success := false;
    end if;
    
    logger.log('END', l_scope);
exception when others then 
    logger.log_error('Unhandled Exception', l_scope, null, l_params); 
    raise;
end remove_subscriber;

-- see package specs
function get_list_of_subscribers ( p_list_id in varchar2) -- the email list_id
                                   RETURN subscriber_typ_set PIPELINED
is 
TYPE subscriber_table IS table of subscriber_typ_TBL%ROWTYPE INDEX BY PLS_INTEGER;
l_scope         logger_logs.scope%type := gc_scope_prefix || 'get_list_of_subscribers';
l_params        logger.tab_param;
l_subscriber_set subscriber_table;
l_subscribers    subscriber_typ_set := subscriber_typ_set();
l_response       clob;
l_total_items    integer;
l_counter        integer;
begin
  logger.append_param(l_params, 'p_list_id', p_list_id);
  logger.log('START', l_scope, null, l_params);
  
  l_response:= apex_web_service.make_rest_request(
                  p_url         => g_url_prefix||'/lists/'||p_list_id||'/members?offset=0&count=10000'
                , p_http_method => 'GET'
                , p_username    => g_username
                , p_password    => g_password
                , p_wallet_path => g_wallet_path
                , p_https_host  => g_https_host
            );

  l_total_items := json_value(l_response, '$.total_items');
  logger.log('l_total_items :', l_scope, to_char(l_total_items));

  for i in 1..l_total_items 
  loop
    l_counter := i -1;
    l_subscriber_set(i).email_address := json_value(l_response, '$.members['||l_counter||'].email_address');
    l_subscriber_set(i).first_name    := json_value(l_response, '$.members['||l_counter||'].merge_fields.FNAME');
    l_subscriber_set(i).last_name     := json_value(l_response, '$.members['||l_counter||'].merge_fields.LNAME');
    l_subscriber_set(i).status        := json_value(l_response, '$.members['||l_counter||'].status');
    
  end loop;

  for i in 1..l_total_items 
  loop
  PIPE ROW (subscriber_typ(l_subscriber_set(i).email_address,
                           l_subscriber_set(i).first_name,
                           l_subscriber_set(i).last_name,
                           l_subscriber_set(i).status
                          )
           );
  end loop;

  logger.log('END', l_scope);
exception when others then 
    logger.log_error('Unhandled Exception', l_scope, null, l_params); 
    raise;
end get_list_of_subscribers;

--see package specs
function get_list_of_merge_fields (p_list_id in varchar2)
                                   return merge_field_typ_set PIPELINED
is 
TYPE merge_field_table IS table of merge_field_typ_tbl%ROWTYPE INDEX BY PLS_INTEGER;
l_scope logger_logs.scope%type := gc_scope_prefix || 'get_list_of_merge_fields';
l_params logger.tab_param;
l_merge_field_set merge_field_table;
l_merge_fields    merge_field_typ_set := merge_field_typ_set();
l_response       clob;
l_total_items    integer;
l_counter        integer;
begin
    logger.append_param(l_params, 'p_list_id', p_list_id);
    logger.log('START', l_scope, null, l_params);

    l_response:= apex_web_service.make_rest_request(
                  p_url         => g_url_prefix||'/lists/'||p_list_id||'/merge-fields/'
                , p_http_method => 'GET'
                , p_username    => g_username
                , p_password    => g_password
                , p_wallet_path => g_wallet_path
                , p_https_host  => g_https_host
            );

    l_total_items := json_value(l_response, '$.total_items');
    logger.log('l_total_items :', l_scope, to_char(l_total_items));

    for i in 1..l_total_items 
    loop
        l_counter := i -1;
        l_merge_field_set(i).merge_id      := json_value(l_response, '$.merge_fields['||l_counter||'].merge_id');
        l_merge_field_set(i).tag           := json_value(l_response, '$.merge_fields['||l_counter||'].tag');
        l_merge_field_set(i).name          := json_value(l_response, '$.merge_fields['||l_counter||'].name');
        l_merge_field_set(i).default_value := json_value(l_response, '$.merge_fields['||l_counter||'].default_value');
    end loop;

    for i in 1..l_total_items 
    loop
    PIPE ROW (merge_field_typ(l_merge_field_set(i).merge_id,
                              l_merge_field_set(i).tag,
                              l_merge_field_set(i).name,
                              l_merge_field_set(i).default_value
                              )
             );
    end loop;
    logger.log('END', l_scope);
exception when others then 
    logger.log_error('Unhandled Exception', l_scope, null, l_params); 
    raise;
end get_list_of_merge_fields;

-- see package specs
procedure create_merge_field(p_list_id          in varchar2,
                             p_merge_field_tag  in varchar2,
                             p_merge_field_name in varchar2,
                             p_merge_id         out integer,
                             p_tag              out varchar2)
is 
l_scope     logger_logs.scope%type := gc_scope_prefix || 'create_merge_field';
l_params    logger.tab_param;
l_body      varchar2(1000);
l_response  varchar2(2000);
l_tag_count number;
    procedure check_if_already_there is 
    begin
        SELECT     merge_id,   tag
            INTO p_merge_id, p_tag
            FROM TABLE(mailchimp_pkg.get_list_of_merge_fields(p_list_id => p_list_id))
            where tag = p_merge_field_tag;
            logger.log('The tag already exists in this list.', l_scope, null, l_params);
            logger.log('p_merge_id :', l_scope, to_char(p_merge_id));
            logger.log('p_tag :'     , l_scope, p_tag);
        exception when no_data_found then
            logger.log('The tag does not exist yet in this list', l_scope, null, l_params);
    end check_if_already_there;
begin 
    logger.append_param(l_params, 'p_list_id', p_list_id);
    logger.append_param(l_params, 'p_merge_field_tag', p_merge_field_tag);
    logger.append_param(l_params, 'p_merge_field_name', p_merge_field_name);
    logger.log('START', l_scope, null, l_params);

    if length(p_merge_field_tag) > 10 then
        logger.log_error('p_merge_field_tag cannot be more than 10 characters.', l_scope, null, l_params);
        raise_application_error(-20456, 'Merge field too long');
    end if;

    check_if_already_there;
    if p_merge_id is not null then return; end if;

    l_body := '{"tag":"'||p_merge_field_tag||'", "name":"'||p_merge_field_name||'", "type":"text"}';

    l_response := apex_web_service.make_rest_request(
          p_url         => g_url_prefix||'lists/'||p_list_id||'/merge-fields/'
        , p_http_method => 'POST'
        , p_username    => g_username 
        , p_password    => g_password
        , p_body        => l_body
        , p_wallet_path => g_wallet_path
        , p_https_host  => g_https_host
    );
    
    p_merge_id := json_value(l_response, '$.merge_id');
    p_tag      := json_value(l_response, '$.tag');

    if p_merge_id is null then
        logger.log_error('Unhandled Error :', l_scope, l_response);
    else 
        logger.log('p_merge_id :', l_scope, to_char(p_merge_id));
        logger.log('p_tag :'     , l_scope, p_tag);
    end if;

    logger.log('END', l_scope);
exception when others then 
    logger.log_error('Unhandled Exception', l_scope, null, l_params); 
    raise;
end create_merge_field;

-- see package specs
procedure update_merge_field (p_list_id         in varchar2,
                              p_merge_field_tag in varchar2,
                              p_merge_value     in varchar2,
                              p_success         out boolean)
is 
l_scope        logger_logs.scope%type := gc_scope_prefix || 'update_merge_field';
l_params       logger.tab_param;
l_merge_id     integer;
l_body         varchar2(1000);
l_response     clob;
l_confirmation varchar2(1000);
begin
  logger.append_param(l_params, 'p_list_id', p_list_id);
  logger.append_param(l_params, 'p_merge_field_tag', p_merge_field_tag);
  logger.append_param(l_params, 'p_merge_value', p_merge_value);
  logger.log('START', l_scope, null, l_params);
    
    begin
      select merge_id
        into l_merge_id 
        from table(mailchimp_pkg.get_list_of_merge_fields(p_list_id => p_list_id))
        where tag = p_merge_field_tag;
    exception when no_data_found then
      logger.log_error('Tag does not exist in this list. It must be created 1st.', l_scope, null, l_params); 
      raise;
    end;

    l_body := '{"name":"'||p_merge_field_tag||'", "type":"text", "default_value": "'||p_merge_value||'", "options": {"size": 2000}}';

        l_response := apex_web_service.make_rest_request(
                p_url         => g_url_prefix||'/lists/'||p_list_id||'/merge-fields/'||l_merge_id
                , p_http_method => 'PATCH'
                , p_username    => g_username
                , p_password    => g_password
                , p_body        => l_body
                , p_wallet_path => g_wallet_path
                , p_https_host  => g_https_host
            );

    l_confirmation := json_value(l_response, '$.default_value');
    

    if l_confirmation = p_merge_value then
        p_success := true;
        logger.log('Successfully updated merge field to :', l_scope, l_confirmation);
    else 
        p_success := false;
        logger.log('Failure :', l_scope, l_confirmation);
    end if;


  logger.log('END', l_scope);
  exception when others then 
    logger.log_error('Unhandled Exception', l_scope, null, l_params); 
    raise;
end update_merge_field;

-- see package specs
procedure create_template ( p_template_name in  varchar2, 
                            p_html          in  clob, 
                            p_template_id   out integer)
is 
l_scope    logger_logs.scope%type := gc_scope_prefix || 'create_template';
l_params   logger.tab_param;
l_body     varchar2(1000);
l_response clob;
begin
  logger.append_param(l_params, 'p_template_name', p_template_name);
  logger.log('START', l_scope, null, l_params);

    l_body := '{"name":"'||p_template_name||'","html":"'||p_html||'"}';    
    l_response := apex_web_service.make_rest_request(
                  p_url         => g_url_prefix||'/templates'
                , p_http_method => 'POST'
                , p_username    => g_username
                , p_password    => g_password
                , p_body        => l_body
                , p_wallet_path => g_wallet_path
                , p_https_host  => g_https_host
            );
    
    p_template_id := json_value(l_response, '$.id');
    logger.log('p_template_id :', l_scope, to_char(p_template_id));

  logger.log('END', l_scope);
exception when others then 
    logger.log_error('Unhandled Exception', l_scope, null, l_params); 
    raise;
end create_template;

-- see package specs
procedure update_template ( p_template_id in integer,
                            p_html        in clob,
                            p_success     out boolean)
is
l_scope       logger_logs.scope%type := gc_scope_prefix || 'update_template';
l_params      logger.tab_param;
l_body        clob;
l_response    clob;
l_template_id integer;
begin
    logger.append_param(l_params, 'p_template_id', p_template_id);
    logger.log('START', l_scope, null, l_params);
    
    l_body := '{"html":"'||p_html||'"}';

    l_response := apex_web_service.make_rest_request(
                  p_url         => g_url_prefix||'/templates/'||p_template_id
                , p_http_method => 'PATCH'
                , p_username    => g_username
                , p_password    => g_password
                , p_body        => l_body
                , p_wallet_path => g_wallet_path
                , p_https_host  => g_https_host
            );

    l_template_id := json_value(l_response, '$.id');

    if l_template_id = p_template_id then
        p_success := true;
    else
        p_success := false;
    end if;

    logger.log('END', l_scope);
exception when others then 
    logger.log_error('Unhandled Exception', l_scope, null, l_params); 
    raise;
end update_template;

-- see package specs
procedure create_campaign ( p_list_id      in varchar2,
                            p_subject_line in varchar2,
                            p_title        in varchar2,
                            p_template_id  in number,
                            p_campaign_id  out varchar2,
                            p_send_url     out varchar2)
is
l_scope       logger_logs.scope%type := gc_scope_prefix || 'create_campaign'; 
l_params      logger.tab_param;
l_body        varchar2(1000);
l_response    clob;
l_campaign_id varchar2(100);
begin
    logger.append_param(l_params, 'p_list_id', p_list_id);
    logger.append_param(l_params, 'p_subject_line', p_subject_line);
    logger.append_param(l_params, 'p_title', p_title);
    logger.append_param(l_params, 'p_template_id', p_template_id);
    logger.append_param(l_params, 'g_reply_to', g_reply_to);
    logger.append_param(l_params, 'g_from_name', g_from_name);
    logger.append_param(l_params, 'p_send_url', p_send_url);
    logger.log('START', l_scope, null, l_params);
    l_body         := '{"recipients":{"list_id":"'||p_list_id||'"},"type":"regular","settings":{"subject_line":"'||p_subject_line||'", "title": "'||p_title||'","template_id": '||p_template_id||',"reply_to":"'||g_reply_to||'","from_name":"'||g_from_name||'"}}';

    l_response := apex_web_service.make_rest_request(
                      p_url         => g_url_prefix||'/campaigns'
                    , p_http_method => 'POST'
                    , p_username    => g_username
                    , p_password    => g_password
                    , p_body        => l_body
                    , p_wallet_path => g_wallet_path
                    , p_https_host  => g_https_host
                );

    l_campaign_id := json_value(l_response, '$.id');
    logger.log('l_campaign_id :', l_scope, l_campaign_id);
    p_campaign_id := l_campaign_id;
    p_send_url := json_value(l_response, '$."_links"[3].href');
    logger.log('p_send_url :', l_scope, p_send_url);

    logger.log('END', l_scope);
exception when others then 
    logger.log_error('Unhandled Exception', l_scope, null, l_params);
    raise;
end create_campaign;

--see package specs
procedure send_campaign_checklist (p_campaign_id in varchar2, --- the unique id of the campaign
                                   p_ready       out boolean)
is 
l_scope    logger_logs.scope%type := gc_scope_prefix || 'send_campaign_checklist';
l_params   logger.tab_param;
l_response clob;
l_ready    varchar2(10);
begin
  logger.append_param(l_params, 'p_campaign_id', p_campaign_id);
  logger.log('START', l_scope, null, l_params);

  l_response:= apex_web_service.make_rest_request(
                  p_url         => g_url_prefix||'/campaigns/'||p_campaign_id||'/send-checklist'
                , p_http_method => 'GET'
                , p_username    => g_username
                , p_password    => g_password
                , p_wallet_path => g_wallet_path
                , p_https_host  => g_https_host
            );
   l_ready := json_value(l_response, '$.is_ready');
   if l_ready = 'true' then
      logger.log('The campaign is ready to send.', l_scope, null, l_params);
      p_ready := true;
    else 
      logger.log('The campaign is not ready to send.', l_scope, l_response);
      p_ready := false;
    end if;

  logger.log('END', l_scope);
exception when others then 
  logger.log_error('Unhandled Exception', l_scope, null, l_params); 
  raise;
end send_campaign_checklist;

-- see package specs
procedure send_campaign (p_send_url in varchar2,
                         p_success  out boolean)
is 
l_scope    logger_logs.scope%type := gc_scope_prefix || 'send_campaign';
l_params   logger.tab_param;
l_response clob;
begin
    logger.append_param(l_params, 'p_send_url', p_send_url);
    logger.log('START', l_scope, null, l_params);

    l_response := apex_web_service.make_rest_request(
                          p_url         => p_send_url
                        , p_http_method => 'POST'
                        , p_username    => g_username
                        , p_password    => g_password
                        , p_wallet_path => g_wallet_path
                        , p_https_host  => g_https_host
                    );
    
    if length(l_response) = 0 or l_response is null then
        p_success := true;
        logger.log('Success!', l_scope, null, l_params);
    else 
        p_success := false;
        logger.log('l_response :', l_scope, l_response);
    end if;

    logger.log('END', l_scope);
exception when others then 
    logger.log_error('Unhandled Exception', l_scope, null, l_params); 
    raise;
end send_campaign;

function get_campaign_history return campaign_history_typ_set PIPELINED
is 
TYPE campaign_table IS table of campaign_history_typ_tbl%ROWTYPE INDEX BY PLS_INTEGER;
l_scope logger_logs.scope%type := gc_scope_prefix || 'get_campaign_history';
l_params logger.tab_param;
l_campaign_set   campaign_table;
l_campaigns      campaign_history_typ_set := campaign_history_typ_set();
l_response       clob;
l_total_items    integer;
l_counter        integer;
l_send_time      varchar2(100);
begin
    logger.log('START', l_scope, null, l_params);

    l_response:= apex_web_service.make_rest_request(
                  p_url         => g_url_prefix||'/campaigns/'
                , p_http_method => 'GET'
                , p_username    => g_username
                , p_password    => g_password
                , p_wallet_path => g_wallet_path
                , p_https_host  => g_https_host
            );

    l_total_items := json_value(l_response, '$.total_items');
    logger.log('l_total_items :', l_scope, to_char(l_total_items));

    for i in 1..l_total_items 
  loop
    l_counter := i -1;
    l_campaign_set(i).campaign_id       := json_value(l_response, '$.campaigns['||l_counter||'].id');
    l_campaign_set(i).emails_sent       := json_value(l_response, '$.campaigns['||l_counter||'].emails_sent');
    l_send_time                         := json_value(l_response, '$.campaigns['||l_counter||'].send_time');
    l_campaign_set(i).send_time         := to_date(substr(l_send_time,1,instr(l_send_time,'+')-1), 'YYYY-MM-DD"T"HH24:MI:SS');
    l_campaign_set(i).recipient_list_id := json_value(l_response, '$.campaigns['||l_counter||'].recipients.list_id');
    l_campaign_set(i).template_id       := json_value(l_response, '$.campaigns['||l_counter||'].settings.template_id');
    l_campaign_set(i).subject_line      := json_value(l_response, '$.campaigns['||l_counter||'].settings.subject_line');
    l_campaign_set(i).from_name         := json_value(l_response, '$.campaigns['||l_counter||'].settings.from_name');
    l_campaign_set(i).opens             := json_value(l_response, '$.campaigns['||l_counter||'].report_summary.opens');
    l_campaign_set(i).unique_opens      := json_value(l_response, '$.campaigns['||l_counter||'].report_summary.unique_opens');
    l_campaign_set(i).open_rate         := json_value(l_response, '$.campaigns['||l_counter||'].report_summary.open_rate');
    l_campaign_set(i).clicks            := json_value(l_response, '$.campaigns['||l_counter||'].report_summary.clicks');
    l_campaign_set(i).cancel_send       := json_value(l_response, '$.campaigns['||l_counter||']."_links"[4].href');
  end loop;

  for i in 1..l_total_items 
  loop
  PIPE ROW (campaign_history_typ(l_campaign_set(i).campaign_id,
                                 l_campaign_set(i).emails_sent,
                                 l_campaign_set(i).send_time,
                                 l_campaign_set(i).recipient_list_id,
                                 l_campaign_set(i).template_id,
                                 l_campaign_set(i).subject_line,
                                 l_campaign_set(i).from_name,
                                 l_campaign_set(i).opens,
                                 l_campaign_set(i).unique_opens,
                                 l_campaign_set(i).open_rate,
                                 l_campaign_set(i).clicks,
                                 l_campaign_set(i).cancel_send
                          )
           );
  end loop;

    logger.log('END', l_scope);
exception when others then 
    logger.log_error('Unhandled Exception', l_scope, null, l_params); 
    raise;
end get_campaign_history;

-- see package specs
function get_env_var (p_var_name in varchar2) return varchar2
is 
l_scope   logger_logs.scope%type := gc_scope_prefix || 'get_env_var';
l_params  logger.tab_param;
l_var_val varchar2(200);
begin 
    logger.append_param(l_params, 'p_var_name', p_var_name);
    logger.log('START', l_scope, null, l_params);

    select variable_value
        into l_var_val
        from mailchimp_env_var
        where upper(variable_name) = upper(p_var_name);

    logger.log('END', l_scope);
    return l_var_val;
exception 
    when no_data_found then
        logger.log_info('Variable name not recognized.', l_scope, null, l_params);
        return null;
    when others then 
        logger.log_error('Unhandled Exception', l_scope, null, l_params); 
        raise;
end get_env_var;

end mailchimp_pkg;
/