Me toca investigar un tema relativamente interesante:

Como invocar una API REST via POST para comunicar novedades de los cambios que vayan sufriendo las tablas de una base de datos Oracle 11g via trigger y stored procedure:

En la recopilación, me encontrado como siempre la información fragmentada y confusa. Lo que pretendo con este articulo es clarificar los pasos a seguir sin vueltas:

a trabajar !

 

— Como Sysdba de la DB creamos un usuario común que ejecutará las consultas a la DB que dispararán el trigger que generará por via de un Stored procedure una call a la API REST via POST con parámetros por su puesto.

— Como sysdba:

set serveroutput on;
exec dbms_output.enable(1000000000);
set escape ‘\’

CREATE USER APIREST IDENTIFIED BY APIREST;
GRANT CREATE SESSION TO APIREST;
GRANT CREATE VIEW TO APIREST;
GRANT CREATE TABLE TO APIREST;
GRANT UNLIMITED TABLESPACE TO APIREST;
GRANT CREATE SEQUENCE TO APIREST;
GRANT create procedure TO APIREST;
GRANT create trigger to APIREST;

grant execute on utl_http to APIREST
grant execute on dbms_lock to APIREST

BEGIN
DBMS_NETWORK_ACL_ADMIN.create_acl (
acl => ‘local_sx_acl_file.xml’,
description => ‘A test of the ACL functionality’,
principal => ‘APIREST’,
is_grant => TRUE,
privilege => ‘connect’,
start_date => SYSTIMESTAMP,
end_date => NULL);
COMMIT;
end;
/

begin
DBMS_NETWORK_ACL_ADMIN.assign_acl (
acl => ‘local_sx_acl_file.xml’,
host => ‘10.9.4.99’,
lower_port => 8161,
upper_port => NULL);
COMMIT;
end;
/

— LOGIN CON APIREST
— Usando la funcionalidad

create or replace
procedure consult_service_rest is
req UTL_HTTP.REQ;
resp UTL_HTTP.RESP;
value VARCHAR2(1024); — URL to post to
v_url VARCHAR2(200) := ‘http://10.9.4.99:8161/api/message/queue-pruebaa?type=queue’;
— Post Parameters
v_param VARCHAR2(500) := ‘body=hola_mundooo’;
v_param_length NUMBER := length(v_param);
BEGIN

req := UTL_HTTP.BEGIN_REQUEST (url=> v_url, method => ‘POST’);

— Use basic authentication if required.
UTL_HTTP.set_authentication(req, ‘sender’, ‘sender890’);

UTL_HTTP.SET_HEADER(req, ‘User-Agent’, ‘Mozilla/4.0’);

UTL_HTTP.SET_HEADER (r => req,
name => ‘Content-Type’,
value => ‘application/x-www-form-urlencoded’);
UTL_HTTP.SET_HEADER (r => req,
name => ‘Content-Length’,
value => v_param_length);
UTL_HTTP.WRITE_TEXT (r => req,
data => v_param);

resp := UTL_HTTP.GET_RESPONSE(req);

LOOP
UTL_HTTP.READ_LINE(resp, value, TRUE);
DBMS_OUTPUT.PUT_LINE(value);
END LOOP;
UTL_HTTP.END_RESPONSE(resp);
EXCEPTION
WHEN UTL_HTTP.END_OF_BODY THEN
UTL_HTTP.END_RESPONSE(resp);
END;
/

— prueba invocando un HTTP request directamente

begin
consult_service_rest;
end;
/

SEGUNDA PARTE: Trigger que antes de insert en una tabla invoque el Store consult_service_rest;

create table t (
x int
);

create or replace function f (p int)
return int as
begin
return p + 1;
end;
/

create or replace procedure p (p int) is
begin
dbms_output.put_line(‘Input = ‘ || p);
end p;
/

create or replace trigger trig
before insert on t FOR EACH ROW
declare
l int;
begin
l := f(:NEW.x);
p(l);
consult_service_rest;
end;
/

SQL> set serveroutput on
SQL> insert into t values (1);
Input = 2

Message Sent

 

Problemas que surgieron sobre la marcha:

A.– Ver si la ACL está bien establecida

http://dba-oracle.com/t_ora_24247_network_access_denied_by_access_control_list_tips.htm
select utl_http.request(‘http://10.9.4.199:8080/alfresco/faces/jsp/dashboards/container.jsp’) from dual;

 

B.- Certificado invalidos y no importados a wallet de oracle

http://blog.whitehorses.nl/2010/05/27/access-to-https-via-utl_http-using-the-orapki-wallet-command/
1.- con firefox, exportar en el x.509 Certificate PEM cada uno de los certificados desde el propio sitio hasta la autoridad certificante
2.- mkdir /home/oracle/wallet
3. -orapki wallet create -wallet /home/oracle/wallet -pwd password123 -auto_login
4.- scp los archivos de certificados a esa ruta del wallet
5.- agregar los certificados DESDE la CA hasta el propio sitio (al revez de como lo exportaste)
$ orapki wallet add -wallet /home/oracle/wallet -trusted_cert -cert COMODORSACertificationAuthority.crt -pwd password123
$ orapki wallet add -wallet /home/oracle/wallet -trusted_cert -cert COMODORSAOrganizationValidationSecureServerCA.crt -pwd password123
$ orapki wallet add -wallet /home/oracle/wallet -trusted_cert -cert gcbagobar.crt -pwd password123
6.- Comprobar que se hayan importado:
orapki wallet display -wallet /home/oracle/wallet -pwd password123

7.- Ahora a usarlo:

select utl_http.request(‘https://esb.gcba.gob.ar/infomet/historico/20171212′, NULL,’file:/home/oracle/wallet’,’password123′) from dual;