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 \u201cfine grained\u201d 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\u2019t use that specific column.<\/p>\n
See below for an example\u2026<\/p>\n
Create a simple table\u2026<\/p>\n
\r\nSQL> create table t1005(c number, d varchar2(10));\r\n\r\nTable created.\r\n<\/pre>\n\u2026as well as a procedure that depends on the table, with an \u201c*\u201d to indicate all columns are referenced…<\/p>\n
\r\nSQL> create or replace procedure p1005 is\r\n 2 begin\r\n 3 for cur in (select * from t1005) loop\r\n 4 dbms_output.put_line(cur.c);\r\n 5 end loop;\r\n 6 end;\r\n 7 \/\r\n\r\nProcedure created.\r\n<\/pre>\n\u2026and then load the table with some rows\u2026<\/p>\n
\r\nSQL> begin\r\n 2 for i in 1..10 loop\r\n 3 insert into t1005(c,d) values(i,rpad('x',10,'x'));\r\n 4 end loop;\r\n 5 end;\r\n 6 \/\r\n\r\nPL\/SQL procedure successfully completed.\r\n\r\nSQL> commit;\r\n\r\nCommit complete.\r\n\r\nSQL> select * from t1005;\r\n\r\n C D\r\n---------- ----------\r\n 1 xxxxxxxxxx\r\n 2 xxxxxxxxxx\r\n 3 xxxxxxxxxx\r\n 4 xxxxxxxxxx\r\n 5 xxxxxxxxxx\r\n 6 xxxxxxxxxx\r\n 7 xxxxxxxxxx\r\n 8 xxxxxxxxxx\r\n 9 xxxxxxxxxx\r\n 10 xxxxxxxxxx\r\n\r\n10 rows selected.\r\n<\/pre>\nWe first see our procedure is currently in a VALID state, as we would expect it to be.<\/p>\n
\r\nSQL> select status from user_objects where object_name = 'P1005';\r\n\r\nSTATUS\r\n-------\r\nVALID\r\n<\/pre>\nHowever, as soon as we modify the column D, which is not used in our procedure, we invalidate the procedure.<\/p>\n
\r\nSQL> alter table t1005 modify d varchar2(20);\r\n\r\nTable altered.\r\n\r\nSQL> select status from user_objects where object_name = 'P1005';\r\n\r\nSTATUS\r\n-------\r\nINVALID\r\n<\/pre>\nBut if we recreate the procedure without the \u201c*\u201d, and mention by name only the column we use\u2026<\/p>\n
\r\nSQL> create or replace procedure p1005 is\r\n 2 begin\r\n 3 for cur in (select c from t1005) loop\r\n 4 dbms_output.put_line(cur.c);\r\n 5 end loop;\r\n 6 end;\r\n 7 \/\r\n\r\nProcedure created.\r\n\r\nSQL> select status from user_objects where object_name = 'P1005';\r\n\r\nSTATUS\r\n-------\r\nVALID\r\n<\/pre>\n\u2026we can change the \u201cunused\u201d columns and not impact the state of the procedure.<\/p>\n
\r\nSQL> alter table t1005 modify d varchar2(30);\r\n\r\nTable altered.\r\n\r\nSQL> select status from user_objects where object_name = 'P1005';\r\n\r\nSTATUS\r\n-------\r\nVALID\r\n\r\nSQL>\r\n<\/pre>\nYou can identify PL\/SQL objects as having at least one \u201cselect *\u201d in them by running what is below…<\/p>\n
\r\ndeclare\r\n l_txt clob := empty_clob();\r\nbegin\r\n for stored_proc in (select distinct name,type from dba_source where owner = 'XWC4_32SFT') loop\r\n 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\r\n l_txt := l_txt || replace(txt.text,chr(10),'');\r\n l_txt := replace(l_txt,chr(13),'');\r\n l_txt := replace(l_txt,' ','');\r\n l_txt := lower(l_txt);\r\n end loop;\r\n if instr(l_txt,'select*') > 0 then\r\n begin\r\n dbms_output.put_line(stored_proc.name || ' ' || stored_proc.type);\r\n exception\r\n when others then\r\n dbms_output.put_line(stored_proc.name || ' ' || stored_proc.type || ' ' || sqlerrm);\r\n end;\r\n end if;\r\n l_txt := '';\r\n end loop;\r\nend;\r\n\/\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"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 \u201cfine grained\u201d dependency checking.…<\/p>\n