Hi,
I am using APEX 19.1, DB 12.2.0.1.0
I am trying to get data using apex_web_service.make_rest_request from webservices
The URL is
http://data.fixer.io/api/latest?access_key=123456789
When I use
DECLARE
l_clob CLOB;
BEGIN
l_clob := apex_web_service.make_rest_request(
p_url => 'http://data.fixer.io/api/latest',
p_http_method => 'GET',
p_parm_name => apex_util.string_to_table('access_key'),
p_parm_value => apex_util.string_to_table('123456789'));
dbms_output.put_line(l_clob);
END;
I get the correct JSON output
Then When i try
SELECT to_date(xe_date, 'YYYY-MM-DD') xe_date,
base,
gbp,
usd
FROM
JSON_TABLE ( apex_web_service.make_rest_request(p_url => 'http://data.fixer.io/api/latest',
p_http_method => 'GET',
parm_name => apex_util.string_to_table('access_key'),
p_parm_value => apex_util.string_to_table('123456789')
), '$[*]'
COLUMNS (
state VARCHAR2 ( 100 ) PATH '$.success',
base VARCHAR2 ( 100 ) PATH '$.base',
xe_date VARCHAR2 ( 100 ) PATH '$.date',
NESTED PATH '$.rates[*]'
COLUMNS (
gbp NUMBER PATH '$.GBP',
usd NUMBER PATH '$.USD'
)
)
)
I get below error
ORA-00902: invalid datatype
00902. 00000 - "invalid datatype"
*Cause:
*Action:
Error at Line: 8 Column: 33
If I use - Give the Full URL instead of passing the passing the param/value as separate parameters
apex_web_service.make_rest_request(p_url => 'http://data.fixer.io/api/latest?access_key=123456789', p_http_method => 'GET')
in the above sql, I get correct results.
Can you please what is wrong ?
Thanks,
Veerendra.