Então, eu finalmente pude dar atenção pra este tópico.
Eu criei uma conta gratis no
http://www.football-data.org e recebi o TOKEN via email.
Tudo que você precisa aprender está aqui:
https://docs.oracle.com/database/121/ARPLS/u_http.htm
Eu fiz assim:
Selecionar tudo
DECLARE
req UTL_HTTP.REQ;
resp UTL_HTTP.RESP;
name VARCHAR2(256);
value VARCHAR2(1024);
begin
req := UTL_HTTP.BEGIN_REQUEST (url=>'http://api.football-data.org/v2/competitions/PL/matches', method=>'GET');
UTL_HTTP.SET_HEADER (r => req,
name => 'X-Auth-Token',
value => 'f774999999999888888777777121b'); --CHANGE THE TOKEN !!!
--Here you put the parameters needed. (I'm using the example -- matchday 22.
UTL_HTTP.WRITE_TEXT (r => req,
data => 'matchday=22');
resp := UTL_HTTP.GET_RESPONSE
(r => req);
DBMS_OUTPUT.PUT_LINE('HTTP response status code: ' || resp.status_code);
DBMS_OUTPUT.PUT_LINE('HTTP response reason phrase: ' || resp.reason_phrase);
--Get all header info !!!
FOR i IN 1..UTL_HTTP.GET_HEADER_COUNT(resp) LOOP
UTL_HTTP.GET_HEADER(resp, i, name, value);
DBMS_OUTPUT.PUT_LINE(name || ': ' || value);
END LOOP;
--Now, get the JSON...
dbms_output.put_line(' ---- Here is the jason coming from the API !!! ---' );
utl_http.read_text(resp, value);
dbms_output.put_line( value );
UTL_HTTP.END_RESPONSE(resp);
end;
O retorno foi este:
Selecionar tudo
HTTP response status code: 200
HTTP response reason phrase:
Server: nginx/1.6.2
Date: Thu, 08 Aug 2019 22:44:54 GMT
Content-Type: application/json;charset=UTF-8
Transfer-Encoding: chunked
Connection: close
X-Application-Context: application:production
X-Requests-Available-Minute: 9
X-RequestCounter-Reset: 60
X-API-Version: v2
Access-Control-Allow-Origin: *
Access-Control-Allow-Credentials: true
Access-Control-Allow-Methods: GET
X-Authenticated-Client: glufke
Content-Language: en-US
---- Here is the jason coming from the API !!! ---
{"count":380,"filters":{},"competition":{"id":2021,"area":{"id":2072,"name":"England"},"name":"Premier League","code":"PL","plan":"TIER_ONE","lastUpdated":"2019-05-12T20:05:45Z"},"matches":[{"id":264341,"season":{"id":468,"startDate":"2019-08-09","endDate":"2020-05-17","currentMatchday":1},"utcDate":"2019-08-09T19:00:00Z","status":"SCHEDULED","matchday":1,"stage":"REGULAR_SEASON","group":"Regular Season","lastUpdated":"2019-06-13T21:33:58Z","score":{"winner":null,"duration":"REGULAR","fullTime":{"homeTeam":null,"awayTeam":null},"halfTime":{"homeTeam":null,"awayTeam":null},"extraTime":{"homeTeam":null,"awayTeam":null},"penalties":{"homeTeam":null,"awayTeam":null}},"homeTeam":{"id":64,"name":"Liverpool FC"},"awayTeam":{"id":68,"name":"Norwich City FC"},"referees":[]},{"id":264342,"season":{"id":468,"startDate":"2019-08-09","endDate":"2020-05-17","currentMatchday":1},"utcDate":"2019-08-10T11:30:00Z","status":"SCHEDULED","matchday":1,"stage":"REGULAR_SEASON","group":"Regular Season","lastUpdated":"2019-06-13T2
Veja que eu to retornando umas informações a mais antes de pegar o JSON.
Então, você salva isso numa tabela.
E daí segue as dicas do TOM KYTE:
https://asktom.oracle.com/pls/asktom/f? ... 0346341587
Selecionar tudo
create table t( doc_id int,
doc_details varchar2(1000)
);
insert into t values (1 , '{"id":1, "name" : "Jeff"}' );
insert into t values (2 , '{"id":2, "name" : "Jane", "status":"Gold"}' );
insert into t values (3 , '{"id":3, "name" : "Jill", "status":["Important","Gold"]}' );
insert into t values (4 , '{"name" : "John", "status":"Silver"}' );
commit;
select json_value(doc_details, '$.name') from t;
JSON_VALUE(DOC_DETAILS,'$.NAME')
Jeff
Jane
Jill
John