Archivo de la categoría: PL/SQL

Python para desarrolladores PL/SQL

Arup Nanda, Oracle ACE Director, ha publicado una serie de tutoriales para mostrar las diferencias y similitudes entre el desarrollo con Python y PL/SQL.

Estos tutoriales están disponibles en Introduction to Python for PL/SQL Developers – … | Oracle Community

Anuncios

HEXTOBLOB

Implementación de la función HEXTORAW para dar soporte a CLOBS:

create or replace
procedure hextoblob(
                    src_clob  in       clob,
                    dest_blob in out   nocopy blob
				   )
is
    v_pos    number := 1;
    v_amount number := 1024;
    v_buffer varchar2(1024);
    v_raw    raw(2048);
begin

    while v_pos < dbms_lob.getlength(src_clob)
    loop
        dbms_lob.read(
            lob_loc => src_clob, 
            amount  => v_amount, 
            offset  => v_pos, 
            buffer  => v_buffer);
        
        v_raw := hextoraw(v_buffer);

        dbms_lob.writeappend(
            lob_loc => dest_blob, 
            amount  => utl_raw.length(v_raw), 
            buffer  => v_raw);
        
        v_pos := v_pos + v_amount;
    end loop;
    
end hextoblob;

Para probar el procedimiento podemos ejecutar el siguinte código:

declare
  cl_test   clob := '4041424344';
  bl_result blob;
begin

  dbms_lob.createtemporary(
    lob_loc => bl_result,
    cache   => false,
    dur     => dbms_lob.call);

  hextoblob(
    src_clob  => cl_test,
    dest_blob => bl_result);

  dbms_output.put_line('Result: '||
                        utl_raw.cast_to_varchar2( bl_result ));

  dbms_lob.freetemporary( bl_result );

end;

El resultado debe ser:

Result: @ABCD

PL/SQL generate XML output

Para generar XML desde PL/SQL existen diversos métodos que van desde una select que utilice funciones XML_TYPE al empleo de los paquetes PL/SQL integrados en Oracle. En nuestro ejemplo vamos a utilizar DBMS_XMLDOM para montar un XML como este:

<?xml version="1.0" encoding="ISO-8859-1"?>
<root>
  <node01 Attr01="Atribute 01" Attr02="Atribute 02">Node 01 text</node01>
  <node02>Node 02 text</node02>
</root>

Paquete de utilidades XML

El primer paso consiste en compilar el siguiente paquete en la base de datos. Esto nos va a permitir agilizar la creación de los nodos utilizando una serie de tablas para registrar cada nodo y el procedimiento de de escritura de estos nodos en el XML que estemos generando:

CREATE OR REPLACE PACKAGE create_xml IS
 
-- Declare XML types
   type ry_xmlattrib is record (
                                p_attrib_name     varchar2(512),
                                p_attrib_value    varchar2(32767)
                               );
   type ty_xmlattrib is table of ry_xmlattrib;
 
   type ry_xmlelement is record (
                                 p_parent         dbms_xmldom.DOMNode,
                                 p_element_name   varchar2(512),
                                 p_element        dbms_xmldom.DOMElement,
                                 p_attribs        ty_xmlattrib,
                                 p_node           dbms_xmldom.DOMNode,
                                 p_text_value     varchar2(32767),
                                 p_text           dbms_xmldom.DOMText,
                                 p_textnode       dbms_xmldom.DOMNode
                                );
   type ty_xmlelement is table of ry_xmlelement;

   type ty_varchar2 is table of varchar2(32767) index by varchar2(64);
 

-- Add node to ty_xmlelement
    procedure add_node(
                       p_tb_xmlelements  in out  ty_xmlelement,
                       p_tb_index_nodes  in out  ty_varchar2,
                       p_index_id        in      varchar2,
                       p_parent_index_id in      varchar2,
                       p_node_name       in      varchar2,
                       p_text_value      in      varchar2,
                       p_attribs         in      ty_xmlattrib
                      );
-- Write node to XML
   procedure write_node(
                        p_domdoc    in out   dbms_xmldom.DOMDocument,
                        p_reg_nodo  in out   ry_xmlelement
                       );
 
END create_xml;

CREATE OR REPLACE PACKAGE BODY create_xml AS

-- Add node to ty_xmlelement
procedure add_node(
                   p_tb_xmlelements  in out  ty_xmlelement,
                   p_tb_index_nodes  in out  ty_varchar2,
                   p_index_id        in      varchar2,
                   p_parent_index_id in      varchar2,
                   p_node_name       in      varchar2,
                   p_text_value      in      varchar2,
                   p_attribs         in      ty_xmlattrib
                  )
is
begin

   -- Add node
   p_tb_xmlelements.extend;
   p_tb_index_nodes( p_index_id ) := p_tb_xmlelements.last;
   p_tb_xmlelements( p_tb_index_nodes( p_index_id ) ).p_attribs := create_xml.ty_xmlattrib();
   p_tb_xmlelements( p_tb_index_nodes( p_index_id ) ).p_parent := p_tb_xmlelements( p_tb_index_nodes( p_parent_index_id ) ).p_node;
   p_tb_xmlelements( p_tb_index_nodes( p_index_id ) ).p_element_name := p_node_name;
   p_tb_xmlelements( p_tb_index_nodes( p_index_id ) ).p_text_value := p_text_value;
   
   -- Add attribs
   if p_attribs is not null then
      p_tb_xmlelements( p_tb_index_nodes( p_index_id ) ).p_attribs := create_xml.ty_xmlattrib();
      for i_att in 1..p_attribs.count() loop
          p_tb_xmlelements( p_tb_index_nodes( p_index_id ) ).p_attribs.extend();
          p_tb_xmlelements( p_tb_index_nodes( p_index_id ) ).p_attribs(i_att).p_attrib_name := p_attribs(i_att).p_attrib_name;
          p_tb_xmlelements( p_tb_index_nodes( p_index_id ) ).p_attribs(i_att).p_attrib_value := p_attribs(i_att).p_attrib_value;
      end loop;
   end if;
   
end add_node;

-- Write node to XML
procedure write_node(
                     p_domdoc    in out   dbms_xmldom.DOMDocument,
                     p_reg_nodo  in out   ry_xmlelement
                    )
is
begin
   
   -- Write node
   p_reg_nodo.p_element := dbms_xmldom.createElement(p_domdoc, p_reg_nodo.p_element_name );
     
   if p_reg_nodo.p_attribs is not null then
       if p_reg_nodo.p_attribs.count() > 0 then
          for i_att in 1..p_reg_nodo.p_attribs.count() loop
             dbms_xmldom.setAttribute(
                                      p_reg_nodo.p_element, 
                                      p_reg_nodo.p_attribs(i_att).p_attrib_name, 
                                      p_reg_nodo.p_attribs(i_att).p_attrib_value
                                     );
          end loop;
       end if;
   end if;
     
   p_reg_nodo.p_node := dbms_xmldom.appendChild(p_reg_nodo.p_parent,dbms_xmldom.makeNode(p_reg_nodo.p_element));
   p_reg_nodo.p_text := dbms_xmldom.createTextNode(p_domdoc, p_reg_nodo.p_text_value );
   p_reg_nodo.p_textnode := dbms_xmldom.appendChild(p_reg_nodo.p_node,dbms_xmldom.makeNode(p_reg_nodo.p_text));

end write_node;

END create_xml;

Ejemplo

El siguiente procedimiento utiliza el paquete create_xml para generar el XML de ejemplo con el que se ha iniciado esta entrada:

-- Generate XML (example)
CREATE OR REPLACE PROCEDURE xml_example
IS
 
  l_domdoc dbms_xmldom.DOMDocument;
  l_root_node dbms_xmldom.DOMNode;
  
  tb_xmlelements create_xml.ty_xmlelement := create_xml.ty_xmlelement();
  tb_index create_xml.ty_varchar2;
  tb_attribs create_xml.ty_xmlattrib := create_xml.ty_xmlattrib();
    
  v_buffer varchar2(32767);
  
BEGIN
  
   -- Create an empty XML document
   l_domdoc := dbms_xmldom.newDomDocument;
  
   -- Set version
   dbms_xmldom.setVersion(l_domdoc, '1.0');
   dbms_xmldom.setCharset(l_domdoc, 'ISO-8859-1');
  
   -- Create a root node
   l_root_node := dbms_xmldom.makeNode(l_domdoc);
   tb_xmlelements.extend;
   tb_index('root') := tb_xmlelements.last;

   tb_xmlelements(tb_index('root')).p_attribs      := create_xml.ty_xmlattrib();
   tb_xmlelements(tb_index('root')).p_parent       := l_root_node;
   tb_xmlelements(tb_index('root')).p_element_name := 'root';
   tb_xmlelements(tb_index('root')).p_text_value   := '';
       
   create_xml.write_node(l_domdoc, tb_xmlelements(tb_index('root')));
       
   -- Create a new node with text and attributes
   tb_attribs.extend();
   tb_attribs(1).p_attrib_name := 'Attr01';
   tb_attribs(1).p_attrib_value := 'Atribute 01';
  
   tb_attribs.extend();
   tb_attribs(2).p_attrib_name := 'Attr02';
   tb_attribs(2).p_attrib_value := 'Atribute 02';

   create_xml.add_node(tb_xmlelements, tb_index, 'node01', 'root', 'node01', 'Node 01 text', tb_attribs );
   create_xml.write_node(l_domdoc, tb_xmlelements(tb_index('node01')));
       
   -- Create a additional node with text
   tb_attribs.delete;
   create_xml.add_node(tb_xmlelements, tb_index, 'node02', 'root', 'node02', 'Node 02 text', tb_attribs );
   create_xml.write_node(l_domdoc, tb_xmlelements(tb_index('node02')));

   -- Result
   dbms_xmldom.writeToBuffer(l_domdoc, v_buffer);
   dbms_output.put_line(v_buffer);
   dbms_xmldom.freeDocument(l_domdoc);
 
END xml_example;

Encoding

Hay que tener especial cuidado con la declaración del encoding del XML. La forma de declararlo varía dependiendo del destino al que se vaya a enviar el XML: salida dbms_output, un XMLType o un Lob. En algunos conextos, en lugar de utilizar el procedimiento dbms_xmldom.setCharset(l_domdoc, ‘ISO-8859-1’) hay que manipular el procedimiento setVersion de la siguiente forma:

dbms_xmldom.setVersion(l_domdoc, '1.0" encoding="ISO-8859-1');

Generar SHA de una cadena (PL/SQL + Java)

El paquete DBMS_CRYPTO nos permite generar hash de una cadena de texto utilizando distintos algoritmos como MD4, MD5 y SHA1. Pero si queremos generar un hash mediante SHA-256, SHA-384 o SHA-512 tenemos que apoyarnos en Java. Los siguientes bloques de código incorporan la posibilidad de generar estos hash desde SQL o PL/SQL.

Los algoritmos disponibles son los siguientes:

  • MD5
  • SHA-1
  • SHA-256
  • SHA-384
  • SHA-512

El primer paso es crear la clase Java que nos permita generar hash más complejos y compilarla en la base de datos:

create or replace and compile java source named encoder as
package com.test;

import java.io.UnsupportedEncodingException;
import java.security.MessageDigest;
import sun.misc.BASE64Encoder;

public class Encoder {

    public static String encode(String message, String algorithm) {
        
        String encodedMessage;
        
        try {
            MessageDigest messageDigest = MessageDigest.getInstance(algorithm);
            byte[] hash = messageDigest.digest(message.getBytes("UTF-8"));
            encodedMessage = (new BASE64Encoder()).encode(hash);
        
        } catch (Exception e) {
            encodedMessage = null;
        }
        
        return encodedMessage;
    }
} 

Una vez compilada la clase Java en nuestra base de datos, es necesario crear una función PL/SQL de enlace para poder invocar la clase desde una sentencia SQL o desde código PL/SQL:

create or replace function encoder(p_string    in varchar2,
                                   p_algorithm in varchar2) return varchar2 as
language java name 'com.test.Encoder.encode(java.lang.String, java.lang.String) return String';

Por último, un ejemplo de uso:

select Encoder('Hello', 'SHA-256') "hash_Hello",
       Encoder('HELLO', 'SHA-256') "hash_HELLO"
from dual;
hash_Hello
--------------------------------------------
GF+NsyJx/iX1Yab8k4suJkMG7DBO2lGAB9F2SCY4GWk=

hash_HELLO
--------------------------------------------
NzPNl3/46xi5hzV+Is7Zn0YJfzHssjnoeK5jdg6D5NU=

Ver también Generar SH1 de una cadena y Generar el HASH de una cadena

Generar SH1 de una cadena

El paquete DBMS_CRYPTO nos permite, entre otras cosas, generar SHA1 de cualquier cadena de texto. Admite los siguientes parámetros:

  • src – [RAW|BLOB|CLOB]: datos a los que aplicar el hash
  • typ – PLS_INTEGER: algoritmo hash a aplicar

Los algoritmos disponibles son los siguientes:

  • HASH_MD4 := 1
  • HASH_MD5 := 2
  • HASH_SH1 := 3

Nota: Es necesario conceder permisos de ejecución sobre este paquete a los usuarios que vayan a utilizarlo.

select DBMS_CRYPTO.hash(utl_raw.cast_to_raw ('Hello'),
                        3) "hash_Hello",
       DBMS_CRYPTO.hash(utl_raw.cast_to_raw ('HELLO'),
                        3) "hash_HELLO"
from dual;
hash_Hello
----------------------------------------
F7FF9E8B7BB2E09B70935A5D785E0CC5D9D0ABF0

hash_HELLO
----------------------------------------
C65F99F8C5376ADADDDC46D5CBCF5762F9E55EB7

Ver también Generar el HASH de una cadena

Generar el HASH de una cadena

El paquete DBMS_UTILITY nos permite, entre otras cosas, generar resúmenes HASH de cualquier cadena de texto. Admite los siguientes parámetros:

  • name – VARCHAR2: cadena a la que se aplica la función hash
  • base – NUMBER: valor mínimo que devolverá la función hash
  • hash_size – NUMBER: valor máximo que devolverá la función hash
select DBMS_UTILITY.get_hash_value('Hello',
                                   1,
                                   power(2,16)-1
                                  ) "hash_Hello",
       DBMS_UTILITY.get_hash_value('HELLO',
                                   1,
                                   power(2,16)-1
                                  ) "hash_HELLO"
from dual;
hash_Hello hash_HELLO
---------- ----------
     11511      16086

Ver también Generar SH1 de una cadena

PL/SQL HTTP call function

La función que se muestra a continuación es una implementación ligera de acceso a recursos HTTP que sirve tanto para peticiones GET como POST. Esta función no da soporte a conexiones seguras HTTPS ni al envío de parámetros que requieran un tratamiento especial, como puede ser el envío de ficheros. Estas limitaciones nos permiten contar con una función relativamente pequeña que se puede incluir fácilmente en cualquier proyecto o script de prueba.

La función cuenta con 4 constantes, declaradas en las primeras líneas de código, para definir los siguientes aspectos:

  • cons_max_retries: Número de reintentos que vamos a realizar en caso de error de conexión.
  • cons_result_ok: Mensaje resultado en caso de éxito.
  • cons_result_error: Mensaje resultado en caso de error.
  • cons_result_sharp: Carácter separador para montar los mensajes de respuesta compuestos.

El código completo de la función http_param se muestra a continuación:

create function http_param(
                           p_url         in  varchar2,
                           p_method      in  varchar2,
                           p_param       in  varchar2,
                           p_html_result out varchar2
                          ) return varchar2
is

   cons_max_retries             CONSTANT number := 3; -- Maximum retries number.
   cons_result_ok               CONSTANT varchar2(10) := 'OK';
   cons_result_error            CONSTANT varchar2(10) := 'ERROR';
   cons_result_sharp            CONSTANT varchar2(1)  := '#';

   req                          UTL_HTTP.REQ;
   resp                         UTL_HTTP.RESP;
   v_value                      varchar2(1024);
   v_url                        varchar2(200);
   v_param                      varchar2(500);
   v_param_length               number;
   v_html_result                varchar2(32767);

   retry                        boolean;
   num_retries                  number;

   v_result                     varchar2(1024);

begin

     -- Init variables
     v_url := p_url;
     v_param := p_param;
     v_param_length := length(v_param);

     if p_method = 'GET' then
        v_url := v_url||'?'||v_param;
     end if;
    
     retry := true;
     num_retries := 1;

     -- Retry loop
     while retry and num_retries <= cons_max_retries loop

         v_result := cons_result_ok;

         begin
             req := UTL_HTTP.BEGIN_REQUEST (url=> v_url, method => p_method);
             UTL_HTTP.SET_HEADER (r      =>  req,
                                  name   =>  'Content-Type',
                                  value  =>  'application/x-www-form-urlencoded');
             
             if p_method = 'POST' then
               UTL_HTTP.SET_HEADER (r      =>   req,
                                    name   =>   'Content-Length',
                                    value  =>   v_param_length);
               UTL_HTTP.WRITE_TEXT (r      =>   req,
                                    data   =>   v_param);
             end if;
             
             resp := UTL_HTTP.GET_RESPONSE(req);
             
             -- Communication errors
             if resp.status_code != 200 then

                 dbms_output.put_line('Connection error:');
                 dbms_output.put_line('  Resp.status_code: '||resp.status_code);
                 dbms_output.put_line('  Resp.reason_phrase: '||resp.reason_phrase);
                 dbms_output.put_line('  Resp.http_version: '||resp.http_version);
                 dbms_output.put_line('  Resp.private_hndl: '||resp.private_hndl);
                 v_result := cons_result_error||cons_result_sharp||'Resp.status_code: '||resp.status_code||' - Resp.reason_phrase: '||resp.reason_phrase;

             -- Valid response
             else

                 -- Read response
                 begin
                    loop
                         UTL_HTTP.READ_LINE(resp, v_value, TRUE);
                         v_html_result := v_html_result || v_value;
                    end loop;
                 exception
                    when UTL_HTTP.END_OF_BODY then
                         UTL_HTTP.END_RESPONSE(resp);
                    when others then
                         v_result := cons_result_error||cons_result_sharp||'ERROR -> http_param: error READ_LINE - '||sqlerrm;
                         dbms_output.put_line( v_result );
                 end;

             end if;

             retry := false;
         exception
            when UTL_HTTP.TRANSFER_TIMEOUT then
               v_result := cons_result_error||cons_result_sharp||'WARNING -> http_param: error UTL_HTTP.TRANSFER_TIMEOUT (retry '||num_retries||'/'||cons_max_retries||') - '||sqlerrm;
               dbms_output.put_line( v_result );
               retry       := true;
               num_retries := num_retries + 1;
            when UTL_HTTP.HTTP_CLIENT_ERROR then
               v_result := cons_result_error||cons_result_sharp||'WARNING -> http_param: error UTL_HTTP.HTTP_CLIENT_ERROR (retry '||num_retries||'/'||cons_max_retries||') - '||sqlerrm;
               dbms_output.put_line( v_result );
               retry       := true;
               num_retries := num_retries + 1;
            when UTL_HTTP.HTTP_SERVER_ERROR then
               v_result := cons_result_error||cons_result_sharp||'WARNING -> http_param: error UTL_HTTP.HTTP_SERVER_ERROR (retry '||num_retries||'/'||cons_max_retries||') - '||sqlerrm;
               dbms_output.put_line( v_result );
               retry       := true;
               num_retries := num_retries + 1;
            when UTL_HTTP.REQUEST_FAILED then
               v_result := cons_result_error||cons_result_sharp||'WARNING -> http_param: error UTL_HTTP.REQUEST_FAILED (retry '||num_retries||'/'||cons_max_retries||') - '||sqlerrm;
               dbms_output.put_line( v_result );
               retry       := true;
               num_retries := num_retries + 1;
            when OTHERS then
               v_result := cons_result_error||cons_result_sharp||'ERROR -> http_param - '||sqlerrm;
               dbms_output.put_line( v_result );
               retry       := false;
               num_retries := num_retries + 1;
         end;

     end loop;

    -- Result
    p_html_result := v_html_result;
    return v_result;

exception
   when others then
      v_result := cons_result_error||cons_result_sharp||'ERROR -> http_param - '||sqlerrm;
      dbms_output.put_line('Error others: '||v_result);
      return v_result;
end http_param;

Para probar su funcionamiento podemos realizar una consulta al servicio de información meteorológica de Yahoo! En este caso para la ciudad de Madrid:

declare
  v_html_result varchar2(32767);
  v_result      varchar2(2048);
begin
  v_result := http_param(
                         p_url => 'http://weather.yahooapis.com/forecastrss',
                         p_method => 'GET',
                         p_param => 'w=12578024&u=c',
                         p_html_result => v_html_result
                        );
  dbms_output.put_line('Result: '||v_result);
  dbms_output.put_line('HTML result: ');
  if length(v_html_result) > 255 then
      for i in 0..ceil(length(v_html_result)/255) loop
          dbms_output.put_line(substr(v_html_result, ((255*i) + 1), 255));
      end loop;
  else
      dbms_output.put_line(v_html_result);
  end if;
end;

El resultado esperado, en caso de que logremos conectar con el servicio de Yahoo! desde nuestra sesión de base de datos, sería algo similar a esto:

Result: OK
HTML result: ...<xml_with_weather_data>...

Oracle 11g

Hay que tener en cuenta que a partir de la versión 11g de Oracle existe un control de acceso para el uso de cualquier protocolo que permita abrir conexiones externas a la base de datos. Este control de acceso se encuentra regulado por listas de acceso o ACL. Si no hemos habilitado el acceso a las URLs necesarias dando de alta las reglas oportunas en las ACL obtendremos un error como el siguiente:

ERROR#WARNING -> http_param: error UTL_HTTP.REQUEST_FAILED (retry 1/3) -
ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1130
ORA-24247: network access denied by access control list (ACL)
ERROR#WARNING -> http_param: error UTL_HTTP.REQUEST_FAILED (retry 2/3) -
ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1130
ORA-24247: network access denied by access control list (ACL)
ERROR#WARNING -> http_param: error UTL_HTTP.REQUEST_FAILED (retry 3/3) -
ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1130
ORA-24247: network access denied by access control list (ACL)
Result: ERROR#WARNING -> http_param: error UTL_HTTP.REQUEST_FAILED (retry 3/3) -
ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1130
ORA-24247: network access denied by access control list (ACL)
HTML result:

En la entrada “Control de acceso a recursos de red – ACL management” se explica cómo crear la ACL necesaria para completar la prueba de conexión con Yahoo!.