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:
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
I would be grateful for any ideas.
|
One answer
Try first running the SQL outside of report builder using any Oracle client like SQL developer or SQL plus. if you get that working and the same query does not work in Report Builder, then contact IBM support and they will walk you through ways to get the same query to run in Report Builder. |
Your answer
Dashboards and work items are no longer publicly available, so some links may be invalid. We now provide similar information through other means. Learn more here.