Creación de tablas desde un procedimiento almacenado en la Base de datos (Oracle)

Dentro de los conceptos más buscados encontré que muchas personas buscan éste tipo de código, así que para todas ellas aquí les vá, y cómo siempre,   para mí, un ejemplo es mucho más sencillo de aprender, que todo un pergamino de explicaciones, así que, cualquier duda,  solo escribanme.

PROCEDURE “CREA_TABLAS” (P_CODIGO_FORMULARIO IN NUMBER)
IS
CURSOR CONCEPTOS IS
SELECT CODIGO_CONCEPTO, CODIGO_FORMULARIO, NOMBRE_DEL_CAMPO
FROM DM_CONCEPTOS_FORMULARIOS
WHERE CODIGO_FORMULARIO = P_CODIGO_FORMULARIO
ORDER BY CODIGO_CONCEPTO;
DDL VARCHAR2(32767);
BEGIN
EXECUTE IMMEDIATE  ‘DROP TABLE TEMP_’||TO_CHAR(P_CODIGO_FORMULARIO);

DDL := ‘CREATE TABLE TEMP_’||TO_CHAR(P_CODIGO_FORMULARIO)||’ (‘;
DDL := DDL || ‘ANIO NUMBER(4), ‘;
DDL := DDL || ‘MES NUMBER(2), ‘;
FOR J IN CONCEPTOS LOOP
DDL := DDL ||TO_CHAR(J.NOMBRE_DEL_CAMPO)||’ NUMBER(20,2), ‘;
END LOOP;
DDL := SUBSTR(DDL,1,LENGTH(DDL)-2) || ‘ ) TABLESPACE TBS_TABLAS ‘;

EXECUTE IMMEDIATE DDL;

EXECUTE IMMEDIATE ‘CREATE INDEX I_TEMP_’||TO_CHAR(P_CODIGO_FORMULARIO)||’_FACT ON TEMP_’||TO_CHAR(P_CODIGO_FORMULARIO)||’(ANIO, MES) TABLESPACE TBS_INDICES’;

DBMS_OUTPUT.PUT_LINE(‘PROCESO GENERADOR DE LA TEMP_’||TO_CHAR(P_CODIGO_FORMULARIO)||’ CONCLUIDO SATISFACTORIAMENTE!’);
EXCEPTION WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20912, SQLCODE || ‘,’ || SQLERRM);
END;

Obviamente, si quisieran que éste código funcionára sería necesario crear una tabla llamada “Dm_Conceptos_Formularios”, y que ésta tenga las columnas “Codigo_Concepto”, “Codigo_Formulario” y “Nombre_del_Campo”, por lo demás, sólo bastaría con crear este procedimiento a nivel de base de datos, y listo,… ya tienen un ejemplo de cómo crear tablas dinamicamente desde un procedimiento almacenado.

De igual forma pueden llenar la tabla también desde un procedimiento almacenado, así que espero les sirva.

Saludos.

Problema con procedimiento bloqueado (Seguimiento Galileo).

En éstos días me he estado topando con un problema raro.

Intentando compilar una forma me dí cuenta que Forms simplemente se quedaba frizado todo,.. y ya no hacía nada, ésto lo hacía cuando al compilar la forma llegaba a determinado procedimiento.

Investigando resulta que el procedimiento hacía uso de otro procedimiento, pero éste estába almacenado en base de datos.

Dandole un seguimiento general, descubrí que otro usuario estába utilizando éste procedimiento almacenado en base de datos (el objeto lo tenía bloqueado), y éste era una sesion activa (pelada),  pero  en éstos momentos en la realidad ya ni conectado estába dicho usuario, así que la solución que yo le encontré fué  matar (killiar) todas las sesiones activas (desde el Sistema Operativo), para poder compilar la forma que utilizaba dicho objeto.

Query dinamico en procedimiento almacenado (Oracle).

Ayer un usuario de  La Web del programador me contactó porque tenía dudas sobre cómo hacer un Query Dinámico en un procedimiento almacenado en la base de datos, por lo que me gustaría compartir con ustedes la solución, ya que que en su momento yo también anduve buscando por todos lados una solución y no me fué fácil encontrarla.

Personalmente la forma que más utilizo es la siguiente:

DECLARE
  TYPE CUR_TYP IS REF CURSOR;
  c_cursor   CUR_TYP;
  fila PAISES%ROWTYPE;
  v_query     VARCHAR2(255);
BEGIN
  v_query := 'SELECT * FROM PAISES';

  OPEN c_cursor FOR v_query;
  LOOP
    FETCH c_cursor INTO fila;
    EXIT WHEN c_cursor%NOTFOUND;
    dbms_output.put_line(fila.DESCRIPCION);
  END LOOP;
  CLOSE c_cursor;
END;

Tomar en cuenta que  la variable “fila” es de tip “ROWTYPE”,  o sea que contiene los mismos nombres de columnas que los de la tabla y allí es en donde se almacena el resultado del Query.

Espero haberles ayudado en algo, y espero sus comentarios.   !! Saludos !!.

Cómo conectarse a la base de datos desde una forma (.Fmx)

Para ello es necesario agregar a los triggers de la forma el trigger llamado “On-Logon” y utilizar el código que incluyo a continuación:

declare
uname varchar2(12) := ‘El_usuario’;
pass  varchar2(12) := ‘El_password’;
begin
logon(uname, pass||’@La_basededatos’);

if not form_success then
message(‘hay clavos’); synchronize;
pause;
raise form_trigger_failure;
end if;
end;

Ojo que en donde dice ‘El_usuario’, ‘El_password’ y ‘La_basededatos’ es realmente el Usuario que ustedes van a utilizar para conectarse a la base de datos, el Password de éste usuario y la base de datos a la que se van a conectar.

Código para convertir de Número a Letras

Uno de los proyectos que más piden en las clases de programación en los colegios o universidades es la conversión de números a letras, por algún tiempo me la pasé buscando y encontré varias soluciones en Visual Basic, en C, en PHP, en ASP, en fin, en tantas herramientas, pero en PL/SQL de Oracle !!!   fué casi misión imposible, por lo que aquí les comparto una para quienes les pueda servir, … no hay que inventar el agua azucarada.

– Start of DDL script for NUMTOLETRAS

– Function NUMTOLETRAS

CREATE OR REPLACE
FUNCTION NUMTOLETRAS (Numero Number) RETURN Varchar2 IS
TYPE Textos IS TABLE OF Varchar2(20)
INDEX BY BINARY_INTEGER;
Texto Textos;
Letras Varchar2(255) := ”;
I BINARY_INTEGER := 0;
EnTexto Varchar2(255);
Unidad Varchar2(255);
Grupo Varchar2(255);
TextoCentena Varchar2(255);
TextoDecena Varchar2(255);
TextoUnidad Varchar2(255);
Y Varchar2(255);
Decimales Varchar2(255);
DigitoCentena Number;
DigitoDecena Number;
DigitoUnidad Number;

BEGIN
Texto(0) := ‘UN ‘;
Texto(1) := ‘UNO ‘;
Texto(2) := ‘DOS ‘;
Texto(3) := ‘TRES ‘;
Texto(4) := ‘CUATRO ‘;
Texto(5) := ‘CINCO ‘;
Texto(6) := ‘SEIS ‘;
Texto(7) := ‘SIETE ‘;
Texto(8) := ‘OCHO ‘;
Texto(9) := ‘NUEVE ‘;
Texto(10):= ‘DIEZ ‘;
Texto(11):= ‘VEINTE ‘;
Texto(12) := ‘TREINTA ‘;
Texto(13) := ‘CUARENTA ‘;
Texto(14) := ‘CINCUENTA ‘;
Texto(15) := ‘SESENTA ‘;
Texto(16) := ‘SETENTA ‘;
Texto(17) := ‘OCHENTA ‘;
Texto(18) := ‘NOVENTA ‘;
Texto(19) := ‘ONCE ‘;
Texto(20) := ‘DOCE ‘;
Texto(21) := ‘TRECE ‘;
Texto(22) := ‘CATORCE ‘;
Texto(23) := ‘QUINCE ‘;
Texto(24) := ‘CIEN ‘;
Texto(25) := ‘CIENTO ‘;
Texto(26) := ‘DOSCIENTOS ‘;
Texto(27) := ‘TRESCIENTOS ‘;
Texto(28) := ‘CUATROCIENTOS ‘;
Texto(29) := ‘QUINIENTOS ‘;
Texto(30) := ‘SEISCIENTOS ‘;
Texto(31) := ‘SETECIENTOS ‘;
Texto(32) := ‘OCHOCIENTOS ‘;
Texto(33) := ‘NOVECIENTOS ‘;
Texto(34) := ‘MIL ‘;
Texto(35) := ‘MILLON ‘;
Texto(36) := ‘MILLONES ‘;

EnTexto := ltrim(rtrim(TO_CHAR(TRUNC(Numero), ’99999999999′))); –       ‘ Convirtiendo el numero a un string de 12 posiciones.
EnTexto:=lpad(EnTexto,12,’0′);        — Rellena de 0′s

– Este ciclo recorre el numero de tres en tres posiciones.
FOR I IN 1..4 LOOP
Grupo := SUBSTR(EnTexto, I * 3 – 2, 3);    — Obtiene el grupo de tres digitos segun el contador del ciclo.

– Establece las unidades a usar: “mil” o “millon”
IF I=1 or I=3 THEN
Unidad := Texto(34);
ELSE
If I = 2 THEN
IF TO_NUMBER(Grupo)=1 THEN
Unidad := Texto(35);
ELSE
Unidad:= Texto(36);
END IF;
ELSE
Unidad := ”;
END IF;
END IF;

– Si hay un numero el grupo lo evalua, sino sigue con el siguiente grupo.
If TO_NUMBER(Grupo) > 0 Then
If TO_NUMBER(Grupo) = 1 Then   — Por los casos de “mil” o “un millon” se evalua cuando el valor sea 1.
IF I = 2 THEN
TextoUnidad := Texto(0);
ELSE
IF I=4 THEN
TextoUnidad := Texto(1);
ELSE
TextoUnidad := ”;
END IF;
END IF;
Letras := Letras || TextoUnidad || Unidad;
ELSE   –                         ‘ Los demas casos que no son 1.
– Se asignan a variables cada digito del grupo de tres.
DigitoCentena := TO_NUMBER(SUBSTR(Grupo, 1, 1));
DigitoDecena  := TO_NUMBER(SUBSTR(Grupo, 2, 1));
DigitoUnidad  := TO_NUMBER(SUBSTR(Grupo, 3, 1));

– Si hay algo en los cientos lo analiza sino lo deja pasar.
If DigitoCentena <> 0 Then
– En el caso que sea 100 le asigna de una vez, sino el texto que le corresponde segun la
– posicion en el vector.
IF Grupo = ’100′ THEN
TextoCentena := Texto(24);
ELSE
TextoCentena := Texto(24 + DigitoCentena);
END IF;
End If;

– Analiza sino se encuentra entre el 11 y el 15.
If DigitoDecena = 1 And DigitoUnidad >= 1 And DigitoUnidad <= 5 Then
TextoDecena := Texto(18 + DigitoUnidad);
TextoUnidad := NULL;
Y           := NULL;

– Evalua Los demas numeros fuera del 11 al 15.
Else
– Si hay numero en las decenas asigna la posicion correspondiente en el vector.
IF DigitoDecena <> 0 THEN
TextoDecena := Texto(9 + DigitoDecena);
ELSE
TextoDecena := ”;
END IF;

– Si hay numero en las unidades asigna la posicion correspondiente en el vector.
IF DigitoUnidad <> 0 THEN
TextoUnidad := Texto(DigitoUnidad);
ELSE
TextoUnidad := ”;
END IF;

– Evalua si hay que conectar con “y”.
IF DigitoUnidad > 0 AND DigitoDecena > 0 THEN
Y := ‘Y ‘;
ELSE
Y := ”;
END IF;
End If;
– Une los textos generados en este grupo y los va agregando al resultado
– de la funcion letras.
Letras := Letras || TextoCentena || TextoDecena || Y || TextoUnidad || Unidad;

END IF;
END IF;

END LOOP;

– Evalua si hay decimales.
IF TRUNC(Numero) = Numero THEN
Decimales := ‘EXACTOS’;
ELSE
Decimales := ‘CON ‘||SUBSTR(TO_CHAR(Numero, ’99999999999.99′),14,2)  || ‘/00′;
END IF;
– Une los decimales al valor de retorno de la funcion.
Letras := Letras || Decimales;

RETURN Letras;
EXCEPTION
WHEN OTHERS THEN
RETURN ‘NO CONVERTIDO’;
END;
/

– End of DDL script for NUMTOLETRAS

Bases de datos orientadas a objetos y XML

Hoy en día todo está orientado a objetos, y las bases de datos no son la excepción, Oracle es una base de datos orientada a objetos por lo que nos brinda las facilidades para poder utilizarla con ésta orientación, y trabajar XML dentro de ella.

A veces cuesta encontrar documentación en español sobre éste tema, pero me gustaría compartir éste documento “xml_en_oracle” que me encontré por allí, en donde explica de una manera bastante entendible cómo trabajar orientado a Objetos y también cómo almacenar y trabajar con documentos de tipo XML.