Como retornar resultados implícitamente de un SELECT usando procedimientos almacenados

Oracle desde la versión 11g permite retornar un conjunto resultado de una sentencia SELECT de modo implícito (sin usar una variable de tipo sys_refcursor como parámetro de salida) invocando a la función RETURN_RESULT del paquete DBMS_SQL.

SYS_REFCURSOR es un tipo de datos que representa un cursor o área de memoria donde se almacena temporalmente un conjunto resultado permitiendo recuperar una a una cada fila.

 

A continuación se presenta un ejemplo que recupera filas de la tabla empleados:

create or replace procedure select_employees as

  rf_cursor_employees SYS_REFCURSOR;
begin
 
  open rf_cursor_employees for

    SELECT employee_id,first_name,last_name,salary,email,phone_number
      FROM HR.employees
    ORDER BY last_name,first_name;
   
    dbms_sql.return_result(rf_cursor_employees);
   
end select_employees;

Compilar  con Oracle SQL Developer o SQL*Plus:

Para su ejecución, se invoca al procedimiento SELECT_EMPLOYEES:

exec select_employees; 

Se puede recuperar más de un conjunto resultado al igual que se lo hace con la forma tradicional empleando variables de tipo  sys_refcursor en lugar de parámetros como se ilustra en el siguiente caso :

create or replace procedure select_multiples_resultados as
 rf_cursor_departments SYS_REFCURSOR;
 rf_cursor_employees SYS_REFCURSOR;
begin
  open rf_cursor_departments for
     SELECT
         department_id,department_name
     FROM HR.departments
     ORDER BY department_name;
    
    dbms_sql.return_result(rf_cursor_departments);

  open rf_cursor_employees for
    SELECT employee_id,first_name,last_name,salary,email,phone_number
    FROM HR.employees
    ORDER BY last_name,first_name;
   
    dbms_sql.return_result(rf_cursor_employees);
   
end select_multiples_resultados;

 

Compilar con SQL Developer o SQL*PLus.

Ejecutar el procedimiento:

exec select_multiples_resultados;

Conclusión:

Por medio de la función RETURN_RESULT se puede obtener uno o varios conjuntos resultado sin declarar parámetros de tipo SYS_REFCURSOR en los  procedimientos, sino, como variables locales del mismo facilitando el trabajo de codificación.

 

Nota:

Les comparto el enlace para saber como llamar estos procedimientos que retornan múltiples recordsets desde JDBC.

Cómo desbloquear una cuenta de usuario en Oracle XE 18c usando SQL Developer

Para poder desbloquear una cuenta de usuario Oracle empleando SQL Developer, se debe crear una conexión basada en un usuario con privilegios DBA como SYS o SYSTEM.

En este post  se usa una conexión basada en el usuario SYSTEM de la base de datos XEPDB1 instalada por defecto y que contiene al esquema HR(la base de datos contenedora de nombre XE y referida como CDB$ROOT no contiene HR).

1) Abrir la conexión basada en SYSTEM seleccionándola y haciendo click con botón secundario.

2) Ingresar la contraseña y hacer click en Ok.

 

3) Seleccionar Otros usuarios.

4) En el cuadro de diálogo Editar usuario, deseleccionar la casilla de comprobación Bloquedo haciendo click sobre ella.

5) Click sobre el botón Aplicar.

6) Click en Ok.

Cómo desbloquear una cuenta de usuario en Oracle XE 18c

Una cuenta de usuario Oracle puede estar bloqueada desde el momento de su creación o por algún otro factor, por tanto, para desbloquear una cuenta se debe iniciar sesión con un usuario administrador como SYS, SYSTEM o cualquier otra cuenta con privilegios DBA.

Para ejemplificar se iniciará sesión como SYS desde SQLPLUS usando la base de datos XEPDB1 donde se almacena el esquema HR:

1)Ejecutar SQL PLUS desde CMD(Windows) o Terminal(Linux)

2)Ejecutar el siguiente comando

sqlplus/as nolog

3)Igresar como user-name: sys as sysdba, pulsar INTRO e ingresar la clave, después pulsar INTRO.

4)Cambiar la sesión usando la base de datos XEPDB1 donde se almacena el esquema HR.

alter session set container=XEPDB1; 

5)Submitir la sentencia alter user siguiente y pulsar ENTER:

alter user HR
identified by HR
account unlock;

6)Comprobar si la acción ejecutada surtió efecto conectándose como HR:

conn HR/HR@localhost:1521/XEPDB1

Como se aprecia en la última imagen, la cuenta HR fue desbloqueada y se pudo iniciar sesión con ella.

Otro modo de hacer lo mismo conectándose directamente a XEPDB1:

*Repetir las instrucciones 1 y 2.

*Submitir el comando de conexión siguiente:

conn sys/suclave@localhost:1521/XEPDB1 AS SYSDBA

*Repetir las instrucciones 5 y 6.

Espero este post sea de utilidad.

Saludos