Muchas veces nos encontramos con que campos de nuestras tablas tienen valor null, usea, ná de ná, y al traerlos al spread, a un xml o a donde sea nos suele dar problemas. No tengo la solución mágica, pues cada desarrollo es diferente, pero sí las herramientas mágicas (dos de ellas) que nos ayudarán a lidiar estas situaciones, el NVL y el NVL2.

NVL( campo, SiNuloReemplazarPor)

La page de tech on then net dice: The NVL function lets you substitute a value when a null value is encountered. Que traducido del marciano viene a decir, si el campo es nulo devuélveme esto otro.

Así, si pongo, por ejemplo, NVL(campo,’No hay dato’), me devolverá ‘No hay dato’ si Campo en nulo, sino me devolverá el valor que contenga el campo.

Pero hay otra función de PL/SQL que me ha parecido muy interesante, como es el NVL2.

NVL2( string1, value_if_NOT_null, value_if_null )

La page de tech on then net dice: It lets you substitutes a value when a null value is encountered as well as when a non-null value is encountered. Que traducido del marciano viene a decir, si el campo es nulo devuélveme este dato, peeeeero, si no es nulo este otro. Lo cual abre una serie de posibilidades muy interesantes.

Así, siguiendo el ejemplo anterior, si pongo NVL2(Campo,’Hay dato’,'No hay dato’), si campo es nulo me devolverá ‘No hay dato’, mientras que si tiene valor me devolverá ‘hay dato’.

[!] Naturalmente en vez de ‘Hay dato’ o ‘No hay dato’, podría ponerse otro campo de la select o lo que nos apetezca

Os iba a hablar también del NANVL, pero como es para el Oracle 10 y todavía no lo tenemos… pues que paso de hablar de algo que no puedo probar, leñe!

Ná, una gilipollez. El asunto es que estaba explicándole a un compa del curro como funcionan los triggers en oracle y para ello debía rellenar una tabla cuando jugásemos con los datos de otra tabla, poniendo si habíamos hecho una update, una insert o una delete:

if :old.Codigo<>NULL and :new.Codigo<>NULL then
iud:='U';
else
if :old.Codigo=NULL and :new.Codigo<>NULL then
iud:='I';
else
iud:='D';
end if;
end if;

¿Porqué me pone siempre la variable iud con valor ‘D’?

Mu simple, el = NULL no funciona, hay que poner el is o el is not, quedando el código de la siguiente forma:

if :old.Codigo is not NULL and :new.Codigo is not NULL then
iud:='U';
else
if :old.Codigo is NULL and :new.Codigo is not NULL then
iud:='I';
else
iud:='D';
end if;
end if;

Lo que os he dicho, una gilipollez, pero me ha hecho gracia.

Hasta el momento hemos comentado como se hacen las exports, las import,… pero no hemos hablado de cómo crear un esquema en oracle. Para ello basta con lanzar este sencillo script, pero antes debemos comprobar un par de cuestiones.

Los datos a comprobar es la ruta donde se almacenan los tablespace en nuestro Oracle, así como el nombre del tablespace temporal. El tablespace es un espacio de tablas es una división lógica de una base de datos. Toda base de datos consta, al menos, de un espacio de tablas (llamado espacio de tablas SYSTEM). Se pueden utilizar otros espacios de tablas para agrupar a los usuarios o aplicaciones, con el fin de facilitar el mantenimiento y mejorar el rendimiento. Algunos ejemplos de estos espacios de tablas pueden ser: USERS (para uso general) y UNDO (para deshacer cambios) [1]. Una vez lo hallamos comprobado, los ponemos en el script y lo lanzamos.

Lo primero que hay que crear son los dos tablespace, para el Nombre_data y para el Nombre_index (pudiendo ser Nombre lo que queramos). En este ejemplo crearemos un esquema llamado Compras y a los tablespace los llamaremos Comp_data y Comp_index

CREATE TABLESPACE "COMP_DATA"
LOGGING
DATAFILE 'D:\ORACLE\ORADATA\OR92\COMP_DATA1.ora' SIZE 50M
REUSE AUTOEXTEND
ON NEXT 10240K MAXSIZE 32767M EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;
CREATE TABLESPACE "COMP_INDEX"
LOGGING
DATAFILE 'D:\ORACLE\ORADATA\OR92\COMP_INDEX1.ora' SIZE 50M
REUSE AUTOEXTEND
ON NEXT 10240K MAXSIZE 32767M EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;

Y ahora nos creamos al usuario con unos permisos, etc… que manejará estos tablespace:

CREATE USER "COMPRAS" PROFILE "DEFAULT"
IDENTIFIED BY "COMPRAS" DEFAULT TABLESPACE "COMP_DATA"
TEMPORARY TABLESPACE "TEMP"
QUOTA UNLIMITED ON "COMP_DATA"
QUOTA UNLIMITED ON "COMP_INDEX"
QUOTA UNLIMITED ON "TEMP"
ACCOUNT UNLOCK;
GRANT "CONNECT" TO "COMPRAS";
GRANT "RESOURCE" TO "COMPRAS";
REVOKE UNLIMITED TABLESPACE FROM "COMPRAS";

Y así ya tenemos creado el esquema Compras, por lo que ya podemos jugar con él, jiji.

[1] definición de tablespace sacada de ajpdsoft

Nos ha pasado una cosa mu curiosa con Oracle en un par de proyectos, y además el mismo día, pues al poco rato de empezar a mirarlo veo una SOS de Iñaki, un compa del curro, en el GTalk. La historia consistía que al capturar la fecha y la hora de oracle lo hacia bien salvo que la hora fueran las 00:00:00. Es decir, imaginate que lanzo la siguiente select en el Toad:

select fecha from compras order by fecha

Lo que me devuelve el campo fecha de tipo date de la tabla compras tal que así:

Fecha:
2006/11/01 14:00:01
2006/11/01 15:00:01
2006/11/02
2006/11/02 14:58:01


¡¿Eim..?! ¿Que le pasa al tercer registro? ¿quien marrobau la hora?

No, no es que Oracle tenga un bug, es más bien su forma de ver la vida, usease, si son las 00:00:00 no lo muestra (recordar que un tipo date SIEMPRE guarda la hora, como recalca siempre ini), lo da por entendido (o por lo menos eso dedugimos). Como creíamos que pudiera ser cosa del Toad, o de la dll de acceso a datos que utilizamos en el curro, pues ala, a currarse un miniejemplo con la clase System.Data.OracleClient de .NET.

Nuestro gozo en un pozo! hay que comentar, que el fallo que nos daba era que no aparecía en pantalla la hora (ya sabeis, txt=campo ;), por lo que el cliente no sabía a que hora se refería el registro en cuestión, porqué el programa ni petaba ni daba información falsa (de ahi lo de su forma de ver la vida)

¿Que hicimos? Pasarlo a texto:

select to_char(Fecha, 'DD/MM/YYYY HH24:Mi:SS') from compras order by fecha

Pero me toca las webs que si el registro contiene la hora no me la devuelva por que lo de por suspuesto, coñe!

Un compa me preguntaba vía Google Talk como podía sumarle horas a una fecha en Oracle. La cosa parecía sencilla, y más teniendo en cuenta la función Add_Months, por lo que deduzco, erróneamente, que también existirá el Add_Hours. Pos no! no existe. Fale, podemos pasar la fecha a string, coger las horas, sumarle lo que sea, calcular la nueva fecha con la suma y para pasarla hacerle un to_date. Mu complicau. Se debe de poder hacer lo mismo pero más fácil, con menos código, hasta que se me enciende la luz. Si para sumar 1 día se hace sysdate+1, y sí 1 día tiene 24 horas, 0.5 días tiene 12 horas, por lo que si hago sysdate + 0.5 me sumara 12 horas a la fecha que quiero modificar. Bingo!!

La formulación quedaría así:

select sysdate + (x/24) from dual

[!] Donde x es el número de horas a sumarle a, en este caso, a la fecha actual. Si queremos sumarle a otra fecha que no sea la actual solo hay que sustituir el sysdate por to_date(’11/09/2006 12:00:00′,’DD/MM/YYYY HH24:Mi:SS’), en este caso, la fecha de creación de este blog :-)

Últimamente me han preguntado ya un par de veces como sacar las tablas de un esquema concreto. Hay dos formas, dependiendo de qué es lo que queramos sacar:

select * from user_tables

[!] Sacara las tablas propiedad del usuario en cuestión.

o:

select * from all_tables

[!] Las tablas a las que tiene acceso (pueden ser de otros usuarios) el usuario en cuestión.

Hoy me llega un correo de Iñaki (un compa del curro) con la siguiente cuestión:

“[...] El caso es que necesito ejecutar un procedimiento almacenado dentro de otro procedimiento almacenado y a partir de ahí hacer una serie de cosas mientras el cursor del procedimiento (el que ejecuto dentro del otro) tenga datos. [...]”

Así que me he puesto a mirar por san google, por los manuales de oracle que me he ido bajando a lo largo de estos añicos, por messenger con Iñigo (otro compa),… y por fin, con un ménage à trois de todos ellos he podido juntar las piezas y dar con el código que funciona.

Tenemos una tabla TCiudades en un esquema X de Oracle9i, que contiene dos campos: CiuCodigo y CiuNombre. Nos hemos creado un procedure que me hace una select de esta tabla devolviéndomelo en un cursor, previamente implementado en un package:

El package (ya veis que simple, que no tiene no body ni ná de ná):

CREATE OR REPLACE PACKAGE "GLOBALES" as
TYPE refcur IS REF CURSOR;
end;
/

El procedimiento “Hijo” (también de una simpleza exquisita):

CREATE OR REPLACE PROCEDURE CIUDAD_SEL
(
vcursor OUT Globales.refcur
)
IS
BEGIN

OPEN vcursor FOR
SELECT CIUCODIGO, CIUNOMBRE
FROM MIP_TCIUDADES;

END CIUDAD_SEL;
/

Y ahora el procedimiento padre, resolviendo de esta forma la duda central:

CREATE OR REPLACE PROCEDURE CIUDADCopia_INS
(
vcursor OUT Globales.refcur
)
IS
Micursor Globales.refcur;
Cod varchar2(3);
Nom varchar2(30);
BEGIN
ciudad_sel(micursor);
loop
fetch micursor into Cod, Nom;
EXIT WHEN micursor%NOTFOUND;
insert into TCiudadcopia values(cod, nom);
end loop;
close micursor;
END CIUDADCopia_INS;
/

En donde me declaro tres variables, el cursor que me va a devolver el procedimiento “Hijo”, y dos variables más donde meter lo que me devuelva dicho cursor… y listo Kalixto!.

[!] Si os fijáis, en el procedimiento padre no hago el open cursor, ya que se hace en el hijo, y en el padre simplemente lo cierro.

Cualquier pregunta, optimización, duda, corrección… en comentarios.

Ahora para cargar en vez del export completo, sólo unas tablas, te guardas el archivo .dmp en c:\MisTxorradas (por ejemplo, por que si te gusta escribir guardalo en c:\mmmn\jhgdahgahf\jgsafdkjhg\jhgfdsajh).

Una vez guardada, limpias el contenido de la/s tabla/s del esquema destino que vayas a exportar y una vez limpias te abres el MSDOS y escribes lo siguiente, despues de situarte en el directorio en el que este el archivo .dmp:

Para Oracle 8:

• imp80 Usuario/Pas@Service file=MiExport.dmp log=kk.log tables=(tabla1,tabla2,….,tablaN) ignore=y commit=y

Para Oracle 9i:

• imp Usuario/Pas@Service file=MiExport.dmp log=kk.log tables=(tabla1,tabla2,….,tablaN) ignore=y commit=y

…. y con esto y un bizkotxo hasta mañana a las 8.

Es decir, nos hemos traido una BBDD de Oracle desde el cliente en un archivo .dmp (ya explicamos hace unos días como se creaban estos ficheros .dmp) y ahora queremos meterla (la BBDD, degeneraus!) en nuestro Oracle.

FACIL, y además sin utilizar el Toad. Abre el MSDOS, te pones en el directorio donde tengas el export. Normalmente, un fichero con extensión “.dmp”.

Ahora, por otro lado, abres el Security Manager de Oracle, entrando como administrador al servidor donde quieres hacer la carga. (Ejemplo: user->Usuario password->Pas servicio->Service).

Nos situamos en seguridad–>usuarios. Selecciona el usuario en el que quieras cargar el export (como ejemplo utilizaremos el esquema Cargas) y pulsa botón derecho, en el menu contextual seleccionamos “Crear como” (“Create like” en marciano) Y le ponemos el mismo nombre y password que tiene el usuario que hemos utilizado de origen (trankis, que no peta, seguir leyendo y vereis porque). Lo dejas así, no pulses en aplicar, ni en aceptar ni en ná. Es decir, sin crearte una copia del usuario vamos a dar el paso siguiente, que es eliminarlo. Te vas a MSDOS y tecleas:

• c:\>sqlplus Usuario/Pas@Service

Con ello te conectas a la base de datos mediante SQL*Plus. En el prompt tecleas:

• sql>drop user cargas cascade;

[!] ah! no la cagues como yo y pon al final de la linea el ; que NO es decoracion!

Le damos al intro y al de unos segundicos nos aparecerá algo como “usuario borrado” o su equivalente en marciano. Que no! Que no me se ha ido la olla, trankilos. ¿No te acuerdas que antes de eliminar al usuario Cargas, desde el Security Manager de oracle le hemos dado al Create Like? je je pues eso, vete de nuevo al Security Manager (recuerda que lo tienes abierto) y ejecuta el “Crear como” que tienes abierto desde el primer paso. Simplemente pulsas aplicar, y … tatxan! aparecera un mensajiko que diga “usuario creado correctamente” (o su equivalente en marciano).

Una vez creado, seleccionas el usuario. En la parte derecha, se abriran varias pestañas. Te pones en la de roles/privilegies. Seleccionas tipo de privilegio->privilegios de sistema. En la parte de abajo seleccionas el único que hay y le das a la flecha que apunta hacia arriba (quitar ese privilegio) y le das al aplicar.
Ahora te posicionas en la pestaña de Cuotas, y compruebas que los tablespace para ese esquema más el temporal estan unlimited, sino, eliges unlimited para todos los tablespaces asignados al usuario más el temporal (hay un pequeño bug en el Oracle, ya que normalmente si le clickas directamente a cuotas sin cambiar de usuario no sale nada, para que te salga, simplemente miras la cuota de otro usuario cualquiera y luego vuelve al usuario que te interesa). Le das otra vez al apply.

Ahora te pasas a la ventana de Msdos en la que tenias el SQL*Plus y escribes:

• sql>quit

osea que nos vamos, que salimos, que cerramos el SQL*Plus
ahora te vas al msdos y tecleas:

Para ORACLE 8:

• c:\>imp80 Usuario/Pas@Service file=archivo.dmp log=kk.log fromuser=EsquemaOrigen touser=cargas commit=y ignore=y

Para ORACLE 9i:

• c:\>imp Usuario/Pas@Service file=archivo.dmp log=kk.log fromuser=EsquemaOrigen touser=cargas commit=y ignore=y

esto hace que se cargue desde el export (archivo.dmp) todos los objetos del usuario (EsquemaOrigen, cliente origen del export en el cliente) sobre el nuestro usuario (Cargas, usuario que nosotros utilizamos).
Cuando termine, que tarda un ratiko, y tras verificar que no produce errores, te vas al SQL worksheet de oracle y lanza la siguiente instrucción:

• select ‘alter index ‘||index_name||’ rebuild tablespace ayra_idx;’ from user_indexes where tablespace_name<>‘AYRA_IDX’;

Esto genera las sentencias SQL necesarias para para mover los indices a su ubicación correcta. (Aunque desde la instalacion del Oracle 9i se cambio la forma de hacer el export, o bien se cambio la configuración del oracle en nuestro servidor o por lo que sea para que este paso no fuera necesario y no hacerlo ya que lo hace solo anteriormente, de todas maneras si te toca hacer un export, con consultarle al vguru de Oracle del curro en el que estes, si hay o no hay que hacerlo vale). Te copias todas esas sentencias y las ejecutas con el propio SQL WorkSheet. Cuando termine estará listo.

…. y con esto y un bizkotxo hasta mañana a las 8!

Abrimos la línea de comandos (cmd), ya que las instrucciones las lanzaremos desde ahí, y nos situamos en el directorio donde queramos almacenar el archivo .dmp.

Podemos crear dos tipos de ficheros .dmp, de TODO el oracle o de un esquema en concreto.

La sintaxis para hacer un export FULL (completa, de TODO el oracle con todos los esquemas) es:

• exp login/password@servicio full=y file=NomArchivo.dmp log=NomLog.log

[!] El login y password son los del administrador de oracle.

La sintaxis para hacer export de un esquema concreto es:

• exp usuario/password@servicio owner=usuario file=usuario.dmp log=usuario.log

[!] El login y password utilizados son los del esquema a exportar.

Genera dos ficheros: Un “.dmp” y un “.log” en la ruta en la que se ha lanzado el comando.

eskerrik asko INI!