So after a few years of using them I have something of an opinion on them.
Pros
- 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)
Cons
- 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.
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.
ReplyDeleteWhat 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.
ReplyDeleteIf 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.
ReplyDeleteIn 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.
ReplyDeleteSo "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
ReplyDeleteYour 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?
ReplyDeleteUntil 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....
ReplyDeleteI'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
ReplyDeleteWell 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." ;)