Mis Presentaciones en el Oracle Open World 2014

Un post muy breve para informarles de las dos presentaciones que voy a realizar en el Oracle Open World 2014.

Session ID:         UGF4482
Session Title:     “Getting Started with SQL Pattern Matching in Oracle Database 12c
Venue / Room:  Moscone South – 301
Date and Time:  9/28/14, 13:30 – 14:15

Session ID:          CON4493
Session Title:      “Regular Expressions in Oracle Database 101″
Venue / Room:   Moscone South – 303
Date and Time:   10/2/14, 13:15 – 14:00

No olviden de confirmar que las presentaciones sean en los salones que indico porque a veces hacen cambios de última hora.

Espero verlos por allá!


Nuevo en Oracle 12c: Consultar el contenido de una colección tipo arreglo en programas PL/SQL

En versiones previas a Oracle 12c, un programa PL/SQL no podía ejecutar una sentencia SQL que hiciera referencia a una colección de tipo arreglo (también conocidos como tablas en memoria). Esto es lo que sucede si trato de hacerlo en Oracle 11g.

SQL> drop table test_array purge;

Table dropped.

SQL> create table test_array as
  2  select level num_col from dual
  3  connect by level <= 10;

Table created.

SQL> select * from test_array;

   NUM_COL
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10                                                                      

10 rows selected.

SQL> drop package PKG_TEST_ARRAY;

Package dropped.

SQL> create or replace package PKG_TEST_ARRAY as
  2
  3    type tab_num is table of number index by pls_integer;
  4
  5  end PKG_TEST_ARRAY;
  6  /

Package created.

SQL> declare
  2    my_array pkg_test_array.tab_num;
  3  begin
  4    for i in 1 .. 5 loop
  5      my_array(i) := i*2;
  6    end loop;
  7
  8    for i in (
  9              select num_col from test_array
 10              where num_col in (select * from table(my_array))
 11             )
 12    loop
 13      dbms_output.put_line(i.num_col);
 14    end loop;
 15  end;
 16  /
            where num_col in (select * from table(my_array))
                                                  *
ERROR at line 10:
ORA-06550: line 10, column 51:
PLS-00382: expression is of wrong type
ORA-06550: line 10, column 45:
PL/SQL: ORA-22905: cannot access rows from a non-nested table item
ORA-06550: line 9, column 13:
PL/SQL: SQL Statement ignored
ORA-06550: line 13, column 26:
PLS-00364: loop index variable 'I' use is invalid
ORA-06550: line 13, column 5:
PL/SQL: Statement ignored

Como pueden ver, el operador TABLE está esperando una colección de tipo nested table o varray.

Esta limitación ha sido removida en Oracle 12c. Ahora el bloque PL/SQL se ejecuta sin problemas.

SQL> set serveroutput on
SQL>
SQL> drop table test_array purge;

Table dropped.

SQL> create table test_array as
  2  select level num_col from dual
  3  connect by level <= 10;

Table created.

SQL> select * from test_array;

   NUM_COL
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10                                                                      

10 rows selected.

SQL> drop package PKG_TEST_ARRAY;

Package dropped.

SQL> create or replace package PKG_TEST_ARRAY as
  2
  3    type tab_num is table of number index by pls_integer;
  4
  5  end PKG_TEST_ARRAY;
  6  /

Package created.

SQL> declare
  2    my_array pkg_test_array.tab_num;
  3  begin
  4    for i in 1 .. 5 loop
  5      my_array(i) := i*2;
  6    end loop;
  7
  8    for i in (
  9              select num_col from test_array
 10              where num_col in (select * from table(my_array))
 11             )
 12    loop
 13      dbms_output.put_line(i.num_col);
 14    end loop;
 15  end;
 16  /

2
4
6
8
10                                                                              

PL/SQL procedure successfully completed.

Otro ejemplo utilizando un join entre una tabla y la colección.

SQL> declare
  2    my_array pkg_test_array.tab_num;
  3  begin
  4    for i in 1 .. 5 loop
  5      my_array(i) := i*2;
  6    end loop;
  7
  8    for i in (
  9              select a.num_col, b.column_value
 10              from
 11                test_array a,
 12                table (my_array) b
 13              where
 14                a.num_col = b.column_value
 15             )
 16    loop
 17      dbms_output.put_line(i.num_col);
 18    end loop;
 19  end;
 20  /

2
4
6
8
10                                                                              

PL/SQL procedure successfully completed.

Muy interesante.


SQL Developer y la interfaz para trabajar con GIT: Interactuando con un repositorio GitHub Parte 2

En este post voy a mostrar la manera de sincronizar los repositorios remoto (GitHub) y local (PC) una vez que un archivo existente en local es modificado. Lo que voy a hacer es modificar el archivo sp_test_git.pls que se encuentra en el repositorio local y luego voy a transferir los cambios al repositorio remoto.

En primer lugar debo abrir el archivo sp_test_git.pls en SQL Developer, luego agrego otra línea dbms_output y finalmente guardo los cambios. En el momento que el archivo es modificado, la ventana Pending Changes (Git) se actualiza para reflejar los cambios y los íconos de la ventana se activan.

modify_file

En la ventana  Pending Changes (Git) puedo añadir un comentario y luego transferir el archivo a un área temporal (staging area) haciendo click en el botón Add. Algo que hay que notar es el cambio en el status del archivo que pasó de “Modified Not Staged” a “Modified Staged”.

staged_file

¿Qué hago si deseo si deseo comparar las versiones del archivo antes de confirmar los cambios en el repositorio local? Solamente debo hacer click en el ícono Compare with Previous Version que se encuentra en la ventana Pending Changes (Git).

compare2

El panel de la izquierda muestra la versión almacenada en el repositorio local y el panel de la derecha muestra la versión almacenada en el área temporal. La diferencia entre las dos versiones se encuentra resaltada para facilitar su identificación.

El siguiente paso es confirmar los cambios en el repositorio local. Para ello, debo hacer click en el botón Commit que se encuentra en la ventana Pending Changes (Git) y luego debo hacer click en el botón OK de la ventana Commit.

commit

Ahora la ventana Branch Compare muestra información indicando que los repositorios remoto y local no se encuentran sincronizados.

branch_compare2

El paso final consiste en sincronizar los repositorios remoto y local enviando los cambios a GitHub. Para lograrlo, voy al menú principal y hago click en  Team -> Git -> Push para abrir el asistente “Push to Git” donde ingreso el URL del repositorio remoto, el user name y el password para completar la operación. Luego voy a GitHub para asegurarme que los cambios fueron transferidos.

updated_github

 


SQL Developer y la interfaz para trabajar con GIT: Interactuando con un repositorio GitHub Parte 1

En mi último post mostré como clonar un repositorio GitHub usando SQL Developer. En este post voy a mostrar como sincronizar los repositorios remoto (GitHub) y local (PC) una vez que remoto ha sido modificado.

En primer lugar voy a utilizar la funcionalidad existente en GitHub para crear el archivo sp_test_git.pls y luego voy a actualizar el repositorio. Se puede crear un nuevo archivo haciendo click en el ícono señalado con la flecha roja.

new_file

El contenido del archivo es un procedimiento PL/SQL que muestra un mensaje.

file_content

En este momento los repositorios remoto y local ya no se encuentran sincronizados. Lo primero que se debemos hacer antes de empezar a trabajar con un repositorio es asegurarnos de que tenemos la versión más reciente del mismo, de tal forma que incluya todos los cambios realizados por otros desarrolladores. Procedamos a sincronizar remoto y local.

En primer lugar debemos asegurarnos de que la ventana Versions se encuentre abierta. Vamos al menú principal y hacemos click en Team -> Versions.

versions

Luego debemos recorrer la estructura del repositorio local hasta llegar a la derivación máster y la seleccionamos. Ahora vamos al menú principal y hacemos click en Team -> Git -> Fetch para abrir el asistente “Fetch from Git” y seguimos las instrucciones que son similares a a las que utilizamos para clonar el repositorio. Cuando se realiza una operación Fetch sobre un repositorio, lo que realmente ocurre es que se guarda una copia de los cambios del repositorio remoto en una ubicación temporal (en el PC) de manera que no se modifica ninguna de las derivaciones en el repositorio local. En ese momento tenemos la opción para solamente comparar los cambios (Compare) o también podemos incorporar los cambios (Merge) en el repositorio local. Los archivos que han cambiado se muestran en la ventana Branch Compare que aparece cuando vamos al menú principal y hacemos click en Team -> Git -> Branch Compare.

branch_compare

En la ventana Branch Compare podemos hacer click con el botón derecho sobre sp_test_git.pls y elegir Compare para ver las diferencias entre la versión remota y local de sp_test_git.pls.

compare

La ventana del lado izquierdo muestra la versión remota del archivo y la ventana de la derecha muestra la versión local. En este caso la ventana de la derecha se encuentra vacía ya que el archivo es nuevo y no existe una copia previa en el repositorio local. Podemos incorporar los cambios al repositorio local en forma definitiva haciendo click con el botón derecho sobre sp_test_git.pls, eligiendo Merge y haciendo click sobre el botón “Ok”.

merge

Ahora podemos ir al directorio que contiene nuestro repositorio local y confirmar que sp_test_git.pls se encuentra ahí.

local_update

 


SQL Developer y la interfaz para trabajar con GIT: Clonando un repositorio GitHub

SQL Developer 4 provee una interfaz que nos permite interactuar con repositorios Git. En este post voy a mostrar una manera de clonar un repositorio almacenado en GitHub (Un servicio de hosting basado en la web para proyectos de desarrollo de software que utiliza el sistema de control de versiones Git).

Lo primero que debemos hacer es crear una cuenta en GitHub. Obviamente podemos ir al siguiente paso si es que ya contamos con una cuenta.

La cuenta en GitHub nos da acceso a repositorios públicos que podríamos clonar pero les recomiendo crear un repositorio propio para poder probar las diferentes funcionalidades que se incluyen en SQL Developer.

Una vez que la cuenta ha sido creada, debemos hacer click en el botón verde que dice “New Repository” que nos lleva a una página que luce así.

 

github_create_repo

 

Aquí debemos ingresar el nombre del repositorio que vamos a crear, decidir si el repositorio va a ser público (gratis) o privado (pagado) y hacer click en el check box. Finalmente hacemos click en el botón verde que dice “Create Repository” y eso nos lleva a la página principal de nuestro nuevo repositorio que debería ser similar a la siguiente imagen.

 

github_repo

 

Aquí hay que prestar atención a la flecha roja en la esquina inferior derecha. EL HTTPS clone URL es algo que vamos a necesitar cuando configuremos el acceso a GitHub en SQL Developer.

Ahora vamos a realizar la configuración de la conexión a GitHub en SQL Developer. En el menú principal hacemos click en Team –> Git –> Clone… para abrir el asistente “Clone from Git”. Aquí hacemos click en el botón que dice “Next” y que nos lleva a la pantalla para ingresar los detalles del repositorio que deseamos clonar.

 

remote_repo

 

Aquí debemos ingresar el nombre del repositorio que creamos, el HTTPS clone URL, el user name y password de nuestra cuenta GitHub. Luego hacemos click en el botón “Next” para tratar de establecer la conexión con el repositorio. Si la conexión fue exitosa, nos aparece la pantalla para elegir la derivación (branch) que deseamos clonar de lo contrario nos aparecerá algún mensaje de error.

 

remote_branch

 

Aquí solamente tenemos disponible la derivación master que se incluye por default cada vez que creamos un repositorio. Entonces hacemos click en el botón que dice “Next” y eso nos lleva a la pantalla donde debemos especificar el directorio en nuestra computadora que va a almacenar la copia local del repositorio Git.

 

destination

 

Ahora debemos especificar la ruta para almacenar el repositorio local y luego hacemos click en el botón “Next”. La última pantalla muestra un resumen de nuestra configuración. Aquí hacemos click en el botón “Finish” para completar el proceso.

Ahora la pregunta es ¿cómo sabemos si todo está funcionando correctamente? Si vamos a la ubicación que especificamos para almacenar nuestro repositorio local, deberíamos encontrar la misma estructura de carpetas y archivos que se muestran en la página principal de nuestro repositorio en GitHub.

 

local_repo

 

En otro post voy a mostrar la manera en que podemos hacer cambios en el repositorio local y la manera de sincronizar los cambios con el repositorio en GitHub.


¿Cómo habilitar el depurador PL/SQL de SQL Developer para trabajar con Oracle 12c?

SQL Developer posee un depurador de código PL / SQL que no necesita mayor configuración si se lo utiliza con Oracle 11g u otra versión previa. Normalmente necesitamos asegurarnos de que los siguientes pasos han sido ejecutados antes de intentar usar el depurador:

  1. Otorgar el privilegio DEBUG CONNECT SESSION al schema que contiene el código PL/SQL.
  2. Otorgar el privilegio de ejecución (EXECUTE) sobre el paquete DBMS_DEBUG_JDWP al schema que contienen el código PL/SQL.
  3. Verificar que tenemos el privilegio de ejecución (EXECUTE) sobre el código PL/SQL (paquete, procedimiento, función) que se desea depurar.
  4.  Verificar que el código PL/SQL se encuentra compilado en “modo debug” .

Si desean más información sobre los cuatro pasos que acabo de mencionar y cómo utilizar el depurador, pueden visitar el blog de Jeff Smith (PM de SQL Developer) que tiene una publicación sobre estos temas.

Ahora veamos lo que sucede cuando el código PL/SQL que deseamos depurar se encuentra en Oracle 12c. Los cuatro pasos que mencioné previamente todavía son necesarios pero ya no son suficientes. Miren el error que me apareció cuando intenté usar el depurador .

Executing PL/SQL: CALL DBMS_DEBUG_JDWP.CONNECT_TCP( ‘192.168.0.10’, ‘49428’ )
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at “SYS.DBMS_DEBUG_JDWP”, line 68
ORA-06512: at line 1
Process exited.
Disconnecting from the database SCOTT – ORA12CPDB1

A partir de Oracle 12c , si deseamos usar un depurador de código PL/SQL como el que se incluye en SQL Developer o JDeveloper y que implementa el Java Debug Wire Protocol (JDWP), es necesario otorgar el privilegio de control de acceso (ACL) JDWP para permitir que la sesión de la base de datos se comunique con el depurador.

A continuación una manera de configurar el acceso de red para operaciones JDWP :

BEGIN
 DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE
 (
 host => '192.168.0.10',
 lower_port => null,
 upper_port => null,
 ace => xs$ace_type(privilege_list => xs$name_list('jdwp'),
 principal_name => 'scott',
 principal_type => xs_acl.ptype_db)
 );
END;

Host (donde se está ejecutando el depurador) puede puede ser un nombre de host, nombre de dominio, dirección IP o subred.

Principal_name en sección la entrada de control de acceso (ACE ) es el schema que contiene el código PL/SQL que se desea depurar.

Una vez que ejecutamos el bloque PL/SQL que configura el acceso de red para operaciones JDWP, el error desaparece y se puede empezar a utilizar el depurador como se muestra a continuación.

Connecting to the database SCOTT – ORA12CPDB1.
Executing PL/SQL: ALTER SESSION SET PLSQL_DEBUG=TRUE
Executing PL/SQL: CALL DBMS_DEBUG_JDWP.CONNECT_TCP( ‘192.168.0.10’, ‘49428’ )
Debugger accepted connection from database on port 49428.
Executing PL/SQL: CALL DBMS_DEBUG_JDWP.DISCONNECT()
Inside the Procedure
Process exited.
Disconnecting from the database SCOTT – ORA12CPDB1.
Debugger disconnected from database.

Espero que les sirva cuando sea tiempo de migrar a Oracle 12c .


Expresiones de Tablas Comunes Recursivas al Rescate

Traducción del post original publicado en Noviembre 27 2013.

Las expresiones de tablas comunes recursivas han estado disponibles en Oracle desde la versión 11g R2 que fue lanzada por allá en el año 2009. Siempre las había considerado como una alternativa a la cláusula connect by para poder escribir consultas jerárquicas que siguieran el estándar ANSI, así que nunca me había tomado la molestia de revisar su funcionalidad en detalle.

Durante Kscope13, tuve la oportunidad de asistir a una sesión de Dominic Delmolino donde él mostró que las expresiones de tablas comunes recursivas pueden ser utilizadas para resolver otros tipos de problemas. Uno de los casos justamente es cuando necesitamos “generar un valor” y luego queremos hacer referencia a dicho valor en el siguiente registro de nuestra consulta.

No hace mucho tuve que trabajar en una consulta con un requerimiento similar. La idea básica es que tenemos un número variable de registros por cada cliente y para cada registro debemos establecer o calcular un nuevo valor (a la cual vamos a llamar VAL). La parte interesante está en que para algunos registros, VAL debe ser determinado utilizando el VAL que se encuentra en el registro anterior.

En primer lugar voy a crear una tabla y voy a insertar unos registros que ayuden a visualizar el problema.


SQL> CREATE TABLE RSF_TEST
 2 (
 3 ID_CLIENT NUMBER NOT NULL,
 4 DT_ELIG DATE NOT NULL,
 5 TP NUMBER NOT NULL,
 6 SRV_GRP NUMBER NOT NULL,
 7 SRV_CODE NUMBER NOT NULL,
 8 DT_ADMIT DATE NOT NULL,
 9 DT_DISCHARGE DATE NOT NULL
 10 );

Table created

SQL> insert into RSF_TEST (ID_CLIENT, DT_ELIG, TP, SRV_GRP, SRV_CODE, DT_ADMIT, DT_DISCHARGE)
 2 values (525459313, to_date('01-11-2013', 'dd-mm-yyyy'), 13, 19, 12, to_date('01-05-2013', 'dd-mm-yyyy'), to_date('31-12-2013', 'dd-mm-yyyy'));
1 row inserted

SQL> insert into RSF_TEST (ID_CLIENT, DT_ELIG, TP, SRV_GRP, SRV_CODE, DT_ADMIT, DT_DISCHARGE)
 2 values (615086545, to_date('01-11-2013', 'dd-mm-yyyy'), 13, 19, 12, to_date('07-03-2013', 'dd-mm-yyyy'), to_date('31-03-2014', 'dd-mm-yyyy'));
1 row inserted

SQL> insert into RSF_TEST (ID_CLIENT, DT_ELIG, TP, SRV_GRP, SRV_CODE, DT_ADMIT, DT_DISCHARGE)
 2 values (525459313, to_date('01-10-2013', 'dd-mm-yyyy'), 13, 19, 12, to_date('01-05-2013', 'dd-mm-yyyy'), to_date('31-12-2013', 'dd-mm-yyyy'));
1 row inserted

SQL> insert into RSF_TEST (ID_CLIENT, DT_ELIG, TP, SRV_GRP, SRV_CODE, DT_ADMIT, DT_DISCHARGE)
 2 values (615086545, to_date('01-10-2013', 'dd-mm-yyyy'), 13, 19, 12, to_date('07-03-2013', 'dd-mm-yyyy'), to_date('31-03-2014', 'dd-mm-yyyy'));
1 row inserted

SQL> insert into RSF_TEST (ID_CLIENT, DT_ELIG, TP, SRV_GRP, SRV_CODE, DT_ADMIT, DT_DISCHARGE)
 2 values (615086545, to_date('01-09-2013', 'dd-mm-yyyy'), 13, 19, 12, to_date('07-03-2013', 'dd-mm-yyyy'), to_date('31-03-2014', 'dd-mm-yyyy'));
1 row inserted

SQL> insert into RSF_TEST (ID_CLIENT, DT_ELIG, TP, SRV_GRP, SRV_CODE, DT_ADMIT, DT_DISCHARGE)
 2 values (525459313, to_date('01-09-2013', 'dd-mm-yyyy'), 13, 19, 12, to_date('01-05-2013', 'dd-mm-yyyy'), to_date('31-12-2013', 'dd-mm-yyyy'));
1 row inserted

SQL> insert into RSF_TEST (ID_CLIENT, DT_ELIG, TP, SRV_GRP, SRV_CODE, DT_ADMIT, DT_DISCHARGE)
 2 values (525459313, to_date('01-08-2013', 'dd-mm-yyyy'), 13, 19, 12, to_date('01-05-2013', 'dd-mm-yyyy'), to_date('31-12-2013', 'dd-mm-yyyy'));
1 row inserted

SQL> insert into RSF_TEST (ID_CLIENT, DT_ELIG, TP, SRV_GRP, SRV_CODE, DT_ADMIT, DT_DISCHARGE)
 2 values (615086545, to_date('01-08-2013', 'dd-mm-yyyy'), 13, 19, 12, to_date('07-03-2013', 'dd-mm-yyyy'), to_date('31-03-2014', 'dd-mm-yyyy'));
1 row inserted

SQL> insert into RSF_TEST (ID_CLIENT, DT_ELIG, TP, SRV_GRP, SRV_CODE, DT_ADMIT, DT_DISCHARGE)
 2 values (615086545, to_date('01-07-2013', 'dd-mm-yyyy'), 13, 19, 12, to_date('07-03-2013', 'dd-mm-yyyy'), to_date('31-03-2014', 'dd-mm-yyyy'));
1 row inserted

SQL> insert into RSF_TEST (ID_CLIENT, DT_ELIG, TP, SRV_GRP, SRV_CODE, DT_ADMIT, DT_DISCHARGE)
 2 values (525459313, to_date('01-07-2013', 'dd-mm-yyyy'), 13, 19, 12, to_date('01-05-2013', 'dd-mm-yyyy'), to_date('31-12-2013', 'dd-mm-yyyy'));
1 row inserted

SQL> insert into RSF_TEST (ID_CLIENT, DT_ELIG, TP, SRV_GRP, SRV_CODE, DT_ADMIT, DT_DISCHARGE)
 2 values (525459313, to_date('01-06-2013', 'dd-mm-yyyy'), 13, 19, 12, to_date('01-05-2013', 'dd-mm-yyyy'), to_date('31-12-2013', 'dd-mm-yyyy'));
1 row inserted

SQL> insert into RSF_TEST (ID_CLIENT, DT_ELIG, TP, SRV_GRP, SRV_CODE, DT_ADMIT, DT_DISCHARGE)
 2 values (615086545, to_date('01-06-2013', 'dd-mm-yyyy'), 13, 19, 12, to_date('07-03-2013', 'dd-mm-yyyy'), to_date('31-03-2014', 'dd-mm-yyyy'));
1 row inserted

SQL> insert into RSF_TEST (ID_CLIENT, DT_ELIG, TP, SRV_GRP, SRV_CODE, DT_ADMIT, DT_DISCHARGE)
 2 values (525459313, to_date('01-05-2013', 'dd-mm-yyyy'), 13, 19, 12, to_date('01-05-2013', 'dd-mm-yyyy'), to_date('31-12-2013', 'dd-mm-yyyy'));
1 row inserted

SQL> insert into RSF_TEST (ID_CLIENT, DT_ELIG, TP, SRV_GRP, SRV_CODE, DT_ADMIT, DT_DISCHARGE)
 2 values (615086545, to_date('01-05-2013', 'dd-mm-yyyy'), 13, 19, 12, to_date('07-03-2013', 'dd-mm-yyyy'), to_date('31-03-2014', 'dd-mm-yyyy'));
1 row inserted

SQL> insert into RSF_TEST (ID_CLIENT, DT_ELIG, TP, SRV_GRP, SRV_CODE, DT_ADMIT, DT_DISCHARGE)
 2 values (525459313, to_date('01-04-2013', 'dd-mm-yyyy'), 13, 19, 12, to_date('12-12-2012', 'dd-mm-yyyy'), to_date('30-04-2013', 'dd-mm-yyyy'));
1 row inserted

SQL> insert into RSF_TEST (ID_CLIENT, DT_ELIG, TP, SRV_GRP, SRV_CODE, DT_ADMIT, DT_DISCHARGE)
 2 values (615086545, to_date('01-04-2013', 'dd-mm-yyyy'), 13, 19, 12, to_date('07-03-2013', 'dd-mm-yyyy'), to_date('31-03-2014', 'dd-mm-yyyy'));
1 row inserted

SQL> insert into RSF_TEST (ID_CLIENT, DT_ELIG, TP, SRV_GRP, SRV_CODE, DT_ADMIT, DT_DISCHARGE)
 2 values (615086545, to_date('01-03-2013', 'dd-mm-yyyy'), 13, 19, 12, to_date('07-03-2013', 'dd-mm-yyyy'), to_date('31-03-2014', 'dd-mm-yyyy'));
1 row inserted

SQL> insert into RSF_TEST (ID_CLIENT, DT_ELIG, TP, SRV_GRP, SRV_CODE, DT_ADMIT, DT_DISCHARGE)
 2 values (525459313, to_date('01-03-2013', 'dd-mm-yyyy'), 13, 19, 12, to_date('12-12-2012', 'dd-mm-yyyy'), to_date('30-04-2013', 'dd-mm-yyyy'));
1 row inserted

SQL> insert into RSF_TEST (ID_CLIENT, DT_ELIG, TP, SRV_GRP, SRV_CODE, DT_ADMIT, DT_DISCHARGE)
 2 values (525459313, to_date('01-02-2013', 'dd-mm-yyyy'), 13, 19, 12, to_date('12-12-2012', 'dd-mm-yyyy'), to_date('30-04-2013', 'dd-mm-yyyy'));
1 row inserted

SQL> insert into RSF_TEST (ID_CLIENT, DT_ELIG, TP, SRV_GRP, SRV_CODE, DT_ADMIT, DT_DISCHARGE)
 2 values (525459313, to_date('01-01-2013', 'dd-mm-yyyy'), 13, 19, 12, to_date('12-12-2012', 'dd-mm-yyyy'), to_date('30-04-2013', 'dd-mm-yyyy'));
1 row inserted

SQL> insert into RSF_TEST (ID_CLIENT, DT_ELIG, TP, SRV_GRP, SRV_CODE, DT_ADMIT, DT_DISCHARGE)
 2 values (525459313, to_date('01-12-2012', 'dd-mm-yyyy'), 13, 19, 12, to_date('12-12-2012', 'dd-mm-yyyy'), to_date('30-04-2013', 'dd-mm-yyyy'));
1 row inserted

SQL> insert into RSF_TEST (ID_CLIENT, DT_ELIG, TP, SRV_GRP, SRV_CODE, DT_ADMIT, DT_DISCHARGE)
 2 values (525459313, to_date('01-03-2012', 'dd-mm-yyyy'), 13, 1, 1, to_date('18-01-2012', 'dd-mm-yyyy'), to_date('29-03-2012', 'dd-mm-yyyy'));
1 row inserted

SQL> commit;

Commit complete

SQL>

A continuación tenemos una versión simplificada del problema. La consulta original contiene más predicados y las sentencias CASE son un poco más complejas ya que deben cubrir una serie de posibles combinaciones de las columnas srv_grp, srv_code. Traté de limitar el código de forma que únicamente incluya lo necesario para mostrar cómo determinar VAL para los registros que existen en mi tabla.


WITH
clients AS
(
 SELECT
   id_client,
   dt_elig,
   tp,
   srv_grp,
   srv_code,
   dt_admit,
   dt_discharge,
   row_number() OVER (PARTITION BY id_client ORDER BY id_client, dt_elig) rn
 FROM rsf_test
),
results (id_client, dt_elig, tp, srv_grp, srv_code, dt_admit, dt_discharge, rn, val) AS
(
 SELECT
   id_client,
   dt_elig,
   tp,
   srv_grp,
   srv_code,
   dt_admit,
   dt_discharge,
   rn,
   CASE
     WHEN tp <> 14 AND srv_grp = 19 AND srv_code = 12 AND dt_elig BETWEEN dt_admit AND dt_discharge THEN 3
     WHEN tp <> 14 AND srv_grp = 19 AND srv_code = 12 AND dt_elig NOT BETWEEN dt_admit AND dt_discharge THEN 0
     WHEN tp <> 14 AND srv_grp IN (1,10) AND srv_code IN (1,3) THEN 0
   END val
 FROM clients
 WHERE
   rn = 1
 UNION ALL
 SELECT
   c.id_client,
   c.dt_elig,
   c.tp,
   c.srv_grp,
   c.srv_code,
   c.dt_admit,
   c.dt_discharge,
   c.rn,
   CASE
     WHEN c.tp <> 14 AND c.srv_grp = 19 AND c.srv_code = 12 AND p.val = 3 AND c.dt_elig BETWEEN c.dt_admit AND c.dt_discharge THEN 3
     WHEN c.tp <> 14 AND c.srv_grp = 19 AND c.srv_code = 12 AND p.val < 3 AND c.dt_elig BETWEEN c.dt_admit AND c.dt_discharge THEN p.val + 1
     WHEN c.tp <> 14 AND c.srv_grp IN (1,10) AND c.srv_code IN (1,3) AND p.val = 3 THEN 3
     WHEN c.tp <> 14 AND c.srv_grp IN (1,10) AND c.srv_code IN (1,3) AND p.val < 3 THEN p.val
     WHEN c.tp <> 14 AND c.srv_grp = 19 AND c.srv_code = 12 AND p.val = 3 AND c.dt_elig NOT BETWEEN c.dt_admit AND c.dt_discharge THEN 3
     WHEN c.tp <> 14 AND c.srv_grp = 19 AND c.srv_code = 12 AND p.val < 3 AND c.dt_elig NOT BETWEEN c.dt_admit AND c.dt_discharge THEN p.val
   END val
 FROM
   clients c,
   results p
 WHERE
   c.id_client = p.id_client
   AND c.rn = p.rn + 1
)
SELECT
  r.id_client,
  r.dt_elig,
  r.tp,
  r.srv_grp,
  r.srv_code,
  r.dt_admit,
  r.dt_discharge,
  --r.rn,
  r.val
FROM results r
ORDER BY
  r.id_client,
  r.dt_elig;

Como pueden ver, las expresiones de tablas comunes recursivas contienen dos consultas: La consulta anchor que sirve para generar el primer registro que luego es procesado por la consulta recursiva. Las dos consultas son combinadas mediante el operador UNION ALL.

Ahora veamos el resultado una vez que se ejecuta la consulta:


 ID_CLIENT DT_ELIG           TP    SRV_GRP   SRV_CODE DT_ADMIT  DT_DISCHA        VAL
---------- --------- ---------- ---------- ---------- --------- --------- ----------
 525459313 01-MAR-12         13          1          1 18-JAN-12 29-MAR-12          0
 525459313 01-DEC-12         13         19         12 12-DEC-12 30-APR-13          0
 525459313 01-JAN-13         13         19         12 12-DEC-12 30-APR-13          1
 525459313 01-FEB-13         13         19         12 12-DEC-12 30-APR-13          2
 525459313 01-MAR-13         13         19         12 12-DEC-12 30-APR-13          3
 525459313 01-APR-13         13         19         12 12-DEC-12 30-APR-13          3
 525459313 01-MAY-13         13         19         12 01-MAY-13 31-DEC-13          3
 525459313 01-JUN-13         13         19         12 01-MAY-13 31-DEC-13          3
 525459313 01-JUL-13         13         19         12 01-MAY-13 31-DEC-13          3
 525459313 01-AUG-13         13         19         12 01-MAY-13 31-DEC-13          3
 525459313 01-SEP-13         13         19         12 01-MAY-13 31-DEC-13          3
 525459313 01-OCT-13         13         19         12 01-MAY-13 31-DEC-13          3
 525459313 01-NOV-13         13         19         12 01-MAY-13 31-DEC-13          3
 615086545 01-MAR-13         13         19         12 07-MAR-13 31-MAR-14          0
 615086545 01-APR-13         13         19         12 07-MAR-13 31-MAR-14          1
 615086545 01-MAY-13         13         19         12 07-MAR-13 31-MAR-14          2
 615086545 01-JUN-13         13         19         12 07-MAR-13 31-MAR-14          3
 615086545 01-JUL-13         13         19         12 07-MAR-13 31-MAR-14          3
 615086545 01-AUG-13         13         19         12 07-MAR-13 31-MAR-14          3
 615086545 01-SEP-13         13         19         12 07-MAR-13 31-MAR-14          3
 615086545 01-OCT-13         13         19         12 07-MAR-13 31-MAR-14          3
 615086545 01-NOV-13         13         19         12 07-MAR-13 31-MAR-14          3

22 rows selected.

En éste ejemplo en particular, la consulta anchor asigna ya sea un 0 o un 3 para VAL en el primer registro y de ahí la consulta recursiva evalúa si VAL debería incrementarse o permanecer estático en cada uno de los siguientes registros. La idea es tener acceso a VAL de la iteración previa para poder determinar el nuevo valor en la iteración actual.


Seguir

Recibe cada nueva publicación en tu buzón de correo electrónico.