Hi, I was trying to use JSON_TABLE to capture info from REST Request into a table. Would this line of code work? I can't test it myself yet.
Any feedback on this code? Headers are commented out cause I don't have authentication info. I used Select * because I'm fetching all the info defined below in COLUMNS section
declare
l_clob CLOB;
BEGIN
--apex_web_service.g_request_headers(1).name := 'x-rapidapi-key';
--apex_web_service.g_request_headers(1).value := '';
l_clob := apex_web_service.make_rest_request(
p_url => 'https://ge26fe17f66d208-gswobow3a6jm5vc0.adb.us-sanjose-1.oraclecloudapps.com/ords/newspace/countries/country/209',
p_http_method => 'GET');
return 'SELECT *
FROM JSON_TABLE(l_clob, "$.items[*]"
COLUMNS (country_id NUMBER PATH "$.country_id",
name VARCHAR2(100) PATH "$.name",
nationality VARCHAR2(100) PATH "$.nationality",
country_code VARCHAR2(3) PATH "$.coutry_code",
iso_alpha2 VARCHAR2(2) PATH "$.iso_alpha2",
capital VARCHAR2(100) PATH "$.capital",
population NUMBER PATH "$.population",
area_km2 NUMBER PATH "$.area_km2",
region_id NUMBER PATH "$.region_id",
sub_region_id NUMBER PATH "$.sub_region",
intermediate_region_id NUMBER PATH "$.intermediate_region_id",
organization_region_id NUMBER PATH "$.organization_region_id"))';
END;