Que hace y como utilizar la función INSTR de Oracle

La función INSTR de Oracle busca una sub-cadena de caracteres dentro de una cadena de caracteres, retornando la posición de la primer ocurrencia de la misma.

Su sintaxis es:

INSTR( cadena , sub_cadena [, posicion_desde [ , ocurrencias_numero ] ] )

Sus parámetros son:

cadena: Cadena de caracteres donde se buscará la sub-cadena.

sub_cadena: Sub-cadena que se desea buscar.

posicion_desde: Posición a partir de donde se quiere buscar.

ocurrencias_numero: Número de ocurrencia que se desea encontrar su posición.

Su valor de retorno es: Número entero que contiene la posición de la primer ocurrencia de la sub-cadena dentro de la cadena, si no la encuentra retorna cero.

Ejemplos que muestra Oracle en sus manuales:

SELECT INSTR('CORPORATE FLOOR','OR', 3, 2) "Instring"

FROM DUAL;

El resultado obtenido es 14, busca la segunda ocurrencia de la cadena OR a partir de la tercer posición (R), si hubiese omitido el cuarto parámetro o introducido 1 en el mismo el resultado sería 5.

Otro ejemplo es introducir la posición a buscar en negativo, lo que hace es buscar de atrás para adelante:

SELECT INSTR('CORPORATE FLOOR','OR', -3, 2) "Reversed Instring"

FROM DUAL;

En este caso retorna 2, ya que comienza a buscar desde la primer O de FLOOR.

Si no se le pasa ningún parámetro opcional el resultado es 2:

SELECT INSTR('CORPORATE FLOOR','OR') "Normal"

FROM DUAL;

Otra forma de utilizarlo es como un reemplazo, no muy bueno, de la operación IN. Tiene la ventaja de que puede ser utilizado en las columnas resultantes de un SELECT, y en condiciones mediante el uso de variables.

Por ejemplo de una lista de pedidos interesa separar los productos ARROZ, AZUCAR y HARINA del resto. La manera tradicional de hacerlo es utilizando un IN:

select *

from pedidos

where PRODUCTO IN ('ARROZ', 'AZUCAR', 'HARINA');

Y como alternativa:

select *

from pedidos

where INSTR('ARROZ-AZUCAR-HARINA', PRODUCTO) > 0;

Si el resultado de la función INSTR es mayor que 0 quiere decir que se encuentra. Las líneas divisorias son para separar de manea confundirse entre los productos, aunque es mas seguro también ponerle al dato a buscar …

select *

from pedidos

where INSTR('-ARROZ-AZUCAR-HARINA-', '-' PRODUCTO '-') > 0;

… en el caso de que existan productos donde su nombre esté contenido en otro producto.

Si se trata de números con largos diferentes se deberá normalizar todos valores rellenando con ceros a la izquierda:

select *

from pedidos

where INSTR('#00011#00120#15020#', '#' LPAD(codigo, 5, '0') '#') > 0;

No cabe dudas que es mejor y mas claro utilizar en la cláusula WHERE la operación IN, pero por ejemplo cuando la lista se necesita manejar en forma variable podría ser útil (alternativa a SQL dinámicos).

La razón por la que forma no es buena es por que en el caso existir un índice por el campo a buscar, Oracle no lo tomaría en cuenta y accedería a la tabla en forma secuencial (FULL), aun si no se concatenaran los separadores.

Utilizando la operación IN si utilizaría el índice.

Sí se puede utilizar cuando no se requiere acceder a la tabla por el campo a comparar, por ejemplo si existiese una índice por número de cliente y se desea saber los pedidos de estos productos realizados por el cliente 123.

select *

from pedidos

where INSTR('ARROZ-AZUCAR-HARINA', PRODUCTO) > 0 and codigo_cliente = 123;

Es más útil utilizarla en las columnas de un SELECT o asignaciones de un UPDATE combinándolo con la función DECODE (condicional).

select

SUM(DECODE(INSTR('ARROZ-AZUCAR-HARINA', PRODUCTO), 0,

0, IMPORTE_PEDIDO)) importe_que_interesa,

SUM(DECODE(INSTR('ARROZ-AZUCAR-HARINA', PRODUCTO), 0,

IMPORTE_PEDIDO, 0)) importe_que_no_interesa,

SUM(DECODE(INSTR('ARROZ-AZUCAR-HARINA', PRODUCTO), 0,

0, 1)) cantidad_que_interesa,

SUM(DECODE(INSTR('ARROZ-AZUCAR-HARINA', PRODUCTO), 0,

1, 0)) cantidad_que_no_interesa

from pedidos;

En este ejemplo primero se pregunta si el resultado de INSTR es cero (no existe) se retorna cero, de lo contrario el importe del pedido para totalizar el importe que interesa, para el que no interesa se hace lo contrario.

Si en lugar de sumar el dato del importe se suma 1, se logra contar por un lado los datos que interesan y por otro lado los demás.

Como resultado final se suman todas las filas para obtener un resumen.

En este otro ejemplo se actualiza a la tabla pedidos un campo indicando si el producto forma o no parte de los que interesan:

update pedidos

set producto_que_interesa = DECODE(INSTR('ARROZ-AZUCAR-HARINA', PRODUCTO), 0, 'No', 'Si');

Auditoria sobre conexiones de usuarios

Mediante la vista propia de Oracle dba_audit_trail (acceder con system o usuario con permiso de lectura en todas las vistas del sistema) se puede conocer la información sobre las conexiones realizadas a la base de datos. Muy útil para realizar auditorías e investigaciones.

Esta consulta extrae las conexiones que realizó el usuario 'nombre_usuario' en la fecha indicada.

select a.os_username, a.username, a.terminal,

a.timestamp, a.logoff_time, a.comment_text
from dba_audit_trail a
where a.username = 'nombre_usuario'
and trunc(a.timestamp) = '20080124';

Retorna el usuario del sistema operativo con que se conectó, el usuario de Oracle, la máquina utilizada para la conexión, fecha y hora en que se conectó, fecha y hora en que se desconectó, e infrormación sobre la conexión.

Por ejemlpo si se sabe que un determinado usuario realizó una determinada operación en el sistema y se conoce la fecha y hora de la misma, mediante esta vista se puede complementar la información de la máquina con que se conectó, usuario utilizado, etc.

Con la siguiente consulta se obtienen estadísticas del uso de la base de datos por cada usuario:

select a.username,

count(*) cantidad_de_conexiones,
max(a.timestamp) fecha_utima_conexion,
count(distinct a.os_username) cantidad_de_usuarios,
count(distinct a.terminal) cantidad_de_maquinas,
count(distinct trunc(a.timestamp)) cantidad_de_dias
from dba_audit_trail a
where a.timestamp >= '20090101'
group by a.username;

Calcular primer y último día del mes

Para calcular el primer día del mes respecto a una fecha dada, o del día, en Oracle se obtiene truncando la fecha a nivel del mes. Y para calcular el último día del mes se dispone de la función last_day que entrega el último día del mes correspondiente a la fecha que se pasa por parámetros.

select trunc(sysdate, 'mm') primer_dia_del_mes,
trunc(last_day(sysdate)) ultimo_dia_del_mes
from dual;

Para el caso del último día se trunca el resultado para quitarle las horas.

Consulta aquí como calcular el primer y último día laborable del mes.

Escribir un nùmero con letras en Oracle

Aquí se muestra una interesante manera de cómo escribir un número con letras sin desarrollar una función para esto. Si bien no es una solución “tradicional” es bueno para ver se puede hacer en una sentencia SELECT.

Como ejemplo para un número 1234 retorna "un mil doscientes treinta y cuatro".

Buscar tablas que contengan un campo determinado

La siguinete consulta puede ser utilizada para encontrar las tablas que contienen determinado campo al que se le conoce parcial o totalmente su nombre.

select owner,
table_name,
column_name Nombre,
substr(data_type decode(char_col_decl_length, null,
decode(data_precision, null, null,
'('data_precision
decode(data_scale,0,')',','data_scale')')),
'('char_col_decl_length')'),1,20) Tipo
from all_tab_columns
where column_name like upper( 'nombre_campo_o_parte') '%'
order by owner, table_name, column_name;

Esta consulta es util por ejemplo para realizar conversiones de datos, saber tablas relacionadas cuando no existen claves foráneas, o encontrar el nombre de una tabla cuando se conoce solamente un campo de la misma.

Buscar tablas por parte de su nombre

Cuando no se conoce exactamente el nombre de una tabla Oracle, con la siguiente consulta se la puede encontrar.

select owner, table_name
from all_tables
where table_name like upper('parte_de_nombre_tabla' '%')
order by owner, table_name;

Consultar los índices de una tabla

Con la siguiente consulta se obtienen los índices de una tabla Oracle, incluye las columnas de los mismos.

select table_owner,
index_name,
column_position pos,
substr(column_name, 1, 30) column_name
from all_ind_columns
where table_name = upper('nombre_de_tabla')
order by table_owner, index_name, pos;

En caso de tener varios esquemas, incluir el nombre de esquema en la condición: and table_owner = 'nombre_esquema'

Consultar los campos de una tabla

Con la siguiente consulta se obtienen los campos y sus caracteríasticas de una tabla o vista de Oracle, el resultado es igual al comando DESC.


select owner, column_id Id, column_name Nombre,
decode(nullable, 'N','NOT NULL') Nulo,
substr(data_type decode(char_col_decl_length, null,
decode(data_precision, null, null,
'(' data_precisiondecode(data_scale,0,')',','
data_scale')')), '('char_col_decl_length')'),1,20) Tipo
from all_tab_columns
where table_name = upper('nombre_de_tabla')
order by owner, column_id;

Cambiar 'nombre_de_tabla' por el nombre de la tabla a consultar. Si en la base de datos se manejan varios esquemas se puede agregar al where el dueño de la tabla: "and owner = 'nombre_esquema'".