El como realizar un autoincremental en oracle del que platique en mi post anterior surgió más bien de la necesidad de obtener ese mismo número al realizar un “insert” a la tabla, pero aproveche para hablar específicamente de el sin mezclar temas.

Ya platicaba en ese post de que al hacer el insert ya no es necesario especificar el campo del identificador por que el trigger lo generaría automáticamente, pero entonces ahora surge el problema, que pasa si en realidad se necesita durante el proceso conocer ese número ya que, por ejemplo, se trata de una tabla “maestro” y después se inserta en otra tabla “detalle” otra información incluyendo ese identificador único.

Mi respuesta hasta ayer hubiera sido realizar otro “select” a la tabla obteniendo el ultimo identificador generado, o de menos, que el mismo proceso realice la misma acción del trigger para obtener el identificador, ninguna de las dos es muy limpia solución que digamos, pero no se me ocurría otra manera de hacerlo hasta hoy.

El día de hoy me comentaban de una nueva funcionalidad, al menos para mi (no se exactamente en que versión de Oracle fue introducida) que te permite obtener el valor de los campos de una tabla después de un DML (insert, delete, update) sin necesidad de generar otra consulta.

Para este ejemplo utilizare la tabla “employees” que generé en el post anterior por lo que regresen al post anterior antes de comenzar a probar, considerando que ya se tiene esa tabla creada, con su llave primaria y su trigger “emulador” de autoincremental veamos el ejemplo del returning.

En este ejemplo se utilizo un “type” aunque en el segundo ejemplo se ve que no es necesario, todo depende de la necesidad en especifico, al realizar el insert junto con la cláusula “returning into” se obtiene el valor que se genero para el identificador sin necesidad de otra cosa:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

SQL> DECLARE
  2  
  3     TYPE EmpRec IS RECORD (employee_id   employees.employee_id%TYPE);
  4  
  5     emp_info EmpRec;
  6  
  7  BEGIN
  8  
  9     INSERT INTO employees VALUES (NULL,'otro mas','otro',6000)
 10        RETURNING employee_id INTO emp_info;
 11  
 12     DBMS_OUTPUT.PUT_LINE('Se inserto nuevo registro con id ' || emp_info.employee_id);
 13  
 14  
 15  END;
 16  /
Se inserto nuevo registro con id 1
 
PL/SQL PROCEDURE successfully completed.

Este ejemplo es utilizando directamente el “%type”:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

SQL> DECLARE
  2  
  3     emp_info employees.employee_id%TYPE;
  4  
  5  BEGIN
  6  
  7     INSERT INTO employees VALUES (NULL,'otro mas','otro',6000)
  8        RETURNING employee_id INTO emp_info;
  9  
 10     DBMS_OUTPUT.PUT_LINE('Se inserto nuevo registro con id ' || emp_info);
 11  
 12  
 13  END;
 14  /
Se inserto nuevo registro con id 2
 
PL/SQL PROCEDURE successfully completed.

Y solo para probar que todo funcionó correctamente:

1
2
3
4
5
6

SQL> SELECT * FROM employees;
 
EMPLOYEE_ID FIRTS_NAME           LAST_NAME                SALARY
----------- -------------------- -------------------- ----------
          1 otro mas             otro                       6000
          2 otro mas             otro                       6000

Hace algún tiempo me preguntaban que como se realizaba un autoincremental en Oracle, para lo que saben poco, no es más que un atributo que se utiliza para generar un identificador único para cada nueva fila de una tabla, yo se que existe en Mysql simplemente especificando “AUTO_INCREMENT” en el campo al crear la tabla, generalmente en su llave primaria.

Mi respuesta fue, que hasta donde yo se, no existe este atributo en Oracle, aunque con un pequeño trigger se puede “emular” fácilmente la lógica de este atributo en oracle, pero veamos como, claro con un ejemplo sencillo para los menos versados en el tema.

Primero creamos la clásica tabla de pruebas llamada “employees”:

1
2
3
4
5

CREATE TABLE employees (
  employee_id      NUMBER  NOT NULL,
  firts_name       VARCHAR2(20),
  last_name        VARCHAR2(20),
  salary           NUMBER);

Le creamos su llave primaria en el campo “employee_id”:

1

ALTER TABLE employees add constraint "PKemployees" primary key (employee_id);

Creamos el susodicho trigger que generara automáticamente el identificador para cada nueva fila, por supuesto, también lo pudimos haber realizado en base a una secuencia pero este caso, por facilidad, lo crearemos con un simple max + 1:

1
2
3
4
5
6
7
8
9
10
11
12

CREATE OR REPLACE TRIGGER tr_pk_employees 
  before INSERT ON employees
  FOR each ROW
DECLARE
 
BEGIN
 
  SELECT NVL(MAX(employee_id),0) + 1
    INTO :NEW.employee_id
    FROM employees;
 
END;

Es todo, ahora al realizar un insert a la tabla solo necesitamos los valores para los demás campos, si lo queremos hacer sin especificar campos tendremos que ponerle “null” o en su defecto doble comilla simple:

1
2
3

INSERT INTO employees VALUES (NULL, 'pancho',  'lopez',1000);
INSERT INTO employees VALUES (NULL,   'juan',  'penas',2000);
INSERT INTO employees VALUES (NULL,  'nacho','vazquez',3000);

O especificando los campos sin necesidad del campo “employee_id”:

1
2
3

INSERT INTO employees (firts_name, last_name, salary) VALUES ('guicho','sanchez',4000);
INSERT INTO employees (firts_name, last_name, salary) VALUES ('rolando', 'calles',5000);
INSERT INTO employees (firts_name, last_name, salary) VALUES ('juan','gabriel',6000);

Y para corroborar que todo funcionó correctamente, revisamos los datos:

1
2
3
4
5
6
7
8
9
10

SQL> SELECT * FROM employees;
 
EMPLOYEE_ID FIRTS_NAME           LAST_NAME                SALARY
----------- -------------------- -------------------- ----------
          1 pancho               lopez                      1000
          2 juan                 penas                      2000
          3 nacho                vazquez                    3000
          4 guicho               sanchez                    4000
          5 rolando              calles                     5000
          6 juan                 gabriel                    6000

Hace algún tiempo recuerdo haber necesitado generar una contraseña aleatoria en Oracle pero al no encontrar una solución fácil nos fuimos por ponerle de contraseña el mismo nombre de usuario, o sea, si era el usuario “fulanito” le pone la contraseña “fulanito”, esto desde un modulo clásico de “olvide mi contraseña”.

Hoy buscando en Internet la solución para otro problema me tope con el paquete dbms_random de oracle, que entre sus funcionalidades esta la generación de una cadena con caracteres aleatorios, hubiera quedado perfecto para el problema que comento, y no solo sirve para eso, sirve también para generar números aleatorios, pudiendo seleccionar el rango en el que estén esos números.

Pero la opción que considero mas importante es que incluso lo puedo utilizar para seleccionar algún registro aleatorio de una tabla. Me recuerda el “ORDER BY RAND()” de Mysql, aunque no es tan “natural” acá en Oracle como lo es en Mysql, les dejo unos ejemplos de su uso, seguro a mas de uno le ayudara a resolver algo.

Una simple consulta para generar un número aleatorio:

1
2
3
4
5

SQL> SELECT DBMS_RANDOM.random FROM dual;
 
    RANDOM
----------
-267030442

Pero resulta que nos puede generar números negativos, fácil, usamos el abs:

1
2
3
4
5

SQL> SELECT ABS(DBMS_RANDOM.random) FROM dual;
 
ABS(DBMS_RANDOM.RANDOM)
-----------------------
              705640851

Bueno pero queremos especificar el rango en el que estén esos números:

1
2
3
4
5

SQL> SELECT DBMS_RANDOM.VALUE(1,10) FROM dual;
 
DBMS_RANDOM.VALUE(1,10)
-----------------------
             4.41966315

Pero resulta que me regresa números con decimales, fácil, usamos el round:

1
2
3
4
5

SQL> SELECT ROUND(DBMS_RANDOM.VALUE(1,10)) FROM dual;
 
ROUND(DBMS_RANDOM.VALUE(1,10))
------------------------------
                             4

Podemos generar una cadena aleatoria:

1
2
3
4
5

SQL> SELECT DBMS_RANDOM.string('A', 12) FROM dual;
 
DBMS_RANDOM.STRING('A',12)
--------------------------------------------------
mQKXSkedaGpK

Aquí la explicación del primer parámetro, el segundo solo es la longitud de la cadena:

1
2
3
4
5

'a','A'  alpha characters only (mixed CASE)
'l','L'  LOWER CASE alpha characters only
'p','P'  ANY printable characters
'u','U'  UPPER CASE alpha characters only
'x','X'  ANY alpha-numeric characters (UPPER)

Y por ultimo la mejor, podemos obtener un registro aleatorio de una tabla, solo es necesario sustituir “id_campo” por el campo llave de su tabla y por supuesto “mitabla” por el nombre real de su tabla:

1
2
3
4
5

SELECT id_campo
  FROM (SELECT id_campo
          FROM mitabla
      ORDER BY DBMS_RANDOM.VALUE)
 WHERE ROWNUM = 1

Pero bueno, esa consulta solo me regresa un id, quiero todo el registro, fácil:

1
2
3
4
5
6
7

SELECT *
  FROM mitabla mit,
       (SELECT id_campo
          FROM mitabla
      ORDER BY DBMS_RANDOM.VALUE) idv
 WHERE mit.id_campo = idv.id_campo
   AND ROWNUM = 1

Hace algunos días en mi actual proyecto se presento la necesidad de mostrar la información de una cadena pero en forma de tabular.

Al principio no se me ocurría como, pero rascandole un poquito a google di con la solución, estoy trabajando bajo una 10g por lo que no estoy seguro que funcione con versiones anteriores de Oracle.

Primero creamos un “TYPE” que usara la función que después crearemos:

1

CREATE OR REPLACE TYPE str2tblType AS TABLE OF VARCHAR2(500);

Después creamos la función que se encarga de convertir la cadena a una tabla de datos:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

CREATE OR REPLACE FUNCTION 
   str2tbl( p_str IN VARCHAR2, p_delim IN VARCHAR2 DEFAULT ',' ) 
    RETURN str2tblType
    PIPELINED AS
 
    l_str      VARCHAR2(500) DEFAULT p_str||p_delim;
    l_n        NUMBER;
 
BEGIN
 
    LOOP
        l_n := INSTR( l_str, p_delim );
        EXIT WHEN (NVL(l_n,0) = 0);
        pipe ROW( LTRIM(RTRIM(SUBSTR(l_str,1,l_n-1))) );
        l_str := SUBSTR( l_str, l_n+1 );
    END LOOP;
 
    RETURN;
END;

Por ultimo solo nos basta usarla:

1

SELECT COLUMN_VALUE col FROM TABLE(str2tbl('a,b,c'));

Como se darán cuenta utiliza cosas como los “TYPE” otra como “PIPELINED” y la función “table()” después del “from”, tal vez mas adelante hable de ellas, por el momento se los dejo de tarea.

Resulta que en España acaban de sacar una ley que prohíbe a los “hombres-anuncio”, esas personas que van por la ciudad cargando pancartas publicitarias en la espalda y en el pecho:

Hombre anuncio

Aquí la noticia completa

Sus argumentos, como buen político mexicano no son del todo claros, “atentan contra la dignidad de las personas”, si estuviéramos en México los políticos hubieran dicho, “se convirtieron en un medio para que las empresas publicitarias no pagaran impuestos” o algo como “la ley dice que una persona no puede ser usada como medio publicitario por lo que se tendrán que regular todas estas actividades” o yéndonos mas lejos “Estas empresas atentan contra el libre transito de las personas al andar por la calle con esos anuncios”.

Todo a final de cuentas nos llevaría a la modificación de la ley para incluir un nuevo impuesto, aparte con nombre bien ingenioso, “impuesto sobre el uso del cuerpo humano con fines publicitarios”, a final de cuentas corroboraríamos que a los políticos les VL MD la dignidad de las personas como dicen, su fin con esto no sería otra mas que insertarnos (por no decir “meternos” y evitar la connotación sexual) otro impuesto mas a expensas, como siempre, de una buena causa para la sociedad.

Es que, en su punto de vista, y las botargas? A poco esas si serian “dignas para las personas”, a fin de cuentas sirven para el mismo fin, son un medio publicitario usando a una persona, y yéndonos mas lejos, usar una camisa o una gorra o tal vez un pantalón con una marca comercial, no tiene el mismo fin?

Pero bueno, ya no les voy a dar más ideas, así como son de ideosos al rato me van a salir con la sorpresa del nuevo “impuesto sobre botargas, exhibicionistas, extrovertidos y encueratrices” o algo así, al rato voy a salir a la calle y no le voy poder dar un patín a la botarga del “Don simi” o la de “barney” o la de “Bob esponja” etc. etc.

Botarga simi

botarga barney

botarga bob esponja