ORACLE PL/SQL : Sin Fronteras

ORACLE PL/SQL

Scripts Inteligentes



Cuando se necesita modificar o crear un objeto en Oracle y a veces por circunstancias de la vida hay se tiene que ejecutar dos o mas veces es recomendable crear scripts inteligentes para que cuando se ejecuten por segunda ves, no de error de compilación.
E aquí un ejemplo de como crear un Script inteligente, que si existe lo que quiero modificar o crear, primero lo borra y luego lo crea o modifica.
Espero que sea de ayuda.

DECLARE vl_existe_tabla PLS_INTEGER;
BEGIN
select COUNT(*)
into vl_existe_tabla
from dba_tab_columns
where table_name = 'FACTURA'
AND column_name = 'FECHA_VENCIMIENTO';
if vl_existe_tabla > 0 then
execute immediate ' ALTER TABLE FACTURA DROP COLUMN FECHA_VENCIMIENTO';
end if;
END;
/
ALTER TABLE FACTURA
ADD( FECHA_VENCIMIENTO NUMBER(7) );
comment on column FACTURA.FECHA_VENCIMIENTO is 'Fecha de vencimiento';
DECLARE vl_existe_tabla PLS_INTEGER;
BEGIN
select COUNT(*)
into vl_existe_tabla
from all_indexes
where index_name = 'FACTURA_FEC_VENC';
if vl_existe_tabla > 0 then
execute immediate ' DROP INDEX FACTURA_FEC_VENC';
end if;
END;
/
CREATE INDEX FACTURA_FEC_VENC ON FACTURA
(FECHA_VENCIMIENTO)
LOGGING
TABLESPACE MODGBLIDX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL
/




Tablas temporales en las bases de datos Oracle



Además de las tablas de la base de datos permanentes, Oracle permite la creación de tablas temporales para mantener datos propios y exclusivos a una sesión Oracle determinada. Estos datos permanecerán en el sistema sólo durante el tiempo que dure la transacción o sesión involucrada. No obstante, al igual que para las tablas permanentes, la definición de las tablas temporales se almacena en las tablas del sistema.

La sentencia CREATE GLOBAL TEMPORARY TABLE crea una tabla temporal Oracle cuya temporalidad puede ser definida a nivel de transacción (los datos existen mientras se realiza la transacción) o a nivel de sesión (los datos existen mientras dura la sesión). Los datos en una tabla temporal son propios y privativos de la sesión Oracle que la está utilizando. Una sesión Oracle determinada puede ver y modificar los datos que durante dicha sesión se insertaron en la tabla temporal, pero estos datos no son accesibles desde otra sesión diferente. Como es lógico, la sentencia LOCK no tiene efecto sobre las tablas temporales ya que cada sesión hace uso de sus propios datos.

Así pues, los datos almacenados en una tabla temporal son visibles sólo para la sesión de Oracle que inserta datos dentro de dicha tabla. Para especificar si los datos de una tabla temporal son por sesión o por transacción, a la hora de crear la definición de la tabla, utilizaremos la cláusula ON COMMIT DELETE ROWS para indicar que la temporalidad es a nivel de transacción, o la cláusula ON COMMIT PRESERVE ROWS si queremos que la temporalidad sea a nivel de sesión.

A continuación podéis ver un ejemplo de comando SQL para crear una tabla temporal en Oracle:

CREATE GLOBAL TEMPORARY TABLE temp_listado
(
nombre VARCHAR2(40),
fecha_nacimiento DATE
)
ON COMMIT PRESERVE ROWS


Asimismo, si ejecutamos una sentencia TRUNCATE sobre una tabla temporal, los datos que se truncarán serán los de la propia sesión desde la que se ejecuta la sentencia. Los datos que hayan podido ser insertados desde otras sesiones que estén utilizando la misma tabla, no se verán afectados por la sentencia TRUNCATE.

Los datos de una tabla temporal Oracle se borran automáticamente en el caso de que la sesión termine, bien porque el usuario desconecte, bien porque la sesión termine de una de manera anormal al producirse algún tipo de fallo.

Las tablas temporales admiten la creación de índices con el comando CREATE INDEX. Dichos índices, como las tablas, son también temporales, y los datos en el índice permanecen en el sistema mientras la tabla temporal existe.

También se pueden crear views y triggers sobre tablas temporales.

Asignación de segmentos

Para las tablas temporales, Oracle no asigna un segmento justo en el momento en que son creadas, cosa que si que hace para las tablas permanentes. Por contra, los segmentos son asignados cuando se realiza el primer INSERT.

Ser pueden ejecutar sentencias DDL (ALTER TABLE, DROP TABLE, CREATE INDEX,...) sobre las tablas temporales, pero sólo si no hay ninguna sesión que la esté utilizando en ese momento y que ya haya realizado un INSERT sobre la misma. La tabla temporal queda liberada en el momento en que la sesión termina, o cuando se ejecuta una sentencia COMMIT o ABORT si la temporalidad de la tabla fue definida a nivel de transacción.

De igual forma, los segmentos se liberan al final de la transacción o al final de la sesión según se haya definido la temporalidad de la tabla.



Manejo de excepciones en PL/SQL (excepciones predefinidas)


Los errores que se producen durante la ejecución de un bloque de código PL/SQL pueden ser manejados a gusto del programador, es decir, si durante la ejecución de una sentencia PLSQL se produce un error, podemos hacer que el programa realice unas acciones u otras dependiendo del tipo de error que se haya generado, esto es algo parecido a lo que se puede hacer cuando programamos en C++ o Java. Para conseguir esto debemos añadir dentro del bloque de código PL/SQL una sección para tratamiento de las excepciones.

Existen dos tipos de excepciones:
- Excepciones predefinidas
- Excepciones definidas por el usuario.

En este artículo voy a hablar sólo de las excepciones predefinidas.

PL/SQL proporciona un gran número de excepciones predefinidas que permiten controlar las condiciones de error más habituales. Las excepciones predefinidas no necesitan ser declaradas y son las siguientes:

DUP_VAL_ON_INDEX - Se produce cuando se intenta almacenar un valor ya existente en una columna que tiene restricción de índice único.

TIMEOUT_ON_RESOURCE - Se excedió el tiempo máximo de espera por un recurso en Oracle.

NOT_LOGGED_ON - El programa efectuó una llamada a Oracle sin estar conectado.

LOGIN_DENIED - El login o la contraseña utilizados para entrar en Oracle son inválidos.

NO_DATA_FOUND - Una sentencia SELECT INTO no devolvió ningún registro.

TOO_MANY_ROWS - Una sentencia SELECT INTO devolvió más de un registro.

ZERO_DIVIDE - Se ha ejecutado una división donde el divisor valía cero.

STORAGE_ERROR - Si no se dispone de más memoria o la memoria esta dañada.

PROGRAM_ERROR - Ocurrió un problema interno al ejecutar el código PL/SQL.

INVALID_NUMBER - Cuando falla la conversión de una cadena de caracteres hacia un número porque la cadena no representa un número válido.

VALUE_ERROR - Ocurrió un error aritmético, de conversión o truncamiento. Por ejemplo, esto sucede cuando se intenta dar un valor muy grande a una variable que no soporta dicho tamaño.

ROWTYPE_MISMATCH - Los elementos de una asignación (el valor a asignar y la variable que lo contendrá) son de tipos incompatibles. También se presenta este error cuando un parámetro pasado a un subprograma no es del tipo esperado.

SYS_INVALID_ROWID - Falla la conversión de una cadena de caracteres hacia un tipo rowid porque la cadena no representa un número.

INVALID_CURSOR - Se efectuó una operación no válida sobre un cursor. Suele ocurrir cuando un cursor no está abierto y se ejecuta una sentencia para cerrar dicho cursor.

CURSOR_ALREADY_OPEN - Cuando se intenta abrir un cursor que ya estaba abierto. Hay que recordar que un cursor de tipo FOR se abre automáticamente por lo que no se debe ejecutar la sentencia OPEN.

ACCESS_INTO_NULL - Se intentó asignar un valor a los atributos de un objeto no inicializado.

COLLECTION_IS_NULL - Se intentó asignar un valor a una tabla anidada aún no inicializada.

SELF_IS_NULL - El parámetro SELF (el primero que es pasado a un método MEMBER) es nulo.

OTHERS - Cualquier otro tipo de error que pueda producirse. Cuando se utiliza la excepción OTHERS, cualquier excepción que no se haya tratado anteriormente se procesará según la secuencia de instrucciones incluida dentro de la sección OTHERS. OTHERS debe ser la última excepción tratada dentro de la sección dedicada al tratamiento de excepciones.

La sintaxis de una sección para tratamiento de excepciones es como sigue:

BEGIN
[Secuencia de sentencias]
EXCEPTION
WHEN [nombre de la excepción 1] THEN
[Sentencias para tratar la excepción 1]
.................................................
WHEN [nombre de la excepción n] THEN
[Sentencias para tratar la excepción n]
WHEN OTHERS THEN
[Sentencias para tratar el resto de las excepciónes]
END;

En el siguiente ejemplo se utilizan las excepciones predefinidas NO_DATA_FOUND, TOO_MANY_ROWS y OTHERS:

DECLARE
vprecio inventario.precio%TYPE;
BEGIN
[Otras sentencias]
BEGIN
SELECT precio FROM inventario
WHERE cantidad = 100
INTO vprecio;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE("No hay ningún artículo.");
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE("Hay más de un artículo.");
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE("Error, abortando ejecución.");
RAISE;
END;
[Otras sentencias]
END;

En este ejemplo si la sentencia SELECT INTO fallase por no devolver ningún registro o por devolver más de uno, se mandaría un mensaje de error a la pantalla pero la ejecución del programa continuaría; por contra, para cualquier otro error, aunque también se mandaría un mensaje de error a la pantalla, la ejecución del programa se abortaría (comando RAISE).

0 Comments:

Post a Comment