Guía de instalación del PLSQL Gateway utilizando el paquete PLJSON para Oracle 11G o 12C

A continuación les dejo una breve guía de instalación para poder utilizar el PLSQL Gateway de Oracle 11G como servidor de peticiones web. Comentamos de utilizar el paquete PLJSON porque por ejemplo en versiones anteriores a la 12c, no hay tratamiento nativo de JSON. A partir de la version 12c Oracle introdujo funciones específicas. Para este ejemplo hemos creado un usuario llamado TEST. Si tienen oracle 12c instalado, el PLJSON no es necesario instalarlo.

1-Nos bajamos el paquete PLJSON desde aquí https://github.com/pljson/pljson, descomprimimos el zip y desde un sqlplus ejecutamos el script @install.sql

2-Desde un SQLPLUS como SYS ejecutamos los siguientes parámetros.

--Configuramos el PLSQL Gateway para que escuche por el puerto 8080
EXECUTE dbms_xdb.SETHTTPPORT(8080);

--Creamos las acls correspondientes.
begin

  dbms_network_acl_admin.create_acl (

    acl             => 'utlpkg.xml',

    description     => 'Normal Access',

    principal       => 'CONNECT',

    is_grant        => TRUE,

    privilege       => 'connect',

    start_date      => null,

    end_date        => null

  );

end;



begin

  dbms_network_acl_admin.add_privilege (

  acl         => 'utlpkg.xml',

  principal     => 'TEST',--usuario de bbdd

  is_grant     => TRUE,

  privilege     => 'connect',

  start_date     => null,

  end_date     => null);

  dbms_network_acl_admin.assign_acl (

  acl => 'utlpkg.xml',

  host => '127.0.0.1',

  lower_port => 7000,

  upper_port => 9200);

end;

--Creamos el DAD que autoriza al usuario de bbdd a poder desplegar funciones o procedimientos a través del plsql gateway.
BEGIN

  DBMS_EPG.create_dad

  ( dad_name => 'restapi'

  , path => '/api/*'

  );

  DBMS_EPG.AUTHORIZE_DAD('restapi','TEST');--usuario de bbdd

end;

--se define la ruta a la función.
BEGIN
  DBMS_EPG.set_dad_attribute (
    dad_name   => 'restapi',
    attr_name  => 'default-page',
    attr_value => 'home');--será como 127.0.0.1:8080/api/home

  DBMS_EPG.set_dad_attribute (
    dad_name   => 'restapi',
    attr_name  => 'database-username',
    attr_value => 'TEST');--usuario de bbdd
END;
/

--Procedimiento de prueba.
CREATE OR REPLACE PROCEDURE TEST.home IS
BEGIN
  HTP.htmlopen;
  HTP.headopen;
  HTP.title('Este es el titulo!');
  HTP.headclose;
  HTP.bodyopen;
  HTP.print('Esta es una página de prueba! Fecha: ' || TO_CHAR(SYSTIMESTAMP) || ' ejemplo creado desde el blogdelpibe.com');
  HTP.bodyclose;
  HTP.htmlclose;
END home;
/

--Por defecto Oracle bloquea todas las peticiones anónimas, forzando a solicitar usuario y contraseña. Para entornos de desarrollo, lo mejor es permitir peticiones anónimas.

SET SERVEROUTPUT ON
DECLARE
  l_configxml XMLTYPE;
  l_value     VARCHAR2(5) := 'true'; -- (true/false)
BEGIN
  l_configxml := DBMS_XDB.cfg_get();

  IF l_configxml.existsNode('/xdbconfig/sysconfig/protocolconfig/httpconfig/allow-repository-anonymous-access') = 0 THEN
    -- Add missing element.
    SELECT insertChildXML
           (
             l_configxml,
             '/xdbconfig/sysconfig/protocolconfig/httpconfig',
             'allow-repository-anonymous-access',
             XMLType('<allow-repository-anonymous-access xmlns="http://xmlns.oracle.com/xdb/xdbconfig.xsd">' ||
                      l_value ||
                     '</allow-repository-anonymous-access>'),
             'xmlns="http://xmlns.oracle.com/xdb/xdbconfig.xsd"'
           )
    INTO   l_configxml
    FROM   dual;

    DBMS_OUTPUT.put_line('Element inserted.');
  ELSE
    -- Update existing element.
    SELECT updateXML
           (
             DBMS_XDB.cfg_get(),
             '/xdbconfig/sysconfig/protocolconfig/httpconfig/allow-repository-anonymous-access/text()',
             l_value,
             'xmlns="http://xmlns.oracle.com/xdb/xdbconfig.xsd"'
           )
    INTO   l_configxml
    FROM   dual;

    DBMS_OUTPUT.put_line('Element updated.');
  END IF;

  DBMS_XDB.cfg_update(l_configxml);
  DBMS_XDB.cfg_refresh;
END;

--Desbloqueamos el usuario anonymous.
ALTER USER anonymous ACCOUNT UNLOCK;

--Probamos el procedure de ejemplo desde nuestro navegador favorito.

http://<server-name>:8080/api/home

--El resultado será
Esta es una página de prueba! Fecha: 29-08-2018 ejemplo creado desde el blogdelpibe.com

Realmente en este ejemplo no hemos utilizado el PLJSON para mostrar el resultado en la web, pero si necesitamos hacerlo, crearemos una tabla de ejemplo con algunos datos, luego utilizaremos el siguiente procedimiento para devolver los resultados en formato json.

Obviamente si quieren hacerlo visible desde la web, deberán crear un procedimiento con la salida ret.hpt para que se imprima la salida en formato html.

create or replace procedure articulos

is

ret pljson_list;
dyn_s varchar2(200);

begin
dyn_s := 'select codart ,desart from articulos';

ret:=pljson_dyn.executeList(dyn_s);
ret.htp;
end;

Luego invocamos la función con:

begin
articulo;
end;

El resultado será algo similar a esto:

[{"CODART":"112","DESART":"prueba"}]

Referencias:
https://technology.amis.nl/2015/05/13/publish-a-rest-service-from-plsql-to-handle-http-post-requests-using-the-embedded-plsql-gateway/

https://oracle-base.com/articles/10g/dbms_epg_10gR2

Espero que les resulte útil.

Share on Google Plus