So after a few years of using them I have something of an opinion on them.
- Strongly typed declarations
- Inheritance - If the column data type changes, you don't have to change any of your packaged code (not really sure if that is different than #1)
- Difficult to debug - What data type was APPLICATION_DETAIL.FOOID or worse, what was SCHEMANAME.APPLICATION_DETAIL.FOOID? Is it a NUMBER, VARCHAR2, or something else? Off to SQL Developer or SQL*Plus to do a describe on the table...I once spent a full day trying to figure out which of the 30 passed in parameters (and their values) was throwing a data type error. Another developer finally found it.
- Too much typing - I love to type. Seriously. I'm going to say it...this is too much typing.
- It's Ugly - Alright, that's not really a con is it? I like my code pretty. Many times using the SCHEMANAME.APPLICATION_DETAIL.FOOID%TYPE takes me over 90 characters wide...it's just ugly.
In a development situation or trying to spec something out, I can definitely see the value. Nothing is set in stone and needs to be somewhat fluid. But in a stable production environment? Is it really necessary? If you are going to change the data type or precision of a column, wouldn't you expect to make a few changes? For some reason I think of the need to change your Primary Key, and all references...
%TYPE has it's merits. But I think the love affair is over for me.