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.