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.

Hace un tiempo estaba en un proyecto revisando unos procedimientos que necesitarían algunos cambios, se tenían algunos cambios debido que ahora tomarían información de la EBS (E-bussiness Suite de Oracle) y no de los sistemas actuales hechos en casa por la empresa.

Al revisarlos pensaba que la persona que los desarrollo era una persona sin experiencia y con poco tiempo trabajando con Oracle, pero ho sorpresa, cuando pregunte por el autor de esos procesos resulto ser el “experto” en el área, no quise ser abiertamente inquisitivo por respeto a la persona y me reserve mis comentarios, pero es que es casi increíble que una persona que se diga experto no tenga ni siquiera la mínima intención (o tal vez habilidad) de generar un buen trabajo.

No voy a hablar de todos los detalles por los que a fin de cuentas se deducía que realmente conocía muy poco del tema, pero un error que tenían todos sus procesos, es:

Todos los procesos manejaban archivos mediante UTL_FILE, pero todos los procesos tenían la apertura y manejo de excepciones de UTL_FILE en el mismo proceso, lo que generaba muchas lineas de código extras en todos los procesos, claro totalmente innecesarias, lo mas fácil es generar un procedimiento:

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

CREATE OR REPLACE PROCEDURE abre_archivo (in_archivo  IN  VARCHAR2,
                                          in_mode     IN  VARCHAR2,
                                          fHandle     OUT UTL_FILE.FILE_TYPE) IS
BEGIN  
   fHandle := UTL_FILE.FOPEN('CARGAS', in_archivo, in_mode, 32767);  
EXCEPTION WHEN UTL_FILE.INVALID_PATH THEN  
   RAISE_APPLICATION_ERROR(-20100,'Invalid Path');  
WHEN UTL_FILE.INVALID_MODE THEN  
   RAISE_APPLICATION_ERROR(-20101,'Invalid Mode');  
WHEN UTL_FILE.INVALID_OPERATION THEN  
   RAISE_APPLICATION_ERROR(-20102,'Invalid Operation');  
WHEN UTL_FILE.INVALID_FILEHANDLE THEN  
   RAISE_APPLICATION_ERROR(-20103,'Invalid Filehandle');  
WHEN UTL_FILE.WRITE_ERROR THEN  
   RAISE_APPLICATION_ERROR(-20104,'Write Error');  
WHEN UTL_FILE.READ_ERROR THEN  
   RAISE_APPLICATION_ERROR(-20105,'Read Error');  
WHEN UTL_FILE.INTERNAL_ERROR THEN  
   RAISE_APPLICATION_ERROR(-20106,'Internal Error');  
WHEN OTHERS THEN  
   UTL_FILE.FCLOSE(fHandle); 
END; 
/

en el proceso que necesites abrir un archivo lo llamas de la forma:

1
2
3
4
5

DECLARE
  fHandle          UTL_FILE.FILE_TYPE; 
BEGIN
  abre_archivo('archivo', 'w', fHandle);
END;

Listo, te ahorras muchas lineas de código, modularizas tus procesos, realizas un mejor trabajo, te facilitas la vida.

Este ejemplo lo tenia guardado desde hace mucho, fue apenas que necesite accesar a la base de datos con un usuario del que no tenia password que me acorde de el, no se puede decir que sea un “bug” de Oracle, es mas una funcionalidad que pocos conocen, seguramente existe algún privilegio por ahí escondido o que pocos conocen para no permitir cambiar passwords de usuarios de esta manera.

El resultado: poder entrar con cualquier usuario a la base de datos Oracle, yo lo he probado en una base de datos 8i, lo probé en una 10g y en este ejemplo uso la 10g express edition, ¿como? Aquí se los dejo:

Primero crearemos un usuario de prueba, solo para darnos una idea de como funciona y no hechar a perder algo en caso de que no funcione.

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

SQL> CREATE USER prueba identified BY prueba;
 
USER created.
 
SQL> grant CONNECT,resource TO prueba;
 
Grant succeeded.
 
SQL> SELECT password FROM dba_users WHERE username = 'PRUEBA'; 
 
PASSWORD
------------------------------
E552C348C0B39E0E

Después alteramos el usuario con el password de nuestra elección para poder entrar con el, en este caso crearé una tabla y la borrare en el esquema de este usuario:

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

SQL> ALTER USER prueba identified BY mipassword;
 
USER altered.
 
SQL> CONNECT prueba/mipassword@xe;
 
Connected.
 
SQL> CREATE TABLE table_prueba (columna  VARCHAR2(100));
 
TABLE created.
 
SQL> DROP TABLE table_prueba;
 
TABLE dropped.

Hasta aquí nada raro, cualquiera con los privilegios puede alterar el password de un usuario para poder entrar con el, pero la cosa es regresar a ese usuario el password anterior que no sabemos, aquí nos damos cuenta para que sirvió el select al password de dba_users:

1
2
3
4
5
6
7

SQL> conn system/mipass@xe;
 
Connected.
 
SQL> ALTER USER prueba identified BY VALUES 'E552C348C0B39E0E';
 
USER altered.

Y por ultimo en este caso solo para comprobar que esto funciono:

1
2

SQL> CONNECT prueba/prueba@xe;
Connected.

Por supuesto que esta por demás advertir que si están en un ambiente controlado y ustedes, por políticas de su empresa, no deberían de poder entrar/modificar/crear/borrar la información del usuario con el que piensen entrar, se estarán arriesgando a que su acceso sea descubierto, existen muchas formas de llevar a un log la actividad de cualquier usuario, algunas empresas se toman esas políticas mas en serio que otras, por lo que se los dejo a su criterio.

Siempre sucede que en algún proyecto, especialmente los relacionados con datawarehouse, se necesite procesar una gran cantidad de información y que esa información procesada solo sea un primer paso para un siguiente proceso, lo mejor que hacia en esos casos es crear una tabla “de paso” que comúnmente llamaba una tabla temporal.

Desde hace ya un rato Oracle maneja lo que llamo “global temporary tables” que como su nombre lo dice, son tablas verdaderamente temporales, aquí dejo un ejemplo de como crear una:

1
2
3
4
5

CREATE global temporary TABLE global_temporary_table (
  columna1       VARCHAR2(10),
  columna2       VARCHAR2(10),
  columna3       VARCHAR2(10)
) ON COMMIT DELETE rows;

Sus ventajas son varias, la información contenida en ella esta solo disponible para la sesión actual, cualquier inserción, borrado, actualización solo se refleja en la sesión activa, en la creación se tienen dos opciones:

on commit delete rows
on commit preserve rows

“on commit delete” borra la información contenida en la tabla en un commit, “on commit preserve” mantiene la información en un commit, en cualquier caso si la sesión es terminada los datos se eliminan.

Muchas funcionalidades de cualquier tabla normal se mantienen en ella, como trigguers a nivel tabla, poder crear vistas, indices, exportar e importar (claro solo la definición de la tabla).

Desde que empecé a trabajar con Oracle siempre me tope con problemas del tipo, ¿Cómo mandar a un procedimiento un set de registros? ¿Cómo regresar de un paquete un campo multi-registro? La solución al viejo estilo siempre quedaba en mandar el resultado a una tabla temporal y después manejar esa información desde la tabla temporal, una solución bastante sucia y poco confiable, se tenían problemas de concurrencia e integridad.

Desde hace un buen rato Oracle maneja varias soluciones para este problema, desde las “global temporary table” (que en otra ocasión hablare de ellas) hasta los “types” existen varias otra opciones que no he manejado, por lo que solo hablare aquí de los tipos (o “types” según se les quiera llamar).

Su uso es bastante sencillo, pero en lugar de explicarlo dejo un ejemplo, es una simple unidad de programa para ver el resultado desde sql*plus, toad o alguna otra herramienta del estilo, espero les sirva:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

DECLARE
 
  TYPE type_record IS RECORD ( campo1   VARCHAR2(20),
                               campo2   VARCHAR2(20),
                               campo3   VARCHAR2(20),
                               campo4   VARCHAR2(20),
                               campo5   VARCHAR2(20) );
 
  TYPE type_table IS TABLE OF type_record INDEX BY BINARY_INTEGER;
 
  v_type_table  type_table;
 
BEGIN
 
  FOR i IN 1..5 LOOP
 
      v_type_table(i).campo1 := i;
      v_type_table(i).campo2 := i+1;
      v_type_table(i).campo3 := i+2;
      v_type_table(i).campo4 := i+3;
      v_type_table(i).campo5 := i+4;
 
  END LOOP;
 
  FOR i IN 1..v_type_table.COUNT LOOP
 
      DBMS_OUTPUT.put_line(v_type_table(i).campo1||'-'||
                           v_type_table(i).campo2||'-'||
                           v_type_table(i).campo3||'-'||
                           v_type_table(i).campo4||'-'||
                           v_type_table(i).campo5);
 
  END LOOP;
 
END;