Anuncios en tutorial de programación PLSQL

Mostrando entradas con la etiqueta Bases de datos Oracle. Mostrar todas las entradas
Mostrando entradas con la etiqueta Bases de datos Oracle. Mostrar todas las entradas

miércoles, 31 de agosto de 2016

Cláusula DEFAULT para definición de columnas - Base de datos Oracle 12c

Las mejoras fundamentales que aporta la nueva cláusula DEFAULT de la versión 12c de las bases de datos Oracle para definición de las columnas: una significativa mejora del rendimiento y una mayor facilidad para inicializar los datos de los registros de una tabla, lo que al final implica menos líneas de código.

Cláusula DEFAULT

Supongamos que para una tabla concreta necesitamos que, cuando se inserta un nuevo registro, un campo determinado tome el valor de una secuencia. La forma en que implementaríamos este requerimiento en versiones anteriores a la 12c sería mediante un trigger PLSQL.

miércoles, 13 de julio de 2016

Identificación de tablas y columnas en una base de datos Oracle mediante sentencias SQL

Nuevo teclado de programación PL/SQL y PLSQLUna vez que una tabla Oracle es creada utilizando el comando CREATE TABLE, la estructura de dicha tabla se puede visualizar utilizando las siguientes vistas (views) del sistema de la base de datos Oracle:

DBA_TABLES: Muestra la información de la tabla a nivel de cabecera.

DBA_TAB_COLUMNS: Muestra la información de la tabla a nivel de columna.

DBA_TAB_PRIVS: Muestra los privilegios de acceso a las tablas de los usuarios de la base de datos.

DBA_COL_PRIVS: Muestra los privilegios de acceso a nivel de columna. Es bastante raro tener la necesidad de definir permisos a nivel de columna, de hecho, yo nunca me he encontrado con una bases de datos Oracle que utilice esta funcionalidad.

miércoles, 30 de marzo de 2016

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.

martes, 1 de marzo de 2016

Fases durante el procesamiento de una sentencia SQL

Procesamiento de una sentencia SQL en PLSQL o PL/SQL
Durante el procesamiento de una sentencia SQL, ya sea mediante un script o un programa PL/SQL, se distinguen cuatro fases: análisis de la sintaxis (parsing), análisis de las variables (binding), ejecución (executing) y recuperación de datos (fetching).

Fase de parsing

Durante esta fase el servidor de la base de datos Oracle realiza las siguientes acciones:

- Busca la sentencia SQL en la memoria compartida (shared pool).

- Chequea la sintaxis de la sentencia siguiendo las especificaciones y la gramática del lenguaje SQL.

miércoles, 20 de enero de 2016

Almacenamiento de subconsultas (subqueries PL/SQL) en la caché de las bases de datos Oracle

Almacenamiento de subconsultas (subqueries PL/SQL) en la caché de las bases de datos OracleEl almacenamiento caché de subconsultas o subqueries PL/SQL se trata de una funcionalidad de las bases de datos Oracle, denominada en inglés scalar subquery caching, que se encarga de optimizar internamente la ejecución de aquellas consultas que incorporan subconsultas. El funcionamiento es bastante intuitivo, si durante la ejecución de una consulta PLSQL compleja, dicha consulta incluye alguna subquery, la base de datos Oracle intentará almacenar en la caché la salida de dicha subconsulta con el objetivo de poder reutilizar dichos datos, una y otra vez, durante la ejecución de la consulta PL/SQL principal. Obviamente esto será mucho mejor para el rendimiento de la base de datos que el tener que re-ejecutar la subconsulta múltiples veces.

domingo, 15 de noviembre de 2015

Acceso remoto mediante DBLINK a una base de datos Oracle

Accesso remoto a una base de datos Oracle en PL/SQL y SQLLa manera más sencilla de acceder desde una base de datos Oracle a tablas y vistas (views) de otra base de datos Oracle es mediante el uso de un DBLink (enlace a base de datos). No obstante, en muchos casos puede no ser recomendable la utilización de DBLinks, ya que el acceso a tablas y vistas remotas puede provocar importantes problemas de rendimiento en ambas bases de datos, tanto la remota como la local. En la mayoría de los casos estos problemas de rendimiento se deben a la imposibilidad de utilizar los índices de las tablas remotas.

lunes, 8 de diciembre de 2014

Objetos bloqueados en una base de datos (2)

Esta nota es continuación del anterior post Objetos bloqueados en una base de datos (1). Así pretendo dejar zanjado el tema referente a la información almacenada en la tabla V$LOCK.

Tipos de locks y las columnas ID1 e ID2

En nuestro ejemplo de la nota anterior, ya sabemos que nuestro lock es un lock producido por una sentencia DML (sentencias de manipulación de datos como select, insert, update, delete, etc), pero esto es porque fuimos nosotros los que ejecutamos la sentencia bloqueadora. Evidentemente este no va a ser siempre el caso, ya que una base de datos es normalmente compartida por multitud de usuarios. Afortunadamente, podemos encontrar la información que necesitamos en la tabla V$LOCK.

domingo, 2 de noviembre de 2014

Objetos bloqueados en una base de datos (1)

Todo el mundo ha intentado hacer alguna llamada desde su móvil y dicha llamada no ha podido realizarse por problemas de congestión, es decir, otros usuarios han copado los canales disponibles y nosotros no hemos podido tomar posesión de ninguno de ellos. Algo parecido puede ocurrir con las sesiones de Oracle (o de cualquier otra base de datos), ya que una sesión puede bloquear (mantener un "lock") un objeto de la base de datos (tabla, columna, etc) evitando que otra sesión pueda utilizarlo.

Bloqueos en una base de datos Oracle

En esta nota voy a contar como identificar que sesión es la causante del bloqueo y en posteriores mensajes iré más allá y contaré como identificar el objeto que está bloqueado.

jueves, 21 de agosto de 2014

Cómo analizar la interdepencia entre los objetos de una base de datos Oracle

Para analizar y conocer las dependencias existentes entre funciones, procedimientos, paquetes y triggers a los que tiene acceso un usuario de la base de datos Oracle se debe utilizar la vista USER_DEPENDENCIES.

USER_DEPENDENCIES

Esta vista, sin ir más lejos, se puede utilizar para realizar un análisis de nuestro código PL/SQL, permitiendo por ejemplo identificar que programas necesitan ser revisados y actualizados si realizamos algún tipo de cambio sobre una tabla determinada de la base de datos.

miércoles, 18 de junio de 2014

Cómo obtener información sobre los procedimientos, funciones y triggers PLSQL

Obtener información sobre Procedimientos y Funciones PL/SQL


La vista USER_PROCEDURES proporciona información sobre todas la funciones y procedimientos dentro de nuestro esquema, tanto a nivel de esquema como aquellas que se encuentran definidas dentro de los paquetes PL/SQL.

ALL_PROCEDURES

Las columnas más significativas dentro de esta vista son:
  • AUTHID: Identifica si el procedimiento o función se ejecutará con los permisos del usuario que lo llama (CURRENT_USER) o con los permisos del propietario del programa (DEFINER).
  • DETERMINISTIC: Esta columna toma el valor de YES si la función se ha definido como deterministas (deterministic), lo que teóricamente significa que el valor devuelto por la función PLSQL queda unívocamente determinado dependiendo de los valores que tomen los argumentos de la función.
  • PIPELINED: Cuando toma el valor de YES quiere decir que la función ha sido definida como pipelined, lo cual quiere decir que puede ejecutarse el paralelo como parte de una consulta con ejecución paralela.
  • OVERLOAD: Este campo tendrá un valor numérico positivo si el programa correspondiente esta overloaded, o lo que es lo mismo, cuando dentro del mismo paquete PLSQL existen al menos dos subprogramas con el mismo nombre (como podéis ver, la palabra overloaded casi nada tiene que ver con su traducción directa sobrecargado).

martes, 6 de mayo de 2014

Nueva cláusula BEQUEATH para las vistas (Oracle 12c)

Anteriormente a la versión 12c de las bases de datos Oracle, si desde una vista había que ejecutar una función PL/SQL siempre se invocaba con permisos del propietario de la vista, no los privilegios del propietario de la función. Esto implicaba que si la función había sido definida por el invocador, la conducta de la misma podía ser bastante diferente a lo esperado cuando se ejecutaba desde una vista.


Para solucionar este problema, la versión 12c de la base de datos Oracle incorpora la cláusula BEQUEATH que permite definir una vista para que si esta incluye funciones, los permisos de ejecución de las mismas se acomoden con los permisos del usuario que invoca la vista.

miércoles, 26 de marzo de 2014

Nuevos tipos de datos PL/SQL soportados por la versión 12c al asociar datos vía SQL (SQL binding)

Anteriormente a la versión 12c de la base de datos Oracle, siempre que era necesario asociar una variable a una expresión PL/SQL mediante el uso de la sentencia EXECUTE IMMEDIATE o del paquete DBMS_SQL, el tipo de datos PLSQL de dicha expresión debía ser un tipo de dato SQL permitido. Concretamente, no era posible asociar datos de tipo BOOLEAN, ni tampoco tipos de datos definidos por el usuarios que hubiesen sido declarados en la especificación de un paquete PL/SQL, incluyendo registros y colecciones.

Execute Inmediate en Oracle 12c

En la versión 12c se han eliminado prácticamente todas las restricciones de este tipo. Ahora, por ejemplo, se pueden asociar datos de tipo BOOLEAN para ejecutar un bloque de PLSQL dinámico con el comando EXECUTE INMEDIATE. Además, también se pueden asociar matrices asociativas y utilizarlas dentro de una llamada al operador TABLE. Ambas cosas no estaban soportadas por versiones anteriores a la 12c.

viernes, 17 de enero de 2014

Asignación de permisos (ROLES) a los programas PLSQL (mejoras versión 12c)

Con anterioridad a la versión 12c de la base de datos Oracle, un procedimiento PLSQL o unidad de programa con privilegios definidos a través de la cláusula AUTHID DEFINER siempre se ejecutaba con los privilegios del propietario del programa. Por otro lado, cuando la misma unidad de programa se definía utilizando la cláusula AUTHID CURRENT_USER, dicho programa siempre se ejecutaba con los privilegios del usuario que lo ejecutaba.

GRANT [nombre de role] TO [nombre de programa]

Disponer de solo estas dos formas de utilización de la cláusula AUTHID limitaba bastante la funcionalidad de las bases de datos Oracle en cuestión de seguridad, ya que cuando un usuario necesitaba ejecutar un programa PL/SQL determinado, dicho usuario tenía que tener los mismos privilegios que el propietario del programa. El problema de seguridad se agravaba mucho más cuando eran todos los usuarios los que necesitan tener acceso a dicho programa.

lunes, 28 de octubre de 2013

Cláusula WITH FUNCTION (mejoras en la versión 12c de la base de datos Oracle)

Mejoras en la versión 12c de las bases de datos Oracle
Arquitectura de la version 12c de bases de datos Oracle

Uso de funciones PLSQL dentro de una sentencia SELECT


Ya hace bastante tiempo que los programadores de bases de datos Oracle podemos llamar a nuestras propias funciones PL/SQL desde una sentencia SQL. Por ejemplo, supongamos que definimos la función PORCENTAJE que realiza una serie de cálculos para devolver un porcentaje. La función puede ser algo tan sencillo como:
FUNCTION porcentaje (
  val1 IN NUMBER,
  val2 IN NUMBER
)
RETURN NUMBER
IS
BEGIN
  RETURN (val1*100/(val1+val2));
END;

viernes, 11 de octubre de 2013

Uso de Rollback Segments por sentencias SELECT

Uso de Rollback Segments en sentencias SELECT
En alguna ocasión algún lector me ha preguntado, con cierta sorpresa, acerca del por qué una sentencia SELECT le fallaba con el mensaje de error "No es posible ampliar el segmento de rollback" ("Unable to extend rollback segment"). La sorpresa proviene del hecho de que son muchos los desarrolladores PL/SQL los que piensan que los segmentos de rollback sólo se utilizan cuando se emplean sentencias PLSQL en las que se modifican o actualizan datos dentro de la base de datos Oracle. Bajo este tipo de pensamiento es normal que, cuando se produce el error mencionado anteriormente al ejecutar una sentencia SELECT, uno se pregunte: ¿utiliza la base de datos Oracle segmentos de rollback al ejecutar sentencias SELECT?

Bueno, en mi opinión, lo primero que hay que hacer es reformular la pregunta y cambiarla por la siguiente: ¿una sentencia SELECT necesita crear o leer segmentos de rollback? La pregunta formulada de esta manera seguro que nos ayudará a comprender mejor este artículo, ya que el verbo "utilizar" usado en la primera pregunta no es lo suficientemente específico.

miércoles, 28 de agosto de 2013

Cómo obtener información sobre los objetos almacenados en las bases de datos Oracle

Ya hemos hablado en un artículo anterior sobre el diccionario de datos de las bases de datos Oracle, la vista USER_OBJECTS de dicho diccionario contiene un registro por cada objeto de la base de datos del que somos propietarios (o mejor dicho, del que es propietario el esquema con el que estamos conectados a dicha base de datos.

USER_OBJECTS
USER_OBJECTS (hacer clic sobre la imagen para ampliarla)

Las columnas que se utilizan de forma más común dentro de esta vista son:
  • OBJECT_NAME: nombre del objeto.
  • OBJECT_TYPE: tipo de objeto (toma valores como PACKAGE (paquete PLSQL), PROCEDURE (procedimiento), FUNCTION (función) o TRIGGER.
  • STATUS: muestra el estado del objeto que puede ser VALID (válido) o INVALID (inválido).
  • LAST_DDL_TIME: indica la última vez que el objeto fue cambiado.

viernes, 12 de julio de 2013

El diccionario de datos PL/SQL en las bases de datos Oracle

Si habéis llegado hasta este artículo es muy probable que tengáis que escribir en algunas ocasiones código PLSQL. Esto quiere decir que también, al menos ocasionalmente, necesitaréis analizar dicho código contestando cuestiones como:
  • ¿De qué objetos de la base de datos depende mi programa?
  • ¿Cuáles de mis paquetes PL/SQL contienen llamadas a subprogramas en otros paquetes o referencias a variables globales?
  • ¿Contiene alguno de mis subrutinas PLSQL parámetros con tipos de datos que no se deberían seguir utilizando?
  • ¿Están todo mi código compilado con el suficiente nivel de optimización?

Diccionario de datos PLSQL de las bases de datos Oracle

Obviamente, siempre podréis utilizar las funcionalidades de búsqueda de vuestro editor PL/SQL o de vuestro sistema integrado de desarrollo para navegar a través de los múltiples objetos y ficheros de la base de datos Oracle tratando de encontrar específicos pedazos de texto. Pero esto no sería suficiente para poder contestar todas las preguntas anteriores y algunas más que os pudieran surgir. Con este artículo y algunos más que escribiré más adelante podréis conocer las respuestas a dichas preguntas, y para ello hay que conocer en que consiste el diccionario de datos PLSQL de las bases de datos Oracle.

martes, 4 de enero de 2011

Bloqueo de tablas hijo por causa de ejecutar sentencias PL/SQL sobre tablas padre

Bloqueo de tablas hijo por ejecutar sentencias PLSQL sobre  tablas padreEn versiones de la base de datos Oracle anteriores a la 9i, cuando la clave primaria de una tabla padre (parent table) no se encuentra indexada en la tabla hijo (child table), es muy probable que tengamos problemas con los bloqueos de la tabla hijo que se producen, bien cuando se actualiza (con la sentencia PLSQL UPDATE) la clave primaria de la tabla padre (lo cual ocurre con relativa frecuencia ya que existen determinados trabajos que actualizan todas las columnas de una tabla incluso cuando el valor de la misma no ha cambiado), o bien cuando se realizaba el borrado (con la sentencia PL/SQL DELETE) de algún registro de la tabla padre.

El caso es que en las circunstancias anteriores y para evitar que se produzca un bloqueo completo de la tabla hijo (full table lock), lo más recomendable es indexar también en la tabla hijo la clave primaria de la tabla padre. No obstante, esta norma de bloqueo cambió con la versión 9i de la base de datos Oracle.

lunes, 18 de octubre de 2010

Base de datos Oracle 11g: nueva funcionalidad errorlogging del SQL*Plus

Gato durmiendo sobre teclado de programación PL/SQLHasta la versión de la base de datos Oracle 10g Release 2 no era posible capturar en SQL*Plus los errores que se generan cuando escribíamos incorrectamente una sentencia SQL. Es decir, los errores conocidos como SP2 no podían ser gestionados por las funcionalidades estándar del SQL*Plus OSERROR o SQLERROR. Esto era algo normal ya que los errores SP2 no son errores del tipo OS, como el típico error "unable to open spool file", ni tampoco son errores de tipo SQL o PLSQL, ya que la sentencia que hemos escrito incorrectamente en realidad no se trata de ningún comando SQL y nunca llegó a alcanzar la capa SQL o PL/SQL de la base de datos Oracle.

lunes, 7 de junio de 2010

Creación diferida de segmentos (Deferred Segment Creation), nueva funcionalidad de la release 2 de Oracle 11g

Programación PLSQL y la creación diferida de segmentosEn las versiones anteriores a la release 2 de la base de datos Oracle 11g, cuando se creaba cualquier objeto en la base de datos utilizando una sentencia SQL o PL/SQL, ya fuera un tabla, un índice o cualquier otro objeto que requiriese ser almacenado, el gestor de la base de datos creaba los segmentos necesarios y asignaba un tamaño inicial a los mismos, un tamaño que podía ser pequeño, de unos 64 Kbytes mínimo, pero dicho espacio ya no podía ser utilizado para otras necesidades. En los tiempos actuales, un tamaño de 64 Kbytes no es nada, pero si algo tan pequeño se tienen que repetir muchas veces, entonces el consumo de recursos de almacenamiento puede llegar a ser bastante grande.

Por ejemplo, podemos pensar en alguna aplicación que necesitase crear cientos o miles de tablas, tablas que nunca van a ser utilizadas pero cuyos segmentos de almacenamiento tienen que ser creados de todas formas. Algunos pueden preguntarse por qué un aplicación puede necesitar crear tablas que nunca van a ser utilizadas, este hecho no es tan extraño ya que existen muchas aplicaciones, como por ejemplo Oracle Financials, que ofrecen funcionalidades que requieren el uso de determinadas tablas y dichas tablas sólo contienen datos cuando se hace uso de dichas funcionalidades. Para evitar el consumo de almacenamiento en este tipo de situaciones, la release 2 de la base de datos Oracle 11g incorpora una nueva funcionalidad conocida como creación diferida de segmentos (Deferred Segment Creation), funcionalidad que a continuación explicaremos y analizaremos.