Jak pobrać dane z Web Service do Oracle
Czyli jak połączyć się z Web Serwisem, pobrać dane, sparsować je i załadować do bazy danych lub użyć w aplikacji. Oto czego będziesz potrzebować:
- Uprawnienia w bazie danych do wykonywania połączeń HTTP
- Wgrany certyfikat Web Serwisu z którym się łączysz - jeśli połączenie jest https (przeważnie jest)
- Umiejętności parsowania JSON w Oracle. Zazwyczaj WS zwraca dane w formacie JSON
- Umiejętności szyfrowania danych (np. sekretkey) - jeśli WS tego wymaga
Wszystko powyższe przećwiczymy w tym artykule. W pierwszej części informacja o niezbędnych komponentach. Być może niektóre z nich nie są Ci potrzebne, pomiń te sekcje, ale w realnym scenariuszu rzadko jest potrzeba wykonania tylko trywialnego polaczenia z prostyym serwisem i wykonanie GET. Najczęściej jest potrzeba kodowania danych, parsowania wyników. Dlatego ten przykład jest inny od wiekszości przykładów w Internecie. W drugiej części zbierzemy wszystko do kupy w działającą procedurę.
Przygotowujemy środowisko
Uprawnienia do wykonania polączenia z zewnętrznym serwerem
Wykonywanie żądań HTTP z PL/SQL jest dość proste, przy użyciu pakietu UTL_HTTP. W bazie danych Oracle istnieją ograniczenia bezpieczeństwa dotyczące interakcji sieciowych. Oznacza to, że by wykonać połączenie HTTP z danym hostem i portem, konto to musi mieć jawne uprawnienia do tego. W bazie danych wykonaj komendę ("DATABASE" to nazwa Twojej bazy danych a "USER" to Twój użytkownik):
grant execute on utl_http to DATABASE;
Następnie jako użytkownik sysdba stwórz ACL:
BEGIN
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL (
acl => 'test.xml',
description => 'Some permissions',
principal => 'USER',
is_grant => TRUE,
privilege => 'connect',
start_date => SYSTIMESTAMP,
end_date => NULL);
COMMIT;
END;
PL/SQL procedure successfully completed.
Następnie:
BEGIN DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE( acl => 'test.xml', principal => 'USER', is_grant => true, privilege => 'resolve'); COMMIT; END;
PL/SQL procedure successfully completed.
Nastepnie:
BEGIN DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL ( acl => 'test.xml', host => '*'); COMMIT; END;
Instalowanie cetryfikatu SSL
Aby nawiązać połączenie z zabezpieczonym zasobem, musimy uzyskać niezbędny certyfikat. Najłatwiej sam certyfikat pobrac za pomocą przeglądarki. Załączony link tłumaczy krok po kroku instalację certyfikatu.
Parsowanie JSON w PL/SQL Oracle
Oracle oferuje funkcje które umożliwiają parsowanie JSON który to format najczęściej zwracają web serwisy. Dokładny tutorial jak użyć tych funkcjonalności (JSON_OBJECT_T, JSON_ARRAY_T) znajdziesz tutaj.
Nadawanie uprawnień do szyfrowania danych
Jeśli serwis z którym się łączysz wymaga szyfrowania danych autoryzacyjnych, zapoznaj się z poniższą informacją jak nadać uprawnienia do pakietu DBMS_CRYPTO:
grant execute on sys.dbms_crypto to USER;
Sprawdzenie wykonania polecenia
select * from dba_tab_privs where table_name = 'DBMS_CRYPTO' and owner = 'SYS';
Budujemy procedurę
Przykład tej procedury zakłada że dane logowania mamy w samej procedurze - dla uproszczenia. Możesz wynieść je na zewnątrz by nie modyfikować hasła czy loginy jeśli te dane się zmienią. Jak we wstępie, w procedurze mamy demonstrację kodowania znaków, parsowania rezultatu. Często zachodzi taka potrzeba - masz tu zatem gotowy przykład. Coś więcej niż trywialne GET bez loginu.
create or replace PROCEDURE test_method(json in VARCHAR2)
AS
ws_url VARCHAR2(500) := 'https://some.api.url.com';
final_url varchar2(200);
GET_data VARCHAR2(100) := '/rest/method';
apisecret varchar2(100) := 'api_secret';
apikey varchar2(200) := 'apikey';
X_Rest_ApiSign varchar2(1000);
X_Rest_ApiSign_SHA1 varchar2(1000);
json_request VARCHAR2(4000);
buffer varchar2(4000);
res_buffer clob;
sha_string varchar(200);
res_code varchar2(10);
req utl_http.req;
res utl_http.resp;
json_obj JSON_OBJECT_T;
BEGIN
UTL_HTTP.set_wallet('file:D:\owallet\https_wallet','wallet_pass');
json_request := json;
X_Rest_ApiSign := apikey||GET_data||json_request||apisecret;
X_Rest_ApiSign_SHA1 := sys.dbms_crypto.hash(UTL_RAW.CAST_TO_RAW(X_Rest_ApiSign), sys.dbms_crypto.hash_sh1);
final_url := ws_url||GET_data;
req := utl_http.begin_request(final_url, 'POST',' HTTP/1.1');
utl_http.set_body_charset('UTF-8');
utl_http.set_header(req, 'user-agent', 'mozilla/4.0');
utl_http.set_header(req, 'content-type', 'application/json');
utl_http.set_header(req, 'Content-Length', length(json_request));
utl_http.set_header(req, 'X-Rest-ApiSign', LOWER(X_Rest_ApiSign_SHA1));
utl_http.set_header(req, 'X-Rest-ApiKey', apikey);
utl_http.write_text(req, json_request);
res := utl_http.get_response(req);
res_code := res.status_code;
-- process the response from the HTTP call
DBMS_LOB.createtemporary(res_buffer, TRUE);
begin
loop
utl_http.read_line(res, buffer);
-- Zwraca przeczytana linie odpowiedzi:
-- dbms_output.put_line(buffer);
-- Inkrementuje res_buffer (wszystkie linie odpowiedzi WS)
DBMS_LOB.WRITEAPPEND(res_buffer, length(buffer), buffer);
end loop;
--dbms_output.put_line (buffer);
utl_http.end_response(res);
exception
when utl_http.end_of_body
then
utl_http.end_response(res);
end;
-- Parsujemy JSON odpowiedzi
json_obj := JSON_OBJECT_T(res_buffer);
dbms_output.put_line('status : ' || json_obj.get_string('status'));
dbms_output.put_line ('code: '|| res_code);
END test_method;
Szkusz pomocy w stworzeniu biznesowej aplikacji na platformie APEX? Skontaktuj się z nami.