Previous | Table of Contents | Next

Page 201

Table 10.3 Scalar Data Types (+ as of 7.2; ^ as of 7.3; # as of 8.0.3)


Fixed length character strings **holds NLS data
Type Subtypes Description Value Limits Database Limits
CHAR(s) CHARACTER STRING ROWID* NCHAR#** 0_32,767 bytes optional size defaults to 1. 255 bytes *holds database ROWID string **2,000 bytes
VARCHAR2(s) VARCHAR STRING+ NVARCHAR2#* Variable length character strings *holds NLS data 0_32,767 bytes optional size defaults to 1. 2,000 bytes #4,000 bytes
NUMBER (p, s) NUMERIC DEC DECIMAL INT* INTEGER* FLOAT* REAL DOUBLE PRECISION SMALLINT* Packed decimal values p = precision (total # of digits) s = scale (where rounding occurs) *cannot specify scale Magnitude range is PL/SQL 1.0E-129 _ 9.99E125. Precision is 1_38 (default size is the maximum value supported by system). Scale is -84 _ 127 (default scale is 0). Same size as PL/SQL NUMBER
BINARY_ INTEGER NATURAL* POSITIVE** NATURALN^- POSITIVEN^- * SIGNTYPE#*** Signed binary numbers * range starts at 0 ** range starts at 1 - Predefined as NOT NULL; same ranges as above Magnitude range is -231-1 to 231-1; or ±2,147,483,647 ***limited to (-1, 0, +1) for implementing tri-state logic. No database type exists (use NUMBER)
PLS_ INTEGER^ Signed binary numbers Fastest machine Magnitude range is -231-1 to 231-1; or ±2,147,483,647. No database type exists (use NUMBER)
                                             continues

Page 202

Table 10.3 Continued


Type Subtypes Description Value Limits Database Limits
arithmetic, less storage than NUMBER
DATE An internal date value January 1, 4712 BC - December 31, 4712 AD; time in seconds since midnight (default time is 12:00 midnight) Same as PL/SQL date
BOOLEAN Boolean (logical) values TRUE, FALSE, NULL (unknown value) No database type exists
LONG Variable length character strings 0 - 32,760 bytes (size is not specified) 0_2,147,483,647 bytes
RAW(s) LONG RAW Non- interpreted (character set independent) binary data RAW: 0 - 32,767 bytes; LONGRAW: 0 - 32,760 bytes (same as LONG) (required size must be at least 1) RAW is 255 bytes; LONGRAW is 2,147,483,647
REF CURSOR+ Cursor variable (pointer to cursor) No database type exists

Page 203


Type Subtypes Description Value Limits Database Limits
LOB (Large Object) BFILE* BLOB** CLOB*** NCLOB**** Locator to large, unstructured, or binary data (images, sound, video, documents) stored in the database or in external operating system files 232-1 bytes or 0_4GB *external binary files only (read only) **internal/ external binary data (read/write) ***internal/ external single-byte data (read/write) ****internal/ external fixed width multi- byte data (read/write) Same as PL/SQL LOBs

Subtypes generally have the same value limits as their main counterparts. Oracle provides them with PL/SQL for compatibility with the column data types found in Oracle and other databases. For example, you can use the alternate NUMBER subtypes when you want something more descriptive. If a type takes an optional size parameter, it is put in parentheses as shown. This size parameter must be specified with integer literals only (for example, CHAR(3)). For strings, a value of length 0 is the NULL value (unknown value).

Character strings are treated as scalars. In order to access individual characters, you use the Oracle-supplied character functions SUBSTR(), INSTR(), and so on. The maximum size of a CHAR is specified in bytes, not characters. This means that multibyte character sets will require more storage. Note the shift in treatment of the STRING subtype from version 7.1 to 7.2.

Oracle performs a conversion from NUMBER (packed decimal) data types into an internal floating point representation in order to accomplish numeric operations. In contrast, the BINARY_INTEGER and PLS_INTEGER data types already have this internal format. Loops coded with indexes using BINARY_INTEGER, while limited in the range of values, can execute faster because this conversion is not necessary. PLS_INTEGER is even faster than BINARY_INTEGER because it uses native machine arithmetic, while BINARY_INTEGER and NUMBER rely on math library routines. However, BINARY_INTEGER values can be assigned to NUMBER variables to avoid overflow exceptions, while using PLS_INTEGER will not avoid the overflow exception. This slight semantic difference should not concern you very much.

Negative scale rounds to the left of the decimal; for example, -3 rounds to the nearest thousands place (1000, 2000, 3000, …), while +3 rounds to the nearest thousandth (0.001, 0.002, 0.003, …).

Page 204

Looking at this table, you may realize that LONG and RAW PL/SQL types have limited usefulness when interacting with database columns of those types. The PL/SQL LONG RAW is actually shorter than a RAW! They should have called it a RAW LONG. However, there is an Oracle-supplied routine to get a database LONG value into PL/SQL LONG variables.

The other thing you may notice from the table is that the limits of a database data type and that of the corresponding PL/SQL data type may be significantly different (specifically, the character types). Please bear this in mind while programming. Some value errors you may experience are related to this discrepancy (when inadvertently trying to stuff a longer string into a shorter variable).

Using PL/SQL Data TypesSome examples of scalar variable declarations are as follows:

item_nameCHAR(32); May be padded with trailing spaces
item_category VARCHAR2(32); These strings are not padded
price NUMBER(5,2) Can hold a number between ± 999.99
quantity INTEGER(3); Can hold a number between ± 999
discountable BOOLEAN; Can be TRUE, FALSE, or NULL
discount REAL(3,3) Any real number between ± 0.999
run_date DATE; Date fields take up 7 bytes

Of course, a variable of any data type can hold a NULL value (not just Booleans).

One really slick feature is called "basing" variables. Instead of hard-coding a data type, you use the data type of another variable, database column, or table. You do this with the %TYPE and %ROWTYPE attributes. The %TYPE attribute provides the type and length of the desired database column or variable. The %ROWTYPE attribute allows one to define a record variable whose member variables have the correct type and length for every column in the table or cursor. Each member variable in the record is referenced using dot notation. My convention is to suffix a %ROWTYPE variable with _rec to indicate that it is a record variable. For example:

quantity orders.qty%TYPE; Based on qty column in table orders
discount orders.discount%TYPE; Based on discount column in table orders
orders_rec orders%ROWTYPE; A record based on the table orders

Here are some more examples of using types.

DECLARE  -- define user variables
  MAX_INT CONSTANT INTEGER := +2147483647;
  MAX_STR VARCHAR2(32767);  -- let's use this for typing only
  notes   MAX_STR%TYPE;     -- based on another variable's type
  items_rec items%ROWTYPE;  -- based on a table in the database
  iname     items.item_name%TYPE;  -- based on a column in a table
  last_id   MAX_INT%TYPE;   -- this must be a constant too
BEGIN  -- executable code
  iname := `HAMMER';
  items_rec.item_name := iname;  -- compatible data types
  last_id := 0;                  -- Wait! Aagggh! Runtime error!

Previous | Table of Contents | Next