Oracle PL/SQL Best Practices

Name

DAT-01: Match datatypes to computational usage.

Synopsis

Gee, that’s a general best practice, isn’t it? Of course you should do things the right way. So the question becomes: what datatype is the correct datatype? The following table offers some concrete advice on potential issues you might encounter:

Datatype	Issues and Recommendations
NUMBER	If you don’t specify a precision, as in NUMBER(12,2), Oracle supports up to 38 digits of precision. If you don’t need this precision, you’re wasting memory.
CHAR	This is a fixed-length character string and is mostly available for compatibility purposes with code written in earlier versions of Oracle. The values assigned to CHAR variables are right-padded with spaces, which can result in unexpected behavior. Avoid CHAR unless it’s specifically needed.
VARCHAR	This variation on the VARCHAR2 variable-length declaration is provided by Oracle for compatibility purposes. Eschew VARCHAR in favor of VARCHAR2.
VARCHAR2	The greatest challenge you will run into with VARCHAR2 is to avoid the tendency to hard-code a maximum length, as in VARCHAR2(30). Use %TYPE and SUBTYPE instead, as described later in this chapter. Also, prior to Oracle8, VARCHAR2 variables are treated like variable-length strings for purposes of manipulation and evaluation, but Oracle does allocate the full amount of memory upon declaration. If you declare a variable of VARCHAR2(2000), then Oracle allocates 2000 bytes, even if you use only three.
INTEGER	If your ...