Oracle 11g – Natywna usługa sieciowa zwracająca wynik dowolnego zapytania SQL w formacie XML

W Oracle 11g przy użyciu wbudowanego serwera HTTP można publikować natywne webservice’y. Zazwyczaj konfiguracja jest tak zorganizowana aby można było uruchamiać funkcje i procedury konkretnego użytkownika. W tym poście zobaczysz jak stworzyć WebService który umożliwi dowolnemu użytkownikowi uruchomić dowolne polecenie SQL (oczywiście respektując uprawnienia użytkownika) i zwrócić jego wynik w postaci XML’a

Krok pierwszy – tworzymy DAD przy użyciu pakietu DBMS_EPG:

BEGIN
DBMS_EPG.create_dad (
dad_name => 'DAD_SQL',
path => '/dad_sql/*');
END;
/

Krok drugi – na dowolnym schemacie tworzymy procedurę pozwalającą uruchomić dowolny kod SQL’a w kontekście uprawnień użytkownika wywołującego (AUTHID CURRENT_USER) i zwracającą do bufora HTTP dane wynikowe w postaci XML’a. Udostępniamy ją publicznie i tworzymy do niej publiczny synonim:

create OR REPLACE procedure wykonaj_sql(p_sql varchar2) AUTHID current_user is
begin
  htp.prn(dbms_xmlgen.getXML(p_sql));
EXCEPTION WHEN OTHERS THEN
  htp.prn(SQLERRM);
end;
/

GRANT EXECUTE ON wykonaj_sql TO PUBLIC;

CREATE PUBLIC SYNONYM wykonaj_sql FOR wykonaj_sql;

Możemy teraz wywołać naszą usługę korzystając z adresu i parametru p_sql (oczywiście localhost zamieniamy na domenę chyba, że pracujemy lokalnie):

http://<LOGIN>:<HASŁO>@localhost:8080/dad_sql/wykonaj_sql?p_sql=select+user+from+dual

W wyniku powinniśmy zobaczyć nazwę naszego użytkownika. Przy wykorzystywaniu AJAX’a podczas pobierania wyniku trzeba przesłać login i hasło razem z odwołaniem JavaScript:

function make_base_auth(user, password) {
  var tok = user + ':' + pass;
  var hash = btoa(tok);
  return "Basic " + hash;
}

//Przykłady w różnych frameworkach/bibliotekach

var auth = make_basic_auth('me','mypassword');
var url = 'http://localhost:8080/dad_sql/wykonaj_sql?p_sql=select+user+from+dual';

// bezpośrednio
xml = new XMLHttpRequest();
xml.setRequestHeader('Authorization', auth);
xml.open('GET',url)

// ExtJS
Ext.Ajax.request({
    url : url,
    method : 'GET',
    headers : { Authorization : auth }
});

// jQuery
$.ajax({
    url : url,
    method : 'GET',
    beforeSend : function(req) {
        req.setRequestHeader('Authorization', auth);
    }
});