Oracle 23c - Sentencias SELECT sin cláusula FROM

En este artículo vamos a conocer un cambio introducido en Oracle Database 23c en la sintaxis de SQL y PL/SQL que permiten ejecutar sentencias SELECT sin la clausula FROM.

SELECT FROM DUAL

Como muchos de ustedes sabrán, hay muchas ocasiones donde deseamos mostrar algún dato que no proviene de una tabla en particular, siendo un ejemplo muy frecuente el querer obtener la fecha usando la función SYSDATE, o el resultado de cualquier otra función o expresión que no requiera datos de una tabla.

Para poder ejecutar un SELECT que no accede a ninguna tabla en particular, Oracle provee desde sus primeras versiones una tabla llamada DUAL que contiene una sola fila, la cual es utilizada para poder ejecutar un SELECT que no accede a datos de una tabla en particular, ya que la clausula FROM es obligatoria (o lo era, hasta Oracle Database 23c).

Con la sintaxis tradicional, para ver la fecha actual vamos a ejecutar lo siguiente:

SELECT SYSDATE FROM DUAL;

Y obtendremos el siguiente resultado:

SYSDATE            
-------------------
14-04-2023 18:52:22

Desde Oracle 10g, el optimizador de SQL de Oracle reconoce que efectivamente no queremos acceder a los datos de la tabla DUAL sino que sólo queremos devolver el resultado de una función, por lo que implementa una optimización que evita tener que acceder a la tabla. Esa optimización se llama FAST DUAL y la podemos corroborar viendo el plan de ejecución de la sentencia anterior:

Si bien esta mejora implica una ventaja en performance, todavía era necesario escribir la clausula FROM usando la tabla DUAL para que Oracle pudiera ejecutar este tipo de sentencias SELECT que no requieren acceder a ninguna tabla.

SELECT sin FROM en Oracle 23c

Oracle 23c ahora permite ejecutar sentencias SELECT sin necesidad de especificar una clausula FROM, como podemos ver a continuación:

SELECT SYSDATE;

Con esta nueva sintaxis simplificada obtendremos también el resultado deseado:

SYSDATE            
-------------------
14-04-2023 18:58:58

Por supuesto, se puede seguir usando la clausula FROM dual, pero ya no es requerido.

SELECT sin FROM en PL/SQL

Este cambio de Oracle Database 23c puede utilizase también en PL/SQL. Acá podemos ver un ejemplo donde usamos la función USER para mostrar que usuario esta conectado a la base de datos:

Que sucede en el trasfondo?

Para los que le genera interés saber como se implementa este cambio, podemos probar de ver el plan de ejecución de la sentencia para ver que operación realiza efectivamente. Si intentamos hacerlo con SQL Developer, recibiremos el siguiente error (ya que el cambio en la sintaxis todavía no es reconocido por esta versión de SQL Developer):

Pero podemos usar la funcionalidad EXPLAIN PLAN directamente para ver el plan de ejecución:

EXPLAIN PLAN FOR SELECT SYSDATE;
SELECT plan_table_output FROM TABLE(dbms_xplan.display('plan_table',null,'basic'));

Al usar EXPLAIN PLAN y consultar el resultado del mismo, obtendremos lo siguiente (el resaltado en rojo fue agregado por mi):

Explained.

PLAN_TABLE_OUTPUT
---------------------------------------------------
Plan hash value: 1388734953
 
---------------------------------
| Id  | Operation        | Name |
---------------------------------
|   0 | SELECT STATEMENT |      |
|   1 |  FAST DUAL       |      |
---------------------------------

Aquí nos queda claro que el plan es el mismo que cuando usamos la cláusula FROM, ya que está usando FAST DUAL. Esto nos demuestra que Oracle internamente transforma la sentencia para incluir FROM DUAL, sin necesidad de que nosotros lo escribamos en el código.

Conclusión

Esta nueva funcionalidad nos permite escribir sentencias SELECT más cortas sin necesidad de usar la tabla DUAL, lo cual suele generar desconcierto en aquellas personas que recién se introducen en el mundo Oracle.

Si desean conocer más sobre Oracle 23c, es recomendable que vean estos artículos en este blog como punto de partida:

• Oracle Database 23c Free - Developer Edition.
• Instalando Oracle Database 23c (Free Developer Edition) en Oracle Cloud.

Adicionalmente, pueden consultar todos los artículos relacionados a Oracle Database 23c agrupados en en el tag Database 23c.

Oracle 23c - Nuevo Rol DB_DEVELOPER_ROLE

Comenzando con la serie de artículos sobre Oracle Database 23, vamos a analizar uno de los cambios que la misma ofrece, que es el nuevo rol DB_DEVELOPER_ROLE.

Nuevo Rol DB_DEVELOPER_ROLE

El mismo permite otorgar a un usuario un conjunto de roles y privilegios acorde a lo que Oracle considera lógico para poder desarrollar en una base de datos, reemplazando a los viejos y conocidos roles CONNECT y RESOURCE.

Permisos y Roles Incluidos

Podemos revisar los roles y permisos incluidos en este nuevo rol usando la siguiente consulta:

SELECT 'Privilegio de Sistema' AS PrivType, p.privilege, 'Ninguno' AS Object
FROM dba_sys_privs p
WHERE p.grantee = 'DB_DEVELOPER_ROLE'
UNION ALL
SELECT 'Roles' AS PrivType, r.granted_role, 'Ninguno' AS Object
FROM dba_role_privs r
WHERE r.grantee = 'DB_DEVELOPER_ROLE'
UNION ALL
SELECT 'Privilegio de Objeto' AS PrivType, op.privilege, op.table_name 
FROM dba_tab_privs op
WHERE op.grantee = 'DB_DEVELOPER_ROLE'
ORDER BY 1, 2, 3;

La misma nos devuelve un total de 30 permisos o roles asignados al nuevo rol, como vemos a continuación:

PRIVTYPE                       PRIVILEGE                      OBJECT                        
------------------------------ ------------------------------ ------------------------------
Privilegio de Objeto           EXECUTE                        JAVASCRIPT                    
Privilegio de Objeto           READ                           V_$PARAMETER                  
Privilegio de Objeto           READ                           V_$STATNAME                   
Privilegio de Objeto           SELECT                         DBA_PENDING_TRANSACTIONS      
Privilegio de Sistema          CREATE ANALYTIC VIEW           Ninguno                       
Privilegio de Sistema          CREATE ATTRIBUTE DIMENSION     Ninguno                       
Privilegio de Sistema          CREATE CUBE                    Ninguno                       
Privilegio de Sistema          CREATE CUBE BUILD PROCESS      Ninguno                       
Privilegio de Sistema          CREATE CUBE DIMENSION          Ninguno                       
Privilegio de Sistema          CREATE DIMENSION               Ninguno                       
Privilegio de Sistema          CREATE DOMAIN                  Ninguno                       
Privilegio de Sistema          CREATE HIERARCHY               Ninguno                       
Privilegio de Sistema          CREATE JOB                     Ninguno                       
Privilegio de Sistema          CREATE MATERIALIZED VIEW       Ninguno                       
Privilegio de Sistema          CREATE MINING MODEL            Ninguno                       
Privilegio de Sistema          CREATE MLE                     Ninguno                       
Privilegio de Sistema          CREATE PROCEDURE               Ninguno                       
Privilegio de Sistema          CREATE SEQUENCE                Ninguno                       
Privilegio de Sistema          CREATE SESSION                 Ninguno                       
Privilegio de Sistema          CREATE SYNONYM                 Ninguno                       
Privilegio de Sistema          CREATE TABLE                   Ninguno                       
Privilegio de Sistema          CREATE TRIGGER                 Ninguno                       
Privilegio de Sistema          CREATE TYPE                    Ninguno                       
Privilegio de Sistema          CREATE VIEW                    Ninguno                       
Privilegio de Sistema          DEBUG CONNECT SESSION          Ninguno                       
Privilegio de Sistema          EXECUTE DYNAMIC MLE            Ninguno                       
Privilegio de Sistema          FORCE TRANSACTION              Ninguno                       
Privilegio de Sistema          ON COMMIT REFRESH              Ninguno                       
Roles                          CTXAPP                         Ninguno                       
Roles                          SODA_APP                       Ninguno                       

30 rows selected. 

Estos permisos son mas que aquellos que poseen los roles CONNECT y RESOURCE combinados, por lo que es probable que se deba considerar si es necesario otorgar este nuevo rol, si se debe mantener el uso de CONNECT y RESOURCE, o si es mejor crear un rol especifico en base a los permisos que nuestra organización quiera otorgar.

Utilización

Este rol se otorga en forma normal, como cualquier otro rol de Oracle database. En el ejemplo siguiente vamos a crear un usuario Demo23c y vamos a otorgarle el rol:

CREATE USER Demo23c IDENTIFIED BY Pwd23cDemo;
GRANT DB_DEVELOPER_ROLE TO Demo23c;

El resultado es el siguiente:

User DEMO23C created.

Grant succeeded.

Conclusión

Este nuevo rol nos permite asignar rápidamente un conjunto de permisos necesario para poder crear objetos usados frecuentemente en un esquema de bases de datos (tablas, vistas, procedimientos, etc.). Si bien en una base de datos de producción es recomendable otorgar sólo los permisos estrictamente necesarios, para un entorno de desarrollo resulta rápido y eficiente crear usuarios y otorgarles este nuevo rol para que puedan crear los objetos que necesitan sin necesidad de pedir nuevos privilegios al DBA.

Si desean conocer más sobre Oracle 23c, es recomendable que vean estos artículos en este blog como punto de partida:

• Oracle Database 23c Free - Developer Edition.
• Instalando Oracle Database 23c (Free Developer Edition) en Oracle Cloud.

Adicionalmente, pueden consultar todos los artículos relacionados a Oracle Database 23c agrupados en en el tag Database 23c.

Habilitando puerto 1521 (Oracle Listener) en VM de Oracle Cloud

Para poder conectarnos directamente con SQL Developer u otro cliente Oracle a una base de datos que reside en una VM de Oracle Cloud, necesitamos hacer algunos ajustes tanto a la máquina virtual como a la red de Oracle Cloud donde se encuentra la misma.

Esto es recomendado SOLAMENTE para ENTORNOS de PRUEBA, no debiéndose permitir el acceso directo al Listener de Oracle en equipos de producción.

Habilitar el puerto en el Firewall de la Máquina Virtual

El primer paso es habilitar el puerto 1521 en el firewall de la VM donde tenemos la base de datos. Para ello es necesario ejecutar los siguientes comandos conectados con el usuario su de Linux:

firewall-cmd --zone=public --add-port=1521/tcp --permanent
firewall-cmd --reload

Ambos deben devolver el estado "success" confirmando que el puerto fue habilitado y el firewall fue recargado.

Habilitar el puerto en la VCN (Virtual Cloud Network) de Oracle Cloud

El paso siguiente es configurar la red para que permita el acceso al puerto deseado. Desde la página principal de la VM en cuestión, podemos identificar rápidamente en que red se encuentra la misma y haciendo click en su nombre podemos ir a la pagina de configuración de la misma:

Una vez en la página de la Red, debemos seleccionar la opción "Listas de Seguridad" disponible en el menu de la izquierda, para ver las listas de seguridad disponibles:

Seleccionaremos la lista de seguridad por defecto (que se crea junto con la Red) o en caso de haber configurado listas de seguridad adicionales, aquella en la queramos habilitar el puerto:

Ahora tenemos que crear una nueva regla de entrada para la lista, presionando el botón "Agregar reglas de entrada":

Esto habilitará el asistente para crear una Regla de Entrada, el cual deberemos completar con los siguientes datos:

Debemos ingresar 0.0.0.0/0 en el CIDR de Origen para permitir el acceso desde cualquier dirección IP y debemos especificar el puerto 1521 (o el que deseamos habilitar) como puerto destino. Tambien es recomendable describir el uso que se le dará al puerto para facilitar el monitoreo de los puertos habilitados en el futuro.

Al presionar "Agregar regla de entrada" la misma será creada y habilitada automáticamente, por lo que podremos acceder al puerto deseado desde cualquier ubicación en la red.

Instalando Oracle Database 23c (Free Developer Edition) en Oracle Cloud

En este artículo vamos a explicar los pasos requeridos para instalar Oracle Database 23c (Free Developer Edition) en un servidor con Oracle Linux 8 residiendo en Oracle Cloud Infrastructure.

No hay ninguna diferencia en particular entre la instalación en Oracle Cloud y la instalación On-Premise.

Los pasos para crear una instancia de Oracle Linux 8 en Oracle Cloud los pueden ver en el artículo "Creando una VM con Oracle Linux 8 en Oracle Cloud Infrastructure". En el final del mismo también pueden ver una explicación sobre cómo conectarse a la instancia.

Pre-requisitos y Descarga de Instalador

Habilitar Oracle Developer Repository en Oracle Linux

El primer paso para instalar Oracle Database 23c en Oracle Linux (una vez conectados a la VM y utilizando el usuario su), es habilitar el repositorio Developer de Oracle Linux 8:

yum config-manager --set-enabled ol8_developer

Instalando Pre-requisitos

Oracle provee un paquete de preinstalación para cada versión de Oracle Database adaptado a la versión de Oracle Linux que estemos utilizando. El mismo se puede instalar en forma sencilla con el comando yum, como vemos a continuación:

yum -y install oracle-database-preinstall-23c

El proceso de instalación de componentes requeridos y configuración de parámetros del sistema operativo lleva un par de minutos, tras los cuales verán una pantalla como la siguiente:

Descarga de Instalador

El instalador de Oracle Database 23c se puede descargar en forma automática desde el sitio de OTN de Oracle, usando el comando wget:

wget https://download.oracle.com/otn-pub/otn_software/db-free/oracle-database-free-23c-1.0-1.el8.x86_64.rpm

El archivo a descargar tiene un tamaño aproximado de 1.6 Gb.

Nota: se recomienda crear un directorio de descargas y realizar la descarga en el mismo. 

Instalación

Para instalar Oracle Database 23c, solo debemos ejecutar el comando yum con la opción install y el instalador (archivo rpm) que acabamos de descargar:

yum -y install oracle-database-free-23c-1.0-1.el8.x86_64.rpm

Tras unos minutos, la instalación finaliza como podemos ver en la imagen:

El software de Oracle Database 23c va a ser copiado en la ubicación "/opt/oracle/product/23c/dbhomeFree".

Configuración de la Base de Datos

Una vez instalado el software, solo resta configurar la base de datos. Este proceso crea una base de datos Contenedor (CDB) llamada FREE, una base de datos Pluggable (PDB) llamada FREEPDB1 y configura también el Listener para que acepte conexiones en el puerto por defecto de Oracle (1521).

Este script debe ejecutarse con el usuario root de la siguiente manera:

/etc/init.d/oracle-free-23c configure

El mismo solicita ingresar una contraseña para los usuarios SYS / SYSTEM / PDBADMIN y luego hace la creación de la base de datos en unos pocos minutos.

Post-Instalación

Una vez instalado el software y configurada la base de datos, sólo resta configurar la opción de auto-inicio para que la base de datos sea iniciada y apagada automáticamente al iniciar o apagar la VM. Para ello debemos ejecutar el siguiente comando:

systemctl enable oracle-free-23c

Ante lo cual recibiremos el siguiente mensaje:

[root@ol8db23c download]# systemctl enable oracle-free-23c
oracle-free-23c.service is not a native service, redirecting to systemd-sysv-install.
Executing: /usr/lib/systemd/systemd-sysv-install enable oracle-free-23c

Actualización 2024-03-24 - Habilitando el Firewall

Es probable que tengamos que habilitar el puerto 1521 en el Firewall de OL8 para poder conectarnos en forma remota a la base de datos. Se hace de la siguiente manera:

firewall-cmd --permanent --zone=public --add-port=1521/tcp
firewall-cmd --reload

Se puede validar el estado del mismo de la siguiente manera:

firewall-cmd --permanent --zone=public --list-ports

Conectándose a Oracle Database 23c

Ahora que hemos creado la base de datos, podemos comenzar a utilizarla. Para ello debemos configurar algunas variables  de entorno (es recomendable configurar las mismas en el perfil de bash - archivo .bash_profile - de los usuarios de de forma tal que no tengamos que repetir los pasos cada vez que queramos conectarnos):

#Variables de Entorno para Oracle 23c
export ORACLE_SID=FREE 
export ORACLE_HOME=/opt/oracle/product/23c/dbhomeFree 
export ORAENV_ASK=NO 
export PATH=$ORACLE_HOME/bin:$PATH

Una vez configurado el entorno, nos conectamos a la base de datos PDB de la siguiente manera:

sqlplus sys@localhost:1521/FREEPDB1 as sysdba

Y rápidamente podemos validar que estamos conectados a la PDB en una base de datos 23c!

Para conectarse a la CDB, podemos hacerlo de la siguiente manera:

sqlplus sys@localhost:1521/FREE as sysdba

Resumen

Como resumen de este artículo, resulta por demás de sencillo instalar y configurar una base de datos Oracle 23c en un servidor Oracle Linux 8, tanto en Oracle Cloud como en nuestros propios servidores on-premise si los tuviéramos.

En los próximos días voy a estar generando nuevos artículos explorando todas las características destacadas de Oracle Database 23c Free Developer Edition!

Creando una VM con Oracle Linux 8 en Oracle Cloud Infrastructure

En este artículo voy a explicar como crear una máquina virtual con Oracle Linux 8 en Oracle Cloud Infrastructure para poder instalar la Oracle Database 23c Free Developer Edition en ella (proceso descrito en el artículo "Instalando Oracle Database 23c (Free Developer Edition) en Oracle Cloud"), aunque los pasos son genéricos para cualquiera VM que quieran crear.

Cabe mencionar que el tipo de máquina virtual que vamos a crear no está disponible en las cuentas Oracle Cloud Always Free.

Creando la VM

Para crear una máquina virtual en Oracle Cloud, el primer paso consiste en ir a la sección de Instancias (el cual yo ya tengo "fijado") en el menú principal de Oracle Cloud:

Una vez en la sección de Instancias, debemos presionar el botón "Crear Instancia" para iniciar el asistente de creación de instancias:

Nombre, Compartimento, Ubicación y Seguridad

La primera sección del asistente nos permite definir un nombre para nuestra instancia así como elegir el compartimento donde residirá la misma (elegimos el compartimento Blog para no mezclarla con otras instancias).

Adicionalmente, podemos elegir una dominio de disponibilidad dentro de la región de Oracle Cloud en la que estamos trabajando y opciones de capacidad y seguridad avanzadas que vamos a mantener en sus valores por defecto:

Imagen y Unidad

En esta sección vamos a definir qué imagen de Sistema Operativo vamos a utilizar (mantendremos Oracle Linux 8) y qué recursos de hardware va a contar nuestra instancia. Para ello vamos a presionar la opción "Editar" como se muestra a continuación:

Paso siguiente vamos a seleccionar "Change Shape" para cambiar el formato de nuestra instancia:

A continuación el asistente nos mostrará una lista de posibles formatos disponibles, vamos a mantener la opción de máquina virtual pero vamos a elegir la opción "Especialidad y Generación Anterior" para ver opciones acordes a nuestras necesidades:

Desplazandonos hacia abajo, vamos a elegir la opción "VM.Standard.E1" que cuenta con los recursos necesarios para poder instalar Oracle Database 23c y luego presionaremos "Seleccionar unidad" para terminar de definir el formato y volver al asistente de creación de la VM:

Red

El paso siguiente es configurar la red donde va a residir nuestra instancia, por lo que presionaremos "Editar" para ingresar las opciones de la misma:

Vamos a seleccionar crear una nueva red y una nueva subred pública dentro de ella, e ingresaremos los nombres de las mismas. Dejaremos la opción de generar una dirección IP pública habilitada como viene por defecto:

Luego, en las opciones avanzadas, vamos a ingresar un nombre de host para identificar nuestra instancia (uno que sea sencillo para nosotros):

Claves SSH

En la siguiente sección, vamos a mantener la opción de generar el par de claves privada/pública y vamos a descargar ambas:

Volumen de Inicio

Si bien por defecto las VM se crean con un volumen de inicio de 50 gigabytes, suficientes para instalar Oracle Database 23c, vamos a aumentar el mismo a 100 gigabytes para poder "jugar" tranquilos:

Confirmando la creación

En nuestro caso, no vamos a configurar ninguna de las opciones avanzadas, por lo que solo nos resta presionar el botón "Crear" para dar comienzo a la creación de la VM:

Automáticamente se dará comienzo al proceso de creación de la instancia, tal como vemos a continuación, y en algo menos de un minuto la misma estará disponible:

Nota: Destacamos en verde la dirección IP pública de nuestra instancia, la cual necesitaremos para acceder a ella remotamente.

Usando la Instancia

Una vez que la instancia está disponible, podemos conectarnos a ella usando ssh o putty desde nuestras computadoras, usando la siguiente sintaxis

ssh -i <clave-publica> opc@<ip>

Solo tenemos que reemplazar <clave-publica> por el archivo de clave pública que descargamos y reemplazar <ip> por la dirección IP que obtuvimos en el paso anterior, tal como mostramos a continuación:

Actualizando la Instancia

Es recomendable, una vez creada, actualizar la misma con los últimos patches disponibles, para eso debemos utilizar el usuario su de Linux, al que accedemos mediante el comando sudo:

[opc@ol8db23c ~]$ sudo su

Y luego ejecutar "yum update" para que Oracle Linux busque los componentes y actualizaciones necesarios:

[root@ol8db23c opc]# yum update

Tras aceptar la descarga y actualización, Oracle Linux procedera a ser actualizado (lo cual tarda unos minutos) tras lo cual veremos una pantalla como la siguiente indicando que la actualización a finalizado: