Ejemplos Sencillos de PL/SQL: El Bucle WHILE

Se puede repetir un comando PL/SQL en un bucle WHILE. Aquí está un ejemplo que probé en Oracle 11.2.0.2.7:

SQL> l

  1  DECLARE

  2   M NUMBER := 1;

  3  BEGIN

  4   WHILE M < 6 LOOP

  5    DBMS_OUTPUT.PUT_LINE('M = '||M);

  6    M := M + 1;

  7   END LOOP;

  8* END;

SQL> /

M = 1

M = 2

M = 3

M = 4

M = 5

 

Procedimiento PL/SQL terminado correctamente.

 

SQL>

ORA-01502

En este ejemplo, miro una razón por tener el error ORA-01502. Lo probé en Oracle 11.2.0.2.7. Creé una tabla en el tablespace SYSTEM por error: 

SQL> create table andrews_table

  2  tablespace system

  3  as select * from dba_tables

  4  /

 

Tabla creada.

 

SQL>

Creé un índice en la tabla y verifiqué que era válido:

SQL> create index andrews_index

  2  on andrews_table(table_name)

  3  /

 

Indice creado.

 

SQL> select status from user_indexes

  2  where index_name = 'ANDREWS_INDEX'

  3  /

 

STATUS

--------

VALID

 

SQL>

Le dije a Oracle de informarme sobre índices inutilizables:

SQL> alter session

  2  set skip_unusable_indexes = false

  3  /

 

Sesion modificada.

 

SQL>

Ejecuté una consulta para emplear el índice sin problemas: 

SQL> select count(*) from andrews_table

  2  where table_name = 'BLAH'

  3  /

 

  COUNT(*)

----------

         0

 

SQL>

Luego me di cuenta que la tabla estaba en el tablespace SYSTEM y la puse en el tablespace USERS, lo que invalidó el índice:

SQL> alter table andrews_table

  2  move tablespace users

  3  /

 

Tabla modificada.

SQL> select status from user_indexes

  2  where index_name = 'ANDREWS_INDEX'

  3  /

 

STATUS

--------

UNUSABLE

 

SQL>

Así cuando intenté a emplear el índice, tenía un error: 

SQL> select count(*) from andrews_table

  2  where table_name = 'BLAH'

  3  /

select count(*) from andrews_table

*

ERROR en linea 1:

ORA-01502: el indice 'OPS$ORACLE.ANDREWS_INDEX' o una

particion del mismo estan en estado inutilizable

 

SQL>

Después de reconstruir el índice, la consulta funcionó otra vez: 

SQL> alter index andrews_index rebuild

  2  /

 

Indice modificado.

 

SQL> select status from user_indexes

  2  where index_name = 'ANDREWS_INDEX'

  3  /

 

STATUS

--------

VALID

 

SQL> select count(*) from andrews_table

  2  where table_name = 'BLAH'

  3  /

 

  COUNT(*)

----------

         0

 

SQL>

En inglés / in English 

DROP TABLE ... PURGE

Si tienes una base de datos con papelera de reciclaje, y borras una tabla que no esté en el tablespace SYSTEM, esta tabla permanece en la papelera de reciclaje. Entonces se puede restaurar la tabla de allí.

Si esto no te gusta y quieres borrar una tabla completamente, se puede hacerlo con DROP TABLE ... PURGE. Aquí está un ejemplo que probé en Oracle 11.2.0.2.7:

Antes de empezar, verifiqué que mi base de datos empleaba la papelera de reclicaje:

SQL> select value from v$parameter

  2  where name = 'recyclebin'

  3  /

 

VALUE

----------

on

 

SQL>

Creé una tabla en el tablespace USERS: 

SQL> create table tab1

  2  (col1 number)

  3  tablespace users

  4  /

 

Tabla creada.

 

SQL>

Borré la tabla y verifiqué que estaba en la papelera de reciclaje después: 

SQL> drop table tab1

  2  /

 

Tabla borrada.

 

SQL> select original_name from recyclebin

  2  /

 

ORIGINAL_NAME

--------------------------------

TAB1

SQL>

Restauré la tabla: 

SQL> flashback table tab1 to before drop

  2  /

 

Flashback terminado.

 

SQL> desc tab1

Nombre                     ?Nulo?   Tipo

-------------------------- -------- ------------------

COL1                                NUMBER

 

SQL>

Borré la tabla por segunda vez con el comando DROP TABLE ... PURGE: 

SQL> drop table tab1 purge

  2  /

 

Tabla borrada.

 

SQL>

Esta vez la tabla no estaba en la papelera de reciclaje:

SQL> select original_name from recyclebin

  2  /

 

ninguna fila seleccionada

 

SQL>

Así cuando intenté a restaurar la tabla, tenía el error siguiente: 

SQL> flashback table tab1 to before drop

  2  /

flashback table tab1 to before drop

*

ERROR en linea 1:

ORA-38305: el objeto no esta en la papelera de

reciclaje

 

SQL>

Ejemplos Sencillos de PL/SQL: EXECUTE IMMEDIATE y INTO

Probé este ejemplo en Oracle 11.2.0.2.7. Se puede poner un comando PL/SQL en una variable y luego ejecutarlo con EXECUTE IMMEDIATE. Si quieres tener el resultado en otra variable, se puede hacerlo con la palabra INTO de esta manera:

SQL> set serveroutput on

SQL> declare

  2   row_count number;

  3   sql_statement varchar2(200);

  4  begin

  5   sql_statement := 'select count(*) from dba_tables';

  6   dbms_output.put_line(sql_statement);

  7   execute immediate sql_statement into row_count;

  8   dbms_output.put_line(row_count);

  9  end;

 10  /

select count(*) from dba_tables

2760

 

Procedimiento PL/SQL terminado correctamente.

 

SQL>

Ejemplos Sencillos de PL/SQL: División por Cero

Probé este ejemplo en Oracle 11.2.0.2.7. No está permitido dividir por cero en PL/SQL y, si intentas a hacerlo, Oracle te da un error ORA-01476: 

SQL> DECLARE

  2   COCIENTE NUMBER;

  3  BEGIN

  4   COCIENTE := 1 / 2;

  5   DBMS_OUTPUT.PUT_LINE('Primer Cociente = '||COCIENTE);

  6   COCIENTE := 1 / 0;

  7   DBMS_OUTPUT.PUT_LINE('Segundo Cociente = '||COCIENTE);

  8  END;

  9  /

Primer Cociente = .5

DECLARE

*

ERROR en linea 1:

ORA-01476: el divisor es igual a cero

ORA-06512: en linea 6

SQL>

Se puede atrapar este error con la excepción ZERO_DIVIDE de la manera siguiente:

SQL> DECLARE

  2   COCIENTE NUMBER;

  3  BEGIN

  4   COCIENTE := 1 / 2;

  5   DBMS_OUTPUT.PUT_LINE('Primer Cociente = '||COCIENTE);

  6   COCIENTE := 1 / 0;

  7   DBMS_OUTPUT.PUT_LINE('Segundo Cociente = '||COCIENTE);

  8  EXCEPTION

  9   WHEN ZERO_DIVIDE THEN

 10   DBMS_OUTPUT.PUT_LINE('No se puede dividir por cero');

 11  END;

 12  /

Primer Cociente = .5

No se puede dividir por cero

 

Procedimiento PL/SQL terminado correctamente.

 

SQL>

Ejemplos Sencillos de PL/SQL: Variables Constantes

Probé este ejemplo en Oracle 11.2.0.2.7.

De vez en cuando es necesario crear una variable constante que no puede ser cambiada, por ejemplo el número pi, que tiene siempre un valor de 3.14 (o 3.14159 si quieres ser más exacto).

Se puede hacerlo en PL/SQL con la palabra reservada constant. Si intentas a cambiar una variable constante, Oracle te da un error. Aquí está un ejemplo:

SQL> set serveroutput on

SQL> declare

  2  mi_nombre constant varchar2(6) := 'Andrew';

  3  begin

  4  dbms_output.put_line('Me llamo '||mi_nombre);

  5  end;

  6  /

Me llamo Andrew

 

Procedimiento PL/SQL terminado correctamente.

                                                              

SQL> declare

  2  mi_nombre constant varchar2(6) := 'Andrew';

  3  begin

  4  mi_nombre := 'Fred';

  5  end;

  6  /

mi_nombre := 'Fred';

*

ERROR en linea 4:

ORA-06550: linea 4, columna 1:

PLS-00363: la expresion 'MI_NOMBRE' no se puede

utilizar como destino de asignacion

ORA-06550: linea 4, columna 1:

PL/SQL: Statement ignored

 

SQL>

Ejemplos Sencillos de SQL: El Comando WITH

Si quieres probar una consulta SQL pero no quieres crear una tabla antes, se puede hacerlo con el comando WITH. Aquí están dos ejemplos que hice funcionar en Oracle 11: 

SQL> with tab1 as

  2  (select * from dba_tables)

  3  select count(*) from tab1

  4  where owner = 'SYSTEM'

  5  /

 

  COUNT(*)

----------

       181

 

SQL> with ciudades as

  2  (select 'Madrid' as ciudad from dual

  3   union

  4   select 'Granada' from dual

  5   union

  6   select 'Barcelona' from dual)

  7  select * from ciudades

  8  order by ciudad

  9  /

 

CIUDAD

---------

Barcelona

Granada

Madrid

 

SQL>

ORA-14039

Probé este ejemplo en Oracle 11.2.0.2.7. Se puede crear una tabla con particiones de la manera siguiente:

SQL> create table andrews_table

  2  partition by range (owner)

  3  (partition p1 values less than ('M'),

  4   partition p2 values less than (maxvalue))

  5  as select * from dba_tables

  6  where owner = 'SYS'

  7  /

 

Tabla creada.

 

SQL>

Se puede crear un índice local en esta tabla así:

 

SQL> create index andrews_index

  2  on andrews_table (table_name)

  3  local

  4  /

 

Indice creado.

 

SQL> drop index andrews_index

  2  /

 

Indice borrado.

 

SQL>

Pero si quieres crear un índice único, éste no puede ser local y Oracle te da un error que no ayuda mucho:

SQL> create unique index andrews_index

  2  on andrews_table (table_name)

  3  local

  4  /

on andrews_table (table_name)

   *

ERROR en linea 2:

ORA-14039: las columnas de particion deben formar un

subjuego de columnas clave de un indice UNIQUE

 

SQL>

 

La respuesta es sencilla, el índice único tiene que ser global:

 

SQL> create unique index andrews_index

  2  on andrews_table (table_name)

  3  global

  4  /

 

Indice creado.

 

SQL>

El Fallo 445628

Probé este ejemplo en Oracle 11.2.0.2.7.

Si tienes una base de datos con dos usuarios que se llaman (por ejemplo) USUARIO1 y USUARIO2, y USUARIO1 tiene una tabla que se llama USUARIO2, USUARIO1 no puede emplear procedimientos que pertenecen a USUARIO2. Aquí está un ejemplo:

Empecé una sesión con el usuario SYSTEM: 

SQL> conn system/contrasena_secreta

Conectado.

SQL>

Luego empleé SYS.DBMS_LOCK.SLEEP sin problemas:

SQL> exec sys.dbms_lock.sleep(10);

 

Procedimiento PL/SQL terminado correctamente.

 

SQL>

Entonces creé una tabla llamada SYS:

SQL> create table sys(col1 number);

 

Tabla creada.

SQL>

... y cuando intenté a emplear SYS.DBMS_LOCK.SLEEP otra vez, tenía unos errores a causa del fallo 445628:

SQL> exec sys.dbms_lock.sleep(10);

BEGIN sys.dbms_lock.sleep(10); END;

 

          *

ERROR en linea 1:

ORA-06550: linea 1, columna 11:

PLS-00302: el componente 'DBMS_LOCK' se debe declarar

ORA-06550: linea 1, columna 7:

PL/SQL: Statement ignored

 

SQL>