Invalid procedure

Wonder what is in there. Self invalidating procedure. Executable although invalid.

SQL> drop table i purge;

Table dropped.

SQL> create table i(n number);

Table created.

SQL> create or replace procedure INVALID_EXECUTABLE wrapped
  2  a000000
  3  b2
  4  abcd
  5  abcd
  6  abcd
  7  abcd
  8  abcd
  9  abcd
 10  abcd
 11  abcd
 12  abcd
 13  abcd
 14  abcd
 15  abcd
 16  abcd
 17  abcd
 18  abcd
 19  7
 20  133 148
 21  /GKO5fy/wMAULSQ3EONeuteOeyYwgzLINa5qfHQCrcHqNAWjUjjq5aQl2wunqxfypGG6DTUC
 22  yHBZPTO1oTh2rTHCnRckEhNR70+2N0Mxn4pbg7fZJwVKX9dFOCicXmazqP6JcBIEQF1QCSdZ
 23  DK5WOcSpQXvAnj5qIawt3H+JSwVpk8gG4oVLFcJ/Du2MO8CJvj7IVzAKn1TGVhx7nATQ1Xoh
 24  FtjOUd1kLeAhNpt+DGEwkLz/U3ZKLO8ZUrzcIIYGLJsBqyicCNA73FdMJNCGw7WlVkNl7Vji
 25  GmA6ougclJqWHoi4fzY81Efcubz7O35pxg==
 26  /

Procedure created.

SQL> select * from i;

no rows selected

SQL> select status from user_objects where object_name='INVALID_EXECUTABLE';

STATUS
-------
VALID

SQL> exec INVALID_EXECUTABLE

PL/SQL procedure successfully completed.

SQL> select * from i;

         N
----------
         1

SQL> select status from user_objects where object_name='INVALID_EXECUTABLE';

STATUS
-------
INVALID

SQL> alter procedure INVALID_EXECUTABLE compile;

Procedure altered.

SQL> select status from user_objects where object_name='INVALID_EXECUTABLE';

STATUS
-------
VALID

SQL> exec INVALID_EXECUTABLE

PL/SQL procedure successfully completed.

SQL> select * from i;

         N
----------
         2

SQL> select status from user_objects where object_name='INVALID_EXECUTABLE';

STATUS
-------
INVALID

SQL> exec INVALID_EXECUTABLE

PL/SQL procedure successfully completed.

SQL> select * from i;

         N
----------
         3

SQL> select status from user_objects where object_name='INVALID_EXECUTABLE';

STATUS
-------
INVALID

PLSQL_WARNINGS

Struggling with pl/sql code having EXCEPTION WHEN OTHERS THEN NULL; lines.

alter session set plsql_warnings = 'ENABLE:6009';

and compile your code. You get a nice report of your buggy code. There are quite a few other warnings available since 10.1. Here is a sqlplus script to be run on your development/test environment to get a information about your possible problematic code. The compilewarnings.sql script generates a p.txt file containing the output.

SQL> compilewarnings.sql

Check constraints and AND

Rob van Wijk has described nicely check constraints in inheritance conversion to a relation using single table implementation. The issue I planned to write about some day. Similar approach also to checks depending on state/status fields.

unindex 11.2

Tom Kyte has updated his unindex documentation. It is good to review your work every now and then. I guessed he had a valid version of unindex somewhere. Just did not find it at the moment.

Jonathan Lewis talking about the foreign key indexing issue in OTN. It might be that not all foreign keys need an index in your schema.

11.2 new function listagg is useful also in unindex. Here is a listagg version of unindex for the new Oracle version.

select case when i.index_name is not null
            then 'OK'
            else '****'
       end ok
     , c.table_name
     , c.constraint_name
     , c.cols
     , i.index_name
from (
  select a.table_name
       , a.constraint_name
       , listagg(b.column_name, ' ' ) 
          within group (order by column_name) cols
      from user_constraints a, user_cons_columns b
     where a.constraint_name = b.constraint_name
       and a.constraint_type = 'R'
  group by a.table_name, a.constraint_name
 ) c
 left outer join
 (
  select table_name
       , index_name
       , cr
       , listagg(column_name, ' ' ) 
          within group (order by column_name) cols
    from (
        select table_name
             , index_name
             , column_position
             , column_name
             , connect_by_root(column_name) cr
          from user_ind_columns
       connect by prior column_position-1 = column_position
              and prior index_name = index_name
         )
    group by table_name, index_name, cr
) i on c.cols = i.cols and c.table_name = i.table_name
;

Even thou unindex query is not the kind of query that is run several times a day, I measured execution times from different versions. The test schema contains 1700 foreign keys. Performance comparison

"col_cnt > ALL" 13 sec
"connect by"     3 sec 
"listagg"        1 sec

Foreign keys may point also to and from another schema. Here you can find a version using ALL_CONSTRAINTS and ALL_CONS_COLUMNS views.

TOra

Old news, but I just noticed, TOra project is alive. 2.0.0 version have been available for Windows almost a year already. I used the product before Quest purchased it from Underscore AB. Worth trying next to SQL Developer, Toad and/or other tools.