Fined grained dependency – Down with select *!!!

In 11G, I would like to suggest that you begin to move away from select * in your PL/SQL views and hardcoded procedure/function cursors. The reason I suggest this is that versions 11.1.0.6 and higher support “fine grained” dependency checking. What this means is that unless you use a column in a table in a dependent schema object, you can change the definition of the column at will without invalidating any database dictionary objects which depend on the table, but don’t use that specific column.

See below for an example…

Create a simple table…

SQL> create table t1005(c number, d varchar2(10));

Table created.

…as well as a procedure that depends on the table, with an “*” to indicate all columns are referenced…

SQL> create or replace procedure p1005 is
  2  begin
  3    for cur in (select * from t1005) loop
  4      dbms_output.put_line(cur.c);
  5    end loop;
  6  end;
  7  /

Procedure created.

…and then load the table with some rows…

SQL> begin
  2    for i in 1..10 loop
  3      insert into t1005(c,d) values(i,rpad('x',10,'x'));
  4    end loop;
  5  end;
  6  /

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL> select * from t1005;

         C D
---------- ----------
         1 xxxxxxxxxx
         2 xxxxxxxxxx
         3 xxxxxxxxxx
         4 xxxxxxxxxx
         5 xxxxxxxxxx
         6 xxxxxxxxxx
         7 xxxxxxxxxx
         8 xxxxxxxxxx
         9 xxxxxxxxxx
        10 xxxxxxxxxx

10 rows selected.

We first see our procedure is currently in a VALID state, as we would expect it to be.

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

STATUS
-------
VALID

However, as soon as we modify the column D, which is not used in our procedure, we invalidate the procedure.

SQL> alter table t1005 modify d varchar2(20);

Table altered.

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

STATUS
-------
INVALID

But if we recreate the procedure without the “*”, and mention by name only the column we use…

SQL> create or replace procedure p1005 is
  2  begin
  3    for cur in (select c from t1005) loop
  4      dbms_output.put_line(cur.c);
  5    end loop;
  6  end;
  7  /

Procedure created.

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

STATUS
-------
VALID

…we can change the “unused” columns and not impact the state of the procedure.

SQL> alter table t1005 modify d varchar2(30);

Table altered.

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

STATUS
-------
VALID

SQL>

You can identify PL/SQL objects as having at least one “select *” in them by running what is below…

declare
  l_txt clob := empty_clob();
begin
  for stored_proc in (select distinct name,type from dba_source where owner = 'XWC4_32SFT') loop
    for txt in (select text from dba_source where owner = 'XWC4_32SFT' and name = stored_proc.name and type = stored_proc.type order by line,type) loop
      l_txt := l_txt || replace(txt.text,chr(10),'');
      l_txt := replace(l_txt,chr(13),'');
      l_txt := replace(l_txt,' ','');
      l_txt := lower(l_txt);
    end loop;
    if instr(l_txt,'select*') > 0 then
      begin
        dbms_output.put_line(stored_proc.name || ' ' || stored_proc.type);
      exception
        when others then
          dbms_output.put_line(stored_proc.name || ' ' || stored_proc.type || ' ' || sqlerrm);
      end;
    end if;
    l_txt := '';
  end loop;
end;
/