I don't like it for parameters, as it is a bit misleading. A parameter is a datatype (eg VARCHAR2), not datatype+length (or precision/scale in the case of numbers), so change should be very infrequent. And you don't get misled into thinking that your input parameters will be magically constrained to the correct size.

What if you've got a variable that is gets its value from either column a or b ? Is it A%TYPE or B%TYPE. How about if it comes from a function, decode/case statement, two numbers added together ? Or it is used in a physical output file (eg to be printed on an envelope).

Your 'declare' ends up with a mix of explicit and derived (%TYPE) definitions. And you end up assigning one to another which negates half of the 'future-proofing'.

So even on the odd occasions you do change the length of a column, you're not going to do that without testing (which means identifying all the affected objects anyway). The actual physical code change you are 'saving' is insignificant.

I think the only advantage is that it does make you think about your variable sizes rather than going for NUMBER and some big VARCHAR2.
 

Using %TYPE can prevent annoying little mistakes like defining a variable as varchar(256) and then trying to insert it into a varchar(40) column.

If you know in advance that certain values have to go into specific columns, just get the definion from the column and know that at least one potential mistake was eliminated.
 

My rule is that %TYPE is for use in package bodies and not for use in package specs.

In my world 99% of the consumption of packages is done by .NET developers so the use of %TYPE in signatures would be massively counterproductive.

The absolutely perfect use for %TYPE is when declaring a variable in preperation for use in a SELECT ... INTO ... statement.
 

Here we use %TYPE as a form of inline documentation. If the parameter is anchored, the parameter is intended to represent a value appropriate for the column in the table. If it isn't anchored, the parameter may or may not correspond to any particular column.

So "a IN VARCHAR2" might be any particular string, but "a IN my_table.active_ind%TYPE" must be either 'Y' or 'N'.

Not perfect or necessarily practical for everyone but it works ok for us.
 

@gary

Your comment about using %ROWTYPE on my Virtual Columns article is what got me thinking about writing this!

@prod

Agreed. It is helpful in those situations.

@christaunity

I agree, definite PRO when it comes to the SELECT INTO...and probably the only time I'll use it going forward.

@jeffrey

I agree that is a good use of the %TYPE, but it doesn't contrain that value:

CREATE TABLE x
(
text VARCHAR2(1)
CONSTRAINT yorn CHECK ( text IN ( 'Y', 'N' ) )
);

CREATE OR REPLACE
PROCEDURE foo( p_text IN X.TEXT%TYPE )
IS
BEGIN
dbms_output.put_line( p_text );
END foo;
/

CJUSTICE@TEST>EXEC foo( 'Y' );
Y

PL/SQL procedure successfully completed.

CJUSTICE@TEST>EXEC foo( 'N' );
N

PL/SQL procedure successfully completed.

CJUSTICE@TEST>EXEC foo( 'BOLLOCKS' );
BOLLOCKS

PL/SQL procedure successfully completed.
 

I prefer the use of type in parameter and code. There is a significant advantage of this approach to handle changes. There are some gotchas when debugging things. But pluses too. Properly used, it can help define wtf a variable ultimately ties to. How many times have you had to go on a quest to figure out what column in what table is a value coming from, or going to?

Until recently our national debt would have fit in a NUMBER(13,2). Thanks to the bone heads in Washington, we now need a NUMBER(14,2). (Don't get me started...) Now if you wrote this code 10 years ago, thinking our national debt would never go over 9 TRILLION bucks, you might have a lot of code to touch. Or using %type, you might only have one statement to write.

I recently had a .NET developer change a value from a number to a string. It was just a reference value that was being carried through my code. Since I was using %type, the change was pretty much a non-event for me. Alter table blah.... yawn. Done.

This brings up another good point. Frequently, you are just going to carry values through. You're moving them in and out of the database as a go-between of some other component. Take a column like "first_name". Ok, I'm guessing that's a string. I might want to enforce that its a string with something more than white space. Do I care if its a varchar2(20) or a varchar2(100)? No, not so much. To be honest, I don't really want to think about it.

The use of %type also brings a lot of consistency to code. Let's say I have value foo in 5 procs. In the database its a varchar2(30). In one proc its a varchar2(25), in another its a varchar2(50). This code will run fine as along as the length of foo does not exceed 25. I've seen stuff like this sail through QA testing and go boom in production more than once...

So IMHO, the use of %type/%rowtype improves the maintainability and quality of the code.
 

Gave this a little more thought, since my fingers are lazy too....

I'll let the code speak for itself:
CREATE OR REPLACE PACKAGE my_package
IS

SUBTYPE foo is dual.dummy%type;

PROCEDURE test_proc(my_var IN foo);

END my_package;
/

Interesting approach, isn't it....
 

@john

Well thought out answers, thank you.

I can't say I have a great response to it. I'm not absolutely against it, I'm just not for it. You raise valid points of having multiple procedures with different precision defined for a column...but in my opinion, that's a testing issue...but I see your point.

I can't say I haven't done something similar, but I do like to think that I care more about my database code than say a Java or .NET person would (not saying that don't care, but it might be their second language thus not as important).

Good points none-the-less. Until I get to make the rules I'll have to "suffer." ;)