declare
    l_request json_object_t;
    l_request_clob clob;
    l_dimension json_object_t;
    l_dimension_array json_array_t := json_array_t();
    l_metric    json_object_t;
    l_metric_array json_array_t := json_array_t();
    l_date_range json_object_t;
    l_date_range_array json_array_t := json_array_t();
    l_response clob;
    e_call_api_failed exception;
begin
    l_request := json_object_t();
    /* prep dimension */
    for r in (select column_value from apex_string.split(:P1_DIMENSIONS,':'))
    loop
        l_dimension := json_object_t();
        l_dimension.put('name', r.column_value);
        l_dimension_array.append(l_dimension);
    end loop;
    l_request.put('dimensions', l_dimension_array);
    /* prep metrics */
    for r in (select column_value from apex_string.split(:P1_METRICS, ':'))
    loop
        l_metric := json_object_t();
        l_metric.put('name', r.column_value);
        l_metric_array.append(l_metric);
    end loop;
    l_request.put('metrics', l_metric_array);
    /* date range */
    l_date_range := json_object_t();
    l_date_range.put('startDate', :P1_START_DATE);
    l_date_range.put('endDate',   :P1_ENDDATE);
    l_date_range.put('name', 'first_period');
    l_date_range_array.append(l_date_range);
    l_request.put('dateRanges', l_date_range_array);
    l_request_clob := l_request.to_clob();
    apex_web_service.clear_request_headers();
    apex_web_service.set_request_headers('Content-Type', 'applicaton/json', p_reset => false);
    l_response := apex_web_service.make_rest_request(
        p_url => 'https://analyticsdata.googleapis.com/v1beta/properties/' || :G_PROPERTY_ID || ':runReport'
        ,p_http_method => 'POST'
        ,p_body => l_request_clob
        ,p_credential_static_id => :G_GA4_CREDENTIAL_TOKEN
    );
    if apex_web_service.g_status_code <> 200 then
        raise e_call_api_failed;
    end if;
    insert into ga4_data(title, response) values(:P1_TITLE, l_response);
end;