Import json data from URL using SQL query in Report Builder
Hi Everyone!
One of my tables in Report Builder contains "URL" column that contains link to json format file with information that given link was archived: "archived":true. I would like to extract that information to the new created column in the table, so I know which link was archived and which not. I tried to use something like this:
SELECT JSON_VALUE(LT.URL, '$.archived') AS archived_value
FROM my_table LT
But I get error:
CRRGW5616E The input string template is invalid, causing an com.ibm.team.integration.reporting.common.exceptions.STParseException exception.
I think the missing part is that column "URL" contains http that I have follow to get the content of JSON, but I am not sure how to get that JSON from http given in "URL" column.
I tried this to get JSON content:
I tried this to get JSON content:
SELECT LT.URL, utl_http.request(LT.URL) AS json_content
FROM my_table LT
Syntax i valid, but I get error:
CRRGW5503E An unexpected response was received from the data source: ORA-29273: HTTP request failed ORA-06512: at "SYS.UTL_HTTP", line 1530 ORA-24247: network access denied by access control list (ACL) ORA-06512: at "SYS.UTL_HTTP", line 380 ORA-06512: at "SYS.UTL_HTTP", line 1470 ORA-06512: at line 1
Syntax i valid, but I get error:
CRRGW5503E An unexpected response was received from the data source: ORA-29273: HTTP request failed ORA-06512: at "SYS.UTL_HTTP", line 1530 ORA-24247: network access denied by access control list (ACL) ORA-06512: at "SYS.UTL_HTTP", line 380 ORA-06512: at "SYS.UTL_HTTP", line 1470 ORA-06512: at line 1
I would be grateful for any ideas.