sqlplus

PROPÓSITO

notas sobre pl/sql

OBJETIVOS

plantilla de paquete, función, y procedimiento con tratamiento de errores
una ayuda por pantalla que con el menú genérico muestre una que se llama con el nombre de la pantalla como parámetro de forma que muestra desde base de datos lo que se quiere mostrar en la ayuda
ver el fallo del trigger sobre la tabla AUD cuando la tabla esta vacía.
la parte general del paquete plantilla sacarla a otro paquete por ejemplo utilidades.
llevar estos paquetes a un repositorio privado en Bitbucket junto con lo de las gráficas
entender la inyección de dependencias y ver como aplicarlo a los programas del curro

COLUMNAS DE TABLA EN CHAR

si se define una columna de una tabla como de tipo CHAR y no VARCHAR2 se provoca que el valor ocupe todo el tamaño con espacios en blanco lo que provoca problemas

PLANTILLA DE PAQUETE

en el esquema de la aplicacion base gab

PAQUETE DE PROPÓSITO GENERAL

general a todos los proyectos SIGI

`REF CURSOR`

un post sobre este tema en castellano
los ref cursores

-- DESDE SQL PLUS
SQL> conn gab/desarrollo@xe
Connected.
SQL> SHOW USER
USER is "GAB"
SQL> VAR RC REFCURSOR;
SQL> exec :rc := gab.gab_pck_alumno.f_buscar_alumnos (null,null,1);

PL/SQL procedure successfully completed.

SQL> print rc

GAB_ALUMNO
--------------------------------------------------
GAB_DESCRIPCION
--------------------------------------------------------------------------------
ALUMNO1
DESCRIPCION DEL ALUMNO 1

ALUMNO2
DESCRIPCION DEL ALUMNO 2

ALUMNO3
DESCRIPCION DEL ALUMNO 3

SQL> print rc
SP2-0625: Error printing variable "rc"
SQL>

el ref cursor no contiene los datos resultado de la consulta sino que contiene un puntero a la consulta y por eso el segundo print da error
el primer print funciona porque SQL Plus mira el cursor de referencia y ve que es un cursor abierto pues la sentencia no lo cierra.
Entra en el bucle, obteniendo cada fila de datos de la base de datos, utilizando el cursor de referencia como referencia (puntero) a la consulta relevante, y mostrando cada fila de datos hasta que no tenía más filas para recuperar.
Una vez que se han obtenido todas las filas, cierra el cursor.

SQL> ed
Wrote file afiedt.buf

  declare
      c_alumnos    sys_refcursor;
    begin
      c_alumnos := gab.gab_pck_alumno.f_buscar_alumnos (null,null,1); -- This returns an open cursor
      dbms_output.put_line('Alumnos: '||c_alumnos%ROWCOUNT);
      close c_alumnos;
  end;
SQL> /
Alumnos: 0

PL/SQL procedure successfully completed.

sale 0 a pesar de que existen 3 alumnos por que c_alumnos%ROWCOONT muestra el número de fila leídas a través del cursos y no hemos leído ninguna.

Wrote file afiedt.buf

declare
    c_alumnos    sys_refcursor;
    v_id_alumno  varchar2(50);
    v_descripcion varchar2(200);
  begin
    c_alumnos := gab.gab_pck_alumno.f_buscar_alumnos (null,null,1);  -- This returns an open cursor
    dbms_output.put_line('Pre Fetch: Alumnos: '||c_alumnos%ROWCOUNT);
    fetch c_alumnos into v_id_alumno, v_descripcion;
    dbms_output.put_line('Post Fetch: Rows: '||c_alumnos%ROWCOUNT);
    close c_alumnos;
 end;
SQL> /
Pre Fetch: Alumnos: 0
Post Fetch: Rows: 1

PL/SQL procedure successfully completed.

ahora sí que ha leído una fila del cursor.

SQL> ed
Wrote file afiedt.buf
declare
    c_alumnos    sys_refcursor;
    v_id_alumno  varchar2(50);
    v_descripcion varchar2(200);
  begin
    c_alumnos := gab.gab_pck_alumno.f_buscar_alumnos (null,null,1);  -- This returns an open cursor
    dbms_output.put_line('Pre Fetch: Alumnos: '||c_alumnos%ROWCOUNT);
    loop
      fetch c_alumnos into v_id_alumno, v_descripcion;
      exit when c_alumnos%NOTFOUND;
      dbms_output.put_line('Post Fetch: Rows: '||c_alumnos%ROWCOUNT||' '||v_id_alumno||' '||v_descripcion);
    end loop;
    close c_alumnos;
 end;
SQL> /
Pre Fetch: Alumnos: 0
Post Fetch: Rows: 1 ALUMNO1 DESCRIPCION DEL ALUMNO 1
Post Fetch: Rows: 2 ALUMNO2 DESCRIPCION DEL ALUMNO 2
Post Fetch: Rows: 3 ALUMNO3 DESCRIPCION DEL ALUMNO 3

PL/SQL procedure successfully completed.

-- DESDE EPACF
/* Formatted on 18/12/2017 10:12:13 (QP5 v5.287) */
DECLARE
   RetVal                       EPACF.EPACF_PCK_ACTO_CANDIDATO_VAC.cursorActos;
   P_ID_CURSO                   NUMBER;
   P_ID_ACTO                    NUMBER;
   P_ID_VACANTE                 NUMBER;
   V_DTP_NIF                    VARCHAR2( 200 );
   V_DTP_NOMBRE                 VARCHAR2( 200 );
   V_DTP_APELLIDO1              VARCHAR2( 200 );
   V_DTP_APELLIDO2              VARCHAR2( 200 );
   V_DTP_FECHA_NACIMIENTO       VARCHAR2( 200 );
   V_DTP_TELEFONO               VARCHAR2( 200 );
   V_DTP_EMAIL                  VARCHAR2( 200 );
   V_ACF_ID_COLECTIVO           VARCHAR2( 200 );
   V_ACF_DESCRIPCION            VARCHAR2( 200 );
   V_ACF_TIPO_RESOLUCION        VARCHAR2( 200 );
   V_ACF_DESC_TIPO_RESOLUCION   VARCHAR2( 200 );
   V_DTM_GESTOR                 VARCHAR2( 200 );
   V_DTM_DESC_GESTOR            VARCHAR2( 200 );
   V_ACF_ID_ESTADO              VARCHAR2( 200 );
   V_ACF_DESC_ESTADO            VARCHAR2( 200 );
BEGIN
   P_ID_CURSO := 2017;
   P_ID_ACTO := 1;
   P_ID_VACANTE := 10003;

   RetVal :=
      EPACF.EPACF_PCK_ACTO_CANDIDATO_VAC.F_OBTENER_ACTO_CANDIDATOS_VAC
      (
         P_ID_CURSO,
         P_ID_ACTO,
         P_ID_VACANTE
      );


   DBMS_OUTPUT.PUT_LINE
   (
      'El resultado ' || RETVAL%ROWCOUNT
   );

   LOOP
      FETCH RETVAL
         INTO V_DTP_NIF,
              V_DTP_NOMBRE,
              V_DTP_APELLIDO1,
              V_DTP_APELLIDO2,
              V_DTP_FECHA_NACIMIENTO,
              V_DTP_TELEFONO,
              V_DTP_EMAIL,
              V_ACF_ID_COLECTIVO,
              V_ACF_DESCRIPCION,
              V_ACF_TIPO_RESOLUCION,
              V_ACF_DESC_TIPO_RESOLUCION,
              V_DTM_GESTOR,
              V_DTM_DESC_GESTOR,
              V_ACF_ID_ESTADO,
              V_ACF_DESC_ESTADO;

      EXIT WHEN RETVAL%NOTFOUND;

      DBMS_OUTPUT.PUT_LINE
      (
            'El resultado '
         || RETVAL%ROWCOUNT
         || ' '
         || V_DTP_NIF
         || ' V_DTP_NIF '
         || V_DTP_NOMBRE
         || ' V_DTP_NOMBRE '
         || V_DTP_APELLIDO1
         || ' V_DTP_APELLIDO1 '
         || V_DTP_APELLIDO2
         || ' V_DTP_APELLIDO2 '
         || V_DTP_FECHA_NACIMIENTO
         || ' V_DTP_FECHA_NACIMIENTO '
         || V_DTP_TELEFONO
         || ' V_DTP_TELEFONO '
         || V_DTP_EMAIL
         || ' V_DTP_EMAIL '
         || V_ACF_ID_COLECTIVO
         || ' V_ACF_ID_COLECTIVO '
         || V_ACF_DESCRIPCION
         || ' V_ACF_DESCRIPCION '
         || V_ACF_TIPO_RESOLUCION
         || ' V_ACF_TIPO_RESOLUCION '
         || V_ACF_DESC_TIPO_RESOLUCION
         || ' V_ACF_DESC_TIPO_RESOLUCION '
         || V_DTM_GESTOR
         || ' V_DTM_GESTOR '
         || V_DTM_DESC_GESTOR
         || ' V_DTM_DESC_GESTOR '
         || V_ACF_ID_ESTADO
         || ' V_ACF_ID_ESTADO '
         || V_ACF_DESC_ESTADO
      );
   END LOOP;
--COMMIT;
END;

de esta forma se lee el contenido del cursor
- funciona tanto en SQL como desde el TOAD
ver una forma rápida de sacar la información que retorna una función con un refcursor con muchos campos.
- ¿sacando los campos de la select que crea el cursor? EER * [Error] Execution (2: 1): ORA-06504: PL/SQL: Los tipos de retorno de variables de juego de resultados o la consulta no coinciden ORA-06512: en línea 123

FORMA SENCILLA

la forma más sencilla es ejecutando cualquiera de esta select sobre funciones que retornan refcursores

-- DESDE GAB 
select gab.gab_pck_adm_permiso.f_obtener_permisos_usuario ('jiranzo') from dual;
-- DESDE EPACF
SELECT EPACF.EPACF_PCK_ACTO.F_OBTENER_ACTOS_ACTIVOS FROM DUAL;
SELECT EPACF.EPACF_PCK_ACTO_CANDIDATO_COL_6.F_OBTENER_ACTO_CANDIDATOS_COL(2017,6,6,'PROVISIONAL') FROM DUAL;

luego doble click donde pone (CURSOR)

REFCURSOR DESDE TOAD

lanzarlo directamente sobre el editor en Toad
para procedimientos que tiene un REFCURSOR como parámetro de salida se puede usar este sistema

create or replace function f_refcur return sys_refcursor
is
 v_refcur sys_refcursor;
  Begin
    v_refcur := gab.gab_pck_adm_permiso.f_obtener_permisos_usuario ('jiranzo');
    return v_refcur;
  End;
select f_refcur from dual;

select gab.gab_pck_adm_permiso.f_obtener_permisos_usuario ('jiranzo') from dual;

una vez que tenemos el contenido del REFCURSOR podemos seleccionar todas las columnas en la salida

REFCURSOR DESDE TOAD

SPOOL

para crearlo desde PL/SQL Developer Archivo / Nuevo / ventana de comandos
configuración de spool
- pagina oficial de oracle
para configurar una salida y generar por ejemplo un conjunto de inserts

spool c:\inicio\ficheroSpool.sql

SET HEADING OFF
SET FEEDBACK OFF
SET LINESIZE 1000
SET TERMOUT OFF  -- anula la salida por pantalla lo que reduce el tiempo de creación del fichero
PROMPT SET TERMOUT OFF
SELECT 'insert into T_PET  (cod_cue, cod_esp, pos_can, nif, nombre, pos_pet,vac, EST_PET,num_fila) values (''' || P.COD_CUE || ''' , ''' || P.COD_ESP || ''' , ' ||POS_CAN || ' , ''' || P.NIF || ''' , ''' || P.NOMBRE || ''' , ' ||POS_PET || ' , ' || VAC || ' , ' || 0 || ' ,  ' || NUM_FILA || ' );' || DECODE(MOD(ROWNUM, 10), 0, CHR(10) ||  'COMMIT;', NULL)  FROM T_PET P WHERE P.NUM_FILA  < 11 ORDER BY P.NUM_FILA
/
 spool off

lleva incluido el commit cada 11 filas
el fichero generado lleva la opción de anular la salida por pantalla
otras opciones de spool por explorar

SET HEADING OFF
SET FEEDBACK OFF
SET SERVEROUTPUT ON SIZE 1000000
SET PAGESIZE 0
SET FEEDBACK OFF
SET LINESIZE 4096
SET VERIFY OFF
SET TERMOUT OFF  -- anula la salida por pantalla lo que reduce el tiempo de creación del fichero
SELECT 1 FROM DUAL;

EJEMPLO DE SPOOL CON COMMIT

spool c:\inicio\ficheroSpool.sql

SET HEADING OFF
SET FEEDBACK OFF
SET LINESIZE 1000
SET TERMOUT OFF  -- anula la salida por pantalla lo que reduce el tiempo de creación del fichero
PROMPT SET TERMOUT OFF
SELECT 'insert into EPACT_ACTO_ADJ_VAC_SOL
 (ACT_ID_CURSO, ACT_ID_ACTO, ACT_ID_VACANTE, ACT_ID_CUERPO, ACT_ID_ESPECIALIDAD,
 ACT_NIF, ACT_ORDEN, ACT_ID_ESTADO)
values ( 2017, 1,'|| V.N_VACANTE||','''|| V.C_CUERPO||''','''|| V.C_ESPEC||''' , ''' ||FB_DNI_A_NIF(P.DNI)||''' , '||P.N_ORD_PREF ||', '||
'''PENDIENTE'' ); '||
    DECODE(MOD(ROWNUM,10),0,CHR(10)||CHR(13)||'COMMIT;',NULL)
  FROM PETICVAC_AE P, V_VACANTES_AE V
 WHERE P.F_CONV_AE = '27/07/2017'
   AND P.AÑO_INI_CUR = V.AÑO_INI_CUR
   AND P.N_VACANTE = V.N_VACANTE
  AND V.C_ESPEC = 'EF1'
  and rownum < 12 -- para pruebas
 ORDER BY P.N_ORD_PREF;

SELECT 'COMMIT;' FROM DUAL;`

spool off

AÑADIR A UN SPOOL

para añadir información a un spool y no borarlo se usa
- spool <nombre_fichero_spool> append

PARA CONVERTIR UN BLOB A CLOB

--
-- V_BLOBTOCLOB  (Function) 
--
CREATE OR REPLACE function       v_blobtoclob(v_blob_in in blob)
-- CONVIERTE UN BLOB A CLOB
--* la función `v_blobtoclob` la saco de la [web de Burleson](http://www.dba-oracle.com/t_dbms_lob_converttoclob.htm)
return clob is

v_file_clob clob;
v_file_size integer := dbms_lob.lobmaxsize;
v_dest_offset integer := 1;
v_src_offset integer := 1;
v_blob_csid number := dbms_lob.default_csid;
v_lang_context number := dbms_lob.default_lang_ctx;
v_warning integer;
v_length number;

begin

dbms_lob.createtemporary(v_file_clob, true);

dbms_lob.converttoclob(v_file_clob,
v_blob_in,
v_file_size,
v_dest_offset,
v_src_offset,
v_blob_csid,
v_lang_context,
v_warning);

return v_file_clob;

exception
when others then
dbms_output.put_line('Error found');

end;
/

FILAS AFECTADAS

para saber el número de filas afectadas por una sentencia se usa
- SQL%ROWCOUNT

CREATE TABLE employees_temp AS SELECT * FROM employees;
BEGIN
  UPDATE employees_temp SET salary = salary * 1.05 WHERE salary < 5000;
  DBMS_OUTPUT.PUT_LINE('Updated ' || SQL%ROWCOUNT || ' salaries.');
END;

tiene que estar justo después de la sentencia.

NUMERO DE FILAS DE UNA CONSULTA

SELECT NOM.* , COUNT(*) OVER() FROM EPNOM_NOMINA NOM ;

FECHA DE CREACIÓN DEL REGISTRO Y DE MODIFICACIÓN

CREATE TABLE FOO (
    FOO_ID NUMBER(*,0) NOT NULL,
    -- ... y resto de campos

    FECHA_INS DATE DEFAULT SYSDATE NOT NULL,
    FECHA_MOD DATE DEFAULT SYSDATE NOT NULL,

    CONSTRAINT FOO_PK PRIMARY KEY (FOO_ID)
);

COMMENT ON COLUMN FOO.FECHA_INS IS 'Fecha de inserción';
COMMENT ON COLUMN FOO.FECHA_MOD IS 'Fecha de modificación';


/*
 * Mantener actualizada la fecha de modificación
 */
CREATE OR REPLACE TRIGGER FOO_TRG1
    BEFORE UPDATE
    ON FOO
    REFERENCING NEW AS NEW OLD AS OLD
    FOR EACH ROW
BEGIN
    :NEW.FECHA_MOD := SYSDATE;
END FOO_TRG1;
/
ALTER TRIGGER FOO_TRG1 ENABLE;

TUNNING

interesante post sobre tunning de consultas
Cuando se hace una consulta multi-tabla con //joins//, el orden en que se ponen las tablas en el FROM influye en el plan de ejecución. Aquellas tablas que retornan más filas deben ir en las primeras posiciones, mientras que las tablas con pocas filas deben situarse al final de la lista de tablas.
Colocar la tabla que devuelve menor número de registros en el último lugar del FROM.
Utilizar siempre que sea posible las mismas consultas. La segunda vez que se ejecuta una consulta, se ahorrará mucho tiempo de parsing y optimización, así que se debe intentar utilizar las mismas consultas repetidas veces.
otro post interesante sobre el tema

FORMATO AÑOS

para ver de usar to_char(‘RRRR’) por defecto o usar to_char(‘YYYY’)
YYYY da el año actual, en formato de 4 digitos.
RRRR significa que los años de 2 dígitos en el rango de 00 a 49 se suponen en el siglo actual (es decir, tienen los mismos dos primeros dígitos que el año actual) y los años dados de 50 a 99 en el siglo anterior.

SELECT TO_CHAR(SYSDATE) FROM DUAL;
SELECT TO_CHAR(SYSDATE,'DD/MM/RRRR') FROM DUAL;
SELECT TO_CHAR(SYSDATE,'DD/MM/YYYY') FROM DUAL;

SELECT TO_CHAR(TO_DATE('01/01/12'),'DD/MM/RRRR') FROM DUAL;
SELECT TO_CHAR(TO_DATE('01/01/12'),'DD/MM/YYYY') FROM DUAL;

SELECT TO_CHAR(TO_DATE('01/01/99'),'DD/MM/RRRR') FROM DUAL;
SELECT TO_CHAR(TO_DATE('01/01/99'),'DD/MM/YYYY') FROM DUAL;

SELECT TO_DATE('01/01/12','DD/MM/RRRR') FROM DUAL;
SELECT TO_DATE('01/01/12','DD/MM/YYYY') FROM DUAL;

SELECT TO_DATE('01/01/99','DD/MM/RRRR') FROM DUAL;
SELECT TO_DATE('01/01/99','DD/MM/YYYY') FROM DUAL;

para to_char no es relevante
para to_date con fechas que sólo tengan dos dígitos para el año si que es interesante y necesario para evitar tener años con el formato 0012 por ejemplo

FIDDLE

para probar sentencias sql online
sqlfiddle
sqlfiddle ejemplo con oracle
- muestra el plan de ejecucción
- se puede generar un link y compartir
- ejemplo propio 😄
  - el usuario USER_4_C6E41 forma parte del link generado

TRIGGER AUTOINCREMENTAL

cuidado con el trigger para aumentar el contador cuando empezamos con la tabla vacía

SELECT '1 MAX(NUM)'  SENTENCIA , MAX(NUM)  FROM AUD_APL UNION
SELECT '2 NVL(MAX(NUM),0)' , NVL(MAX(NUM),0)  FROM AUD_APL UNION
SELECT '3 NVL(MAX(NUM),0) + 1' ,NVL(MAX(NUM),0) + 1  FROM AUD_APL  UNION
SELECT '4 NVL(NUM,0)'  SENTENCIA , NVL(NUM,0)  FROM AUD_APL UNION
SELECT '5 MAX(NVL(NUM,0))' , MAX(NVL(NUM,0))  FROM AUD_APL UNION
SELECT '6 MAX(NVL(NUM,0)) + 1' , MAX(NVL(NUM,0)) + 1  FROM AUD_APL

los ref cursores

-- DESDE SQL PLUS
SQL> conn gab/desarrollo@xe
Connected.
SQL> SHOW USER
USER is "GAB"
SQL> VAR RC REFCURSOR;
SQL> exec :rc := gab.gab_pck_alumno.f_buscar_alumnos (null,null,1);

PL/SQL procedure successfully completed.

SQL> print rc

GAB_ALUMNO
--------------------------------------------------
GAB_DESCRIPCION
--------------------------------------------------------------------------------
ALUMNO1
DESCRIPCION DEL ALUMNO 1

ALUMNO2
DESCRIPCION DEL ALUMNO 2

ALUMNO3
DESCRIPCION DEL ALUMNO 3

SQL> print rc
SP2-0625: Error printing variable "rc"
SQL>

el ref cursor no contiene los datos resultado de la consulta sino que contiene un puntero a la consulta y por eso el segundo print da error
el primer print funciona porque SQL Plus mira el cursor de referencia y ve que es un cursor abierto pues la sentencia no lo cierra.
Entra en el bucle, obteniendo cada fila de datos de la base de datos, utilizando el cursor de referencia como referencia (puntero) a la consulta relevante, y mostrando cada fila de datos hasta que no tenía más filas para recuperar.
Una vez que se han obtenido todas las filas, cierra el cursor.

SQL> ed
Wrote file afiedt.buf

 declare
     c_alumnos    sys_refcursor;
   begin
     c_alumnos := gab.gab_pck_alumno.f_buscar_alumnos (null,null,1); -- This returns an open cursor
     dbms_output.put_line('Alumnos: '||c_alumnos%ROWCOUNT);
     close c_alumnos;
  end;
SQL> /
Alumnos: 0

PL/SQL procedure successfully completed.

sale 0 a pesar de que existen 3 alumnos por que c_alumnos%ROWCOONT muestra el número de fila leídas a través del cursos y no hemos leído ninguna.

Wrote file afiedt.buf

 declare
     c_alumnos    sys_refcursor;
     v_id_alumno  varchar2(50);
     v_descripcion varchar2(200);
   begin
     c_alumnos := gab.gab_pck_alumno.f_buscar_alumnos (null,null,1);  -- This returns an open cursor
     dbms_output.put_line('Pre Fetch: Alumnos: '||c_alumnos%ROWCOUNT);
     fetch c_alumnos into v_id_alumno, v_descripcion;
     dbms_output.put_line('Post Fetch: Rows: '||c_alumnos%ROWCOUNT);
     close c_alumnos;
  end;
SQL> /
Pre Fetch: Alumnos: 0
Post Fetch: Rows: 1

PL/SQL procedure successfully completed.

ahora sí que ha leído una fila del cursor.

SQL> ed
Wrote file afiedt.buf

declare
    c_alumnos    sys_refcursor;
    v_id_alumno  varchar2(50);
    v_descripcion varchar2(200);
  begin
    c_alumnos := gab.gab_pck_alumno.f_buscar_alumnos (null,null,1);  -- This returns an open cursor
    dbms_output.put_line('Pre Fetch: Alumnos: '||c_alumnos%ROWCOUNT);
    loop
      fetch c_alumnos into v_id_alumno, v_descripcion;
      exit when c_alumnos%NOTFOUND;
      dbms_output.put_line('Post Fetch: Rows: '||c_alumnos%ROWCOUNT||' '||v_id_alumno||' '||v_descripcion);
    end loop;
    close c_alumnos;
 end;
SQL> /
Pre Fetch: Alumnos: 0
Post Fetch: Rows: 1 ALUMNO1 DESCRIPCION DEL ALUMNO 1
Post Fetch: Rows: 2 ALUMNO2 DESCRIPCION DEL ALUMNO 2
Post Fetch: Rows: 3 ALUMNO3 DESCRIPCION DEL ALUMNO 3

PL/SQL procedure successfully completed.

de esta forma se lee el contenido del cursor
- funciona tanto en SQL como desde el TOAD|
  
  SENTENCIA MAX(NUM)

los ref cursores

-- DESDE SQL PLUS
SQL> conn gab/desarrollo@xe
Connected.
SQL> SHOW USER
USER is "GAB"
SQL> VAR RC REFCURSOR;
SQL> exec :rc := gab.gab_pck_alumno.f_buscar_alumnos (null,null,1);

PL/SQL procedure successfully completed.

SQL> print rc

GAB_ALUMNO
--------------------------------------------------
GAB_DESCRIPCION
--------------------------------------------------------------------------------
ALUMNO1
DESCRIPCION DEL ALUMNO 1

ALUMNO2
DESCRIPCION DEL ALUMNO 2

ALUMNO3
DESCRIPCION DEL ALUMNO 3

SQL> print rc
SP2-0625: Error printing variable "rc"
SQL>

el ref cursor no contiene los datos resultado de la consulta sino que contiene un puntero a la consulta y por eso el segundo print da error
el primer print funciona porque SQL Plus mira el cursor de referencia y ve que es un cursor abierto pues la sentencia no lo cierra.
Entra en el bucle, obteniendo cada fila de datos de la base de datos, utilizando el cursor de referencia como referencia (puntero) a la consulta relevante, y mostrando cada fila de datos hasta que no tenía más filas para recuperar.
Una vez que se han obtenido todas las filas, cierra el cursor.

SQL> ed
Wrote file afiedt.buf

 declare
     c_alumnos    sys_refcursor;
   begin
     c_alumnos := gab.gab_pck_alumno.f_buscar_alumnos (null,null,1); -- This returns an open cursor
     dbms_output.put_line('Alumnos: '||c_alumnos%ROWCOUNT);
     close c_alumnos;
  end;
SQL> /
Alumnos: 0

PL/SQL procedure successfully completed.

sale 0 a pesar de que existen 3 alumnos por que c_alumnos%ROWCOONT muestra el número de fila leídas a través del cursos y no hemos leído ninguna.

Wrote file afiedt.buf

 declare
     c_alumnos    sys_refcursor;
     v_id_alumno  varchar2(50);
     v_descripcion varchar2(200);
   begin
     c_alumnos := gab.gab_pck_alumno.f_buscar_alumnos (null,null,1);  -- This returns an open cursor
     dbms_output.put_line('Pre Fetch: Alumnos: '||c_alumnos%ROWCOUNT);
     fetch c_alumnos into v_id_alumno, v_descripcion;
     dbms_output.put_line('Post Fetch: Rows: '||c_alumnos%ROWCOUNT);
     close c_alumnos;
  end;
SQL> /
Pre Fetch: Alumnos: 0
Post Fetch: Rows: 1

PL/SQL procedure successfully completed.

ahora sí que ha leído una fila del cursor.

SQL> ed
Wrote file afiedt.buf

declare
    c_alumnos    sys_refcursor;
    v_id_alumno  varchar2(50);
    v_descripcion varchar2(200);
  begin
    c_alumnos := gab.gab_pck_alumno.f_buscar_alumnos (null,null,1);  -- This returns an open cursor
    dbms_output.put_line('Pre Fetch: Alumnos: '||c_alumnos%ROWCOUNT);
    loop
      fetch c_alumnos into v_id_alumno, v_descripcion;
      exit when c_alumnos%NOTFOUND;
      dbms_output.put_line('Post Fetch: Rows: '||c_alumnos%ROWCOUNT||' '||v_id_alumno||' '||v_descripcion);
    end loop;
    close c_alumnos;
 end;
SQL> /
Pre Fetch: Alumnos: 0
Post Fetch: Rows: 1 ALUMNO1 DESCRIPCION DEL ALUMNO 1
Post Fetch: Rows: 2 ALUMNO2 DESCRIPCION DEL ALUMNO 2
Post Fetch: Rows: 3 ALUMNO3 DESCRIPCION DEL ALUMNO 3

PL/SQL procedure successfully completed.

de esta forma se lee el contenido del cursor
- funciona tanto en SQL como desde el `TOAD

1 MAX(NUM)	null
2 NVL(MAX(NUM),0)	0
3 NVL(MAX(NUM),0) + 1	1
5 MAX(NVL(NUM,0))
6 MAX(NVL(NUM,0)) + 1

BLOGS

POR EXPLORAR

sys.dbms_utility.format_error_backtrace
interesante entrada sobre el tema

SQLPLUS

la referencia de oracle

`OWA_UTIL.CALENDARPRINT`

leo que genera un calendario en HTML
uso del paquete owa_util.calendarprint pero me da un error

-- como GAB
SQL> exec OWA_UTIL.CALENDARPRINT(P_QUERY => 'SELECT sysdate ,gab_alumno, null FROM gab_alumno where gab_alumno = ''ALUMNO1'' order by sysdate', P_MF_ONLY =>'Y');
BEGIN OWA_UTIL.CALENDARPRINT(P_QUERY => 'SELECT sysdate ,gab_alumno, null FROM gab_alumno where gab_alumno = ''ALUMNO1'' order by sysdate', P_MF_ONLY =>'Y'); END;

*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "SYS.OWA_UTIL", line 2356
ORA-06512: at line 1

SQL> exec OWA_UTIL.CALENDARPRINT(P_QUERY => 'SELECT sysdate ,gab_alumno, null FROM gab_alumno order by gab_alumno', P_MF_ONLY =>'Y');

DESACTIVAR CONSTRAINTS

sacado de este gist

-- C - Check constraint on a table  
-- P - Primary key  
-- U - Unique key  
-- R - Referential integrity  
-- V - With check option, on a view  
-- O - With read only, on a view  
-- H - Hash expression  
-- F - Constraint that involves a REF column  
-- S - Supplemental logging
SET SERVEROUTPUT ON
exec dbms_output.enable(1000000);
DECLARE
    v_typ          VARCHAR2(32);
    v_name         VARCHAR2(32);
    v_constraint   VARCHAR2(32);
    v_sql          VARCHAR2(100);

   
      CURSOR c_constraints IS        
        SELECT table_name, constraint_name        
        FROM   user_constraints        
        WHERE  constraint_type = 'R'
        ;
BEGIN

OPEN c_constraints;

    LOOP
	    BEGIN
			FETCH c_constraints
			INTO  v_name, v_constraint;

			EXIT WHEN c_constraints%NOTFOUND;
			v_sql := 'alter table ' || v_name || ' DISABLE constraint ' || v_constraint;
			DBMS_OUTPUT.put_line(v_sql);

			EXECUTE IMMEDIATE v_sql;
		EXCEPTION
			WHEN OTHERS THEN  
			DBMS_OUTPUT.put_line('Error in exec ' || v_sql);
		END;
    END LOOP;

    CLOSE c_constraints;


    v_sql := 'PURGE RECYCLEBIN';
    DBMS_OUTPUT.put_line(v_sql);

    EXECUTE IMMEDIATE v_sql;
    
END;
/

EXIT;

OBJETOS INVÁLIDOS

para todos los usuarios comentando la línea del OWNER

--SET SERVEROUTPUT ON SIZE 1000000
BEGIN
  FOR cur_rec IN (SELECT owner,
                         object_name,
                         object_type,
                         DECODE(object_type, 'PACKAGE', 1,
                                             'PACKAGE BODY', 2, 2) AS recompile_order
                  FROM   dba_objects
                  WHERE  object_type IN ('PACKAGE', 'PACKAGE BODY')
                  AND    status != 'VALID'
                  AND OWNER = USER
                  ORDER BY 4)
  LOOP
 BEGIN
      IF cur_rec.object_type = 'PACKAGE' THEN
        EXECUTE IMMEDIATE 'ALTER ' || cur_rec.object_type || 
            ' "' || cur_rec.owner || '"."' || cur_rec.object_name || '" COMPILE';
      ElSE
        EXECUTE IMMEDIATE 'ALTER PACKAGE "' || cur_rec.owner || 
            '"."' || cur_rec.object_name || '" COMPILE BODY';
      END IF;
    EXCEPTION
      WHEN OTHERS THEN
        DBMS_OUTPUT.put_line(cur_rec.object_type || ' : ' || cur_rec.owner || 
                             ' : ' || cur_rec.object_name);
    END;
  END LOOP;
END;

SINÓNIMOS INVÁLIDOS

de este blog

rem 
rem  Exludes SYS and SYSTEM users
rem
select 'drop '||decode (s.owner,'PUBLIC','PUBLIC SYNONYM ',
'SYNONYM'||s.owner||'.')||s.synonym_name||';'
from dba_synonyms  s
where table_owner not in('SYSTEM','SYS')
and db_link is null
and not exists
     (select  1
      from dba_objects o
      where s.table_owner=o.owner
      and s.table_name=o.object_name)

PL-SQL DEVELOPER

para ejecutar la sentencia en la que esta el cursor
- Tools-preferences-SQL-window

pl_sql_developer_seleccionar_consultas_automaticamente

CERRAR `REPORT BACKGROUND ENGINE`

Add_Parameter(pl_id,'ORACLE_SHUTDOWN',TEXT_PARAMETER,'Yes');

MEDIR TIEMPO DE EJECUCIÓN DE UN BLOQUE

DECLARE
    n_start NUMBER;
    n_end   NUMBER;
    n_total NUMBER;
    RetVal       EPACF.EPACF_PCK_ACTO.cursorActos;
BEGIN
    n_start := DBMS_UTILITY.GET_TIME();
     RetVal := EPACF.EPACF_PCK_ACTO.F_OBTENER_ACTO ( 2021,1);
    n_end := DBMS_UTILITY.GET_TIME();
    n_total := n_end - n_start;
    DBMS_OUTPUT.put_line('Total: '||n_total);
END;

LISTAGG - UNIR VARIOS RESULTADO EN UNA LISTA SEPARADA POR DELIMITADOR

una función que concatena varios resultado en una lista
muy util cuando una consulta tiene una subconsulta que devuelve más de una fila

SELECT LISTAGG(last_name, '; ')
         WITHIN GROUP (ORDER BY hire_date, last_name) "Emp_list",
       MIN(hire_date) "Earliest"
  FROM employees
  WHERE department_id = 30;

otro ejemplo propio

SELECT LISTAGG(DTM_DESCRIPCION, '; ') WITHIN GROUP (ORDER BY DTM_DESCRIPCION) OVER
FROM 
EPREM_MERITO MER, EPREM_MERITO_FOR_ACADEMICA MFA , EPDTM_TITULACION ET
WHERE MER.REM_ID_MERITO = MFA.REM_ID_MERITO
AND MER.REM_NIF = MFA.REM_NIF
AND MFA.REM_ID_TITULACION = ET.DTM_ID 
AND REM_ID_ESTADO = 'VALIDO'
AND DTM_ID_IDIOMA_NIVEL = 'C2'
AND MER.REM_NIF = INTE.LST_NIF

COMANDOS

`TERMOUT`

SET TERMOUT OFF
- anula la salida del comando por el terminal lo que reduce el tiempo de ejecución
usarlo en el script que se lanza y en el generado si es de carga

SQLPLUS

intentar usarlo para los permisos de geb en varias aplicaciones generando un sql de este tipo

conn epcnt/desarrollo@deseduca
select sysdate from dual
/
commit
/

INTERESANTES

SUDOKU

un programa en pl sql para resolver sudokus
lo resuelve solo creando types

pl-sql /pl /sql /sqlplus

PROPÓSITO

OBJETIVOS

COLUMNAS DE TABLA EN CHAR

PLANTILLA DE PAQUETE

PAQUETE DE PROPÓSITO GENERAL

REF CURSOR

FORMA SENCILLA

SPOOL

EJEMPLO DE SPOOL CON COMMIT

AÑADIR A UN SPOOL

PARA CONVERTIR UN BLOB A CLOB

FILAS AFECTADAS

NUMERO DE FILAS DE UNA CONSULTA

FECHA DE CREACIÓN DEL REGISTRO Y DE MODIFICACIÓN

TUNNING

FORMATO AÑOS

FIDDLE

TRIGGER AUTOINCREMENTAL

BLOGS

POR EXPLORAR

SQLPLUS

OWA_UTIL.CALENDARPRINT

DESACTIVAR CONSTRAINTS

OBJETOS INVÁLIDOS

SINÓNIMOS INVÁLIDOS

PL-SQL DEVELOPER

CERRAR REPORT BACKGROUND ENGINE

MEDIR TIEMPO DE EJECUCIÓN DE UN BLOQUE

LISTAGG - UNIR VARIOS RESULTADO EN UNA LISTA SEPARADA POR DELIMITADOR

COMANDOS

TERMOUT

SQLPLUS

INTERESANTES

SUDOKU

pl-sql
/pl /sql /sqlplus

`REF CURSOR`

`OWA_UTIL.CALENDARPRINT`

CERRAR `REPORT BACKGROUND ENGINE`

`TERMOUT`