Archivo por meses: abril 2016

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

Interactive Report + Column confirm button

Deja un comentario

Introducción

Vamos a convertir una columna de un Interactive Report (IR) de APEX en un botón de cambio de estado con confirmación previa.

Partimos de un IR convencional, sobre una copia de la tabla EMP a la que hemos añadido una columna ACTIVE con el valor por defecto ‘YES’:

Las imágenes que nos servirán para el botón se han cargado en el apartado de recursos compartidos de la aplicación (Shared Components) asociadas a la aplicación:

Nota: Es importante acordarse de descargar los scripts de instalación y desintalación de cada una de las imágenes para incluirlos en el apartado «Supporting Objects». De esta forma, las imágenes se incorporarán de manera automática a los procesos de exportación / importación de la aplicación.

Paso 1: Preparamos el enlace

En nuestro ejemplo vamos a reemplazar la columna EMP.ACTIVE del informe por un botón que nos permita cambiar el valor entre ‘YES’ y ‘NO’ pulsando sobre él. Además, incluiremos una petición de confirmación previa para evitar que se ejecute por error.

El primer paso es crear una función PL/SQL que nos facilite la generación del código HTML con el que vamos reemplazar la información actual de la columna. Las siguientes líneas nos servirán:

create function GET_LINK_CHNG_ACTIVE(
    p_empno      in emp_test.empno%type,
    p_active     in emp_test.active%type,
    p_img_prefix in varchar2
   ) return varchar2
is
  v_apex_url  varchar2(2048);
  v_img_file  varchar2(255);
  v_img       varchar2(2048);
  v_link      varchar2(4000);
begin

  v_apex_url := 'javascript:chng_confirm('||p_empno||', '''||p_active||''')';

  case p_active
    when 'YES' then
      v_img_file := 'switch_on_2.gif';
    else
      v_img_file := 'switch_off_2.gif';
  end case;

  v_img     := '<img src="'||p_img_prefix||v_img_file||'"'||
                   ' alt="EMP Active: '||p_active||'"'||
                   ' title="EMP Active: '||p_active||'"'||
                   ' style="vertical-align:middle">';

  v_link    := '<a href="'||v_apex_url||'">' || v_img || '</a>';

  return v_link;

exception
  when others then
    return null;
end GET_LINK_CHNG_ACTIVE;

En este ejemplo se construye un enlace HTML con las siguientes características:

Ejecuta una función JavaScript para solicitar la confirmación del usuario.
Incluye una etiqueta de imagen con la referencia al fichero cargado en Shared Componens y que está asociado al estado actual del registro:
- La imagen «switch_on_2.gif» para los valores ‘YES’.
- La imagen «switch_off_2.gif» para el resto de valores.

Para indicar la ubicación de la imagen se utiliza el parámetro «p_img_prefix». Por norma general, las imágenes utilizadas en una aplicación APEX se suelen alojar en uno de estos tres lugares:

#WORKSPACE_IMAGES#: Cargadas en Shared Components y asociadas al espacio de trabajo.
#APP_IMAGES#: Cargadas en Shared Components y asociadas a la aplicación.
#IMAGE_PREFIX#: Ruta definida para las imágenes estáticas. Por defecto es «/i/».

Una vez compilada la función, debemos modificar la consulta del informe para invocarla en lugar de la columna ACTIVE:

Al guardar estos cambios y volver a cargar el informe de empleados debería aparecernos algo similar a lo siguiente:

¡Vamos bien! Tal y como estaba configurada la columna ACTIVE en el informe, APEX no puede interpretar el código HTML que está generando la función GET_LINK_CHNG_ACTIVE. Pero esto tiene fácil solución. Solo tenemos que editar los atributos del informe y sustituir el valor «Display as a text» por «Standard Report Column» en la definición de esta columna:

Una vez aceptados los cambios, si volvemos a cargar el informe deberíamos encontrarnos con una imagen como la siguiente:

Paso 2: Control de la acción

De momento, si pulsamos sobre la imagen no ocurrirá nada. El código HTML que hemos generado invoca una función JavaScript que aun no hemos declarado. Para que nuestra aplicación tenga constancia de ella deberemos acceder a las propiedades de la página y en el espacio «Function and Global Variable Declaration» incluir el código necesario:

function chng_confirm(p_empno, p_active) {
  apex.confirm(
    'Change EmpNO '+p_empno+' Active status?',
    {request:'CHNG_STAT',
     set:{'P25_EMPNO_CHNG_STAT':p_empno,
          'P25_ACTIVE_CHNG_STAT':p_active}
    }
  );
}

Esta función se encarga de solicitar la confirmación al usuario y, en caso de obtenerla, ejecutar el submit de la página asignando los siguientes valoresde:

Item P25_EMPNO_CHNG_STAT = p_empno
Item P25_ACTIVE_CHNG_STAT = p_active
REQUEST = ‘CHNG_STAT’

Estos Items no existen aun en nuestra página. Así que vamos a crearlos con las siguientes características:

Nota: Es importante tener en cuenta que la propiedad «Value Protected» de cada un de estos Items debe ser ‘NO’ para permitir al código JavaScript modificar su valor después de que el usuario confirme la acción.

Paso 3: Modificación de los datos

Por último, deberemos crear el proceso que se encargue de ejecutar la acción y modifique los datos pertinentes. Es recomendable que este proceso se encuentre compilado en base de datos y, de esta forma, separar las operaciones sobre el modelo de datos del apartado visual. El código podría ser como el que sigue:

create procedure SET_ACTIVE (
    p_empno  in number,
    p_active in varchar2
    )
as
begin

  if p_empno is not null and p_active is not null
  then

    update emp_test set
      active = decode(p_active,
                      'YES', 'NO',
                      'NO', 'YES',
                      active)
    where empno = p_empno
      and active = p_active;

  end if;

end SET_ACTIVE;

Por último, necesitamos indicar al motor de APEX cómo y en qué momento debe ejecutar este bloque de código. De esto se va a encargar un proceso de página, o Page Processing, con las siguientes características:

Type: «PL/SQL anonymous block»
Process point: «On Submit and Before Computation»
Conditions: «Request = Expression 1» y con la expresión igual a la cadena’CHNG_STAT’ que hemos indicado anteriormente en nuestra función JavaScript.

El código PL/SQL a ejecutar en este caso es muy breve:

begin
  SET_ACTIVE(:P25_EMPNO_CHNG_STAT, :P25_ACTIVE_CHNG_STAT);
end;

Si volvemos a cargar el informe, ya podremos activar y desactivar registros de empleados tras aceptar la confirmación que se nos va a solicitar.

SQL vs R

Deja un comentario

Introducción

Vamos a realizar una breve comparativa entre SQL y R. Para ello nos centraremos en los aspectos básicos del acceso a datos, mostrando ejemplos de las mismas operaciones en ambos lenguajes.

En junio de 2015 Oracle publicó el documento SQL – the natural language for analysis en el que se describen los fundamentos del lenguaje SQL y las ventajas que ofrece en el campo del acceso y análisis de datos:

http://www.oracle.com/technetwork/database/bi-datawarehousing/wp-sqlnaturallanguageanalysis-2431343.pdf

Éste documento será nuestra guía en la pequeña comparativa que sigue a continucaión.

Preparación del entorno

El juego de datos que se utiliza en los ejemplos del documento son las conocidas tablas EMP y DEPT, que tradicionalmente han acompañado a Oracle. Desde R se puede acceder a una gran variedad de fuentes de datos, entre las que se incluyen bases de datos, ficheros, recursos web, etc.

En este caso, para acceder a los datos de las tablas Oracle desde R, primero los volcaremos a un fichero con estructura CSV y los cargaremos utilizando la librería de R «DPLYR».

Las siguientes sentencias SQL nos pueden servir para generar la estructura CVS:

select '"EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO"' from dual
union all
select EMPNO||',"'||ENAME||'","'||JOB||'",'||MGR||','||HIREDATE||','||SAL||','||COMM||','||DEPTNO
from emp;

select '"DEPTNO","DNAME","LOC"' from dual
union all
select DEPTNO||',"'||DNAME||'","'||LOC||'"'
from dept;

Después de guardar los datos de EMP y DEPT en disco con formato CSV será necesario copiar ambos ficheros en una ruta a la que se tenga acceso desde el entorno de ejecución de R (por ejemplo, un subdirectorio «./data» dentro del espacionde trabajo) y ejecutar los siguientes comandos:

library(dplyr)
emp <- tbl_dt(read.csv("./data/emp.csv",stringsAsFactors = FALSE))
dept <- tbl_dt(read.csv("./data/dept.csv",stringsAsFactors = FALSE))

Principios de SQL

El lenguaje SQL se compone de cuatro operadores básicos:

Projection
Filter
Join
Aggregate

Projection

El operador projection permite indicar las columnas que queremos recuperar de una tabla. En su forma básica, se indican las columnas existentes por su nombre o posición en la tabla.

SELECT ename, job, hiredate
FROM emp;

ENAME	JOB		HIREDATE
-----	---------	--------------------
KING	PRESIDENT	17-NOV-1981 00:00:00
BLAKE	MANAGER		01-MAY-1981 00:00:00
CLARK	MANAGER		09-JUN-1981 00:00:00
JONES	MANAGER		02-APR-1981 00:00:00
-- Next lines were deleted

emp %>%
select(ENAME, JOB, HIREDATE)

Source: local data table [14 x 3]

    ENAME       JOB             HIREDATE
    (chr)     (chr)                (chr)
1    KING PRESIDENT 17-NOV-1981 00:00:00
2   BLAKE   MANAGER 01-MAY-1981 00:00:00
3   CLARK   MANAGER 09-JUN-1981 00:00:00
4   JONES   MANAGER 02-APR-1981 00:00:00
# Next lines were deleted

La proyección, en su forma extendida, permite generar columnas dinámicas aplicando operaciones que son calculadas en tiempo de ejecución.

SELECT ename, sal, comm, comm/sal*100 as COMM_RATE
FROM emp;

ENAME	SAL	COMM	COMM_RATE
-----	----	----	---------
KING	5000	- 	-
BLAKE	2850	- 	-
CLARK	2450	- 	-
JONES	2975	- 	-
SCOTT	3000	- 	-
FORD	3000	- 	-
SMITH	800	- 	-
ALLEN	1600	300	18.75
WARD	1250	500	40
MARTIN	1250	1400	112
TURNER	1500	0	0
ADAMS	1100	- 	-
JAMES	950	- 	-
MILLER	1300	- 	-

emp %>%
mutate(COMM_RATE = COMM/SAL*100) %>%
select(ENAME,SAL,COMM,COMM_RATE)

Source: local data table [14 x 4]

    ENAME   SAL  COMM COMM_RATE
    (chr) (int) (int)     (dbl)
1    KING  5000    NA        NA
2   BLAKE  2850    NA        NA
3   CLARK  2450    NA        NA
4   JONES  2975    NA        NA
5   SCOTT  3000    NA        NA
6    FORD  3000    NA        NA
7   SMITH   800    NA        NA
8   ALLEN  1600   300     18.75
9    WARD  1250   500     40.00
10 MARTIN  1250  1400    112.00
11 TURNER  1500     0      0.00
12  ADAMS  1100    NA        NA
13  JAMES   950    NA        NA
14 MILLER  1300    NA        NA

Filter

El operador filter permite indicar una serie de reglas que deben cumplir los datos almacenados para ser recuperados en la consulta. En su forma más sencilla comparan los valores de la tabla con una expresión.

SELECT ename, job, hiredate
FROM emp
WHERE job = 'CLERK';

ENAME	JOB	HIREDATE
------	-----	--------------------
SMITH	CLERK	17-DEC-1980 00:00:00
ADAMS	CLERK	12-JAN-1983 00:00:00
JAMES	CLERK	03-DEC-1981 00:00:00
MILLER	CLERK	23-JAN-1982 00:00:00

emp %>%
filter(JOB == "CLERK") %>%
select(ENAME, JOB, HIREDATE)

Source: local data table [4 x 3]

   ENAME   JOB             HIREDATE
   (chr) (chr)                (chr)
1  SMITH CLERK 17-DEC-1980 00:00:00
2  ADAMS CLERK 12-JAN-1983 00:00:00
3  JAMES CLERK 03-DEC-1981 00:00:00
4 MILLER CLERK 23-JAN-1982 00:00:00

Join

El operador join relaciona distintas tablas a través de la comparación de uno o varios de sus campos. Existen varios tipos de join dependiendo de la forma en la que va a realizar el cruzado de los datos: inner, full outer, outer left, outer right y cross.

SELECT d.deptno, d.dname, e.empno, e.ename
FROM dept d INNER JOIN emp e
 ON (e.deptno = d.deptno);

DEPTNO	DNAME		EMPNO	ENAME
------	----------	-----	------
10	ACCOUNTING	7782	CLARK
10	ACCOUNTING	7934	MILLER
10	ACCOUNTING	7839	KING
20	RESEARCH	7902	FORD
20	RESEARCH	7788	SCOTT
-- Next lines were deleted

emp %>%
inner_join(dept, by = c("DEPTNO"="DEPTNO")) %>%
select(DEPTNO,DNAME,EMPNO,ENAME)

Source: local data table [14 x 4]

   DEPTNO      DNAME EMPNO  ENAME
    (int)      (chr) (int)  (chr)
1      10 ACCOUNTING  7839   KING
2      10 ACCOUNTING  7782  CLARK
3      10 ACCOUNTING  7934 MILLER
4      20   RESEARCH  7566  JONES
5      20   RESEARCH  7788  SCOTT
# Next lines were deleted

Aggregate

El operador aggregate ejecuta operaciones sobre grupos, o sets, de datos.

SELECT SUM(sal) AS total_salary
FROM emp;

TOTAL_SALARY
------------
29025

emp %>%
summarise(total_salary = sum(SAL))

Source: local data table [1 x 1]

  total_salary
         (int)
1        29025

Este operador es especialmente importante en el análisis de datos porque permite acotar las operaciones a rangos o agrupaciones de los datos sobre los que estamos trabajando.

SELECT deptno,
       COUNT(empno) AS no_of_employees,
       SUM(sal) AS total_salary,
       AVG(sal) AS average_salary
FROM emp
GROUP BY deptno;

DEPTNO	NO_OF_EMPLOYEES	TOTAL_SALARY	AVERAGE_SALARY
------	---------------	------------	----------------------------------------
30	6		9400		1566.66666666666666666666666666666666667
20	5		10875		2175
10	3		8750		2916.66666666666666666666666666666666667

emp %>%
group_by(DEPTNO) %>%
summarise(no_of_employees = length(EMPNO),total_salary = sum(SAL), average_salary = mean(SAL))

Source: local data table [3 x 4]

  DEPTNO no_of_employees total_salary average_salary
   (int)           (int)        (int)          (dbl)
1     10               3         8750       2916.667
2     30               6         9400       1566.667
3     20               5        10875       2175.000

desc EMP

Oracle DB | Blockchain

Archivo por meses: abril 2016

HEXTOBLOB

Interactive Report + Column confirm button

Introducción

Paso 1: Preparamos el enlace

Paso 2: Control de la acción

Paso 3: Modificación de los datos

SQL vs R

Introducción

Preparación del entorno

Principios de SQL

Projection

Filter

Join

Aggregate

	Santiago Solá en PLS-00653: aggregate/table fun…
	Tratamiento de NIF e… en Tratamiento de NIF españo…

HEXTOBLOB

Comparte esto:

Me gusta esto:

Introducción

Paso 1: Preparamos el enlace

Paso 2: Control de la acción

Paso 3: Modificación de los datos

Comparte esto:

Me gusta esto:

Introducción

Preparación del entorno

Principios de SQL

Projection

Filter

Join

Aggregate

Comparte esto:

Me gusta esto: