Foreign Keys with missing Indexes

The following script will show any Foreign Keys that do not have a corresponding Index:

select fk.owner, fk.table_name, fk.constraint_name, fkc.column_name
from   all_constraints  fk
     , all_cons_columns fkc
where  fk.owner = :owner
and    fk.constraint_type = 'R'
and    not exists ( select null 
                    from   all_indexes ind 
                    where  ind.index_name = fk.constraint_name
                    and    ind.owner      = fk.owner )
and    fkc.owner           = fk.owner
and    fkc.table_name      = fk.table_name
and    fkc.constraint_name = fk.constraint_name
and    not exists ( select null
                    from   all_constraints  pk
                         , all_cons_columns pkc
                    where  pk.constraint_type  = 'P'
                    and    pk.owner            = fk.owner
                    and    pk.table_name       = fk.table_name
                    and    pkc.owner           = pk.owner
                    and    pkc.table_name      = pk.table_name
                    and    pkc.constraint_name = pk.constraint_name
                    and    fkc.column_name     = pkc.column_name )
order by owner, table_name, constraint_name;

CTAS (Create Table As Select)and NOT NULL Constraints

Stumbled across YAOF (Yet Another Oracle Feature) today; When performing a CTAS (Create Table As Select) NOT NULL Constraints are usually created, for example:

CREATE TABLE ctas_test
  ( id   NUMBER NOT NULL
  , name VARCHAR2(30) NULL );

CREATE TABLE ctas_test_copy AS
  SELECT * FROM ctas_test;
  
SELECT table_name, column_name, nullable 
FROM   all_tab_columns 
WHERE  table_name LIKE 'CTAS_TEST%' 
ORDER BY table_name, column_name;

As you can see the Not Null Constraints have been successfully replicated to the CTAS_TEST_COPY table. However if I now execute:

CREATE TABLE ctas_test_minus AS
  SELECT * FROM ctas_test
  MINUS
  SELECT * FROM ctas_test;
  
SELECT table_name, column_name, nullable 
FROM   all_tab_columns 
WHERE  table_name LIKE 'CTAS_TEST%' 
ORDER BY table_name, column_name;

The Not Null Constraints are missing from the CTAS_TEST_MINUS table.

Mimicing "break" in a PL/SQL FOR Loop

It seems a major omission in the Oracle PL/SQL Programming language that the BREAK keyword cannot be used in FOR loops (or WHILE loops) to skip to the next iteration.

Here is a (trivial) example of how you can implement "break" functionality using exceptions:

DECLARE
  e_skip_row EXCEPTION;
  l_total PLS_INTEGER := 0;
BEGIN
  --
  dbms_output.enable(10000);
  --
  FOR l_index IN 1..100 LOOP
  BEGIN
    --
    -- If the row is even then skip
    IF MOD(l_index,2) = 0 THEN
      RAISE e_skip_row;
    END IF;
    --
    l_total := l_total + l_index;
    --
  EXCEPTION
    WHEN e_skip_row THEN NULL;
  END;
  END LOOP;
  --
  dbms_output.put_line('Total is: '||l_total);
  --
END;