7 Using SQL Data Types in Database Applications

7.1.1 How the Correct Data Type Increases Data Integrity

The correct data type increases data integrity by acting as a constraint. For example, if you use a datetime data type for a column of dates, then only dates can be stored in that column. However, if you use a character or numeric data type for the column, then eventually someone will store a character or numeric value that does not represent a date. You could write code to prevent this problem, but it is more efficient to use the correct data type. Therefore, store characters in character data types, numbers in numeric data types, and dates and times in datetime data types.

7.1.2 How the Most Specific Data Type Decreases Storage Requirements

In addition to using the correct data type, use the most specific length or precision; for example:

• When creating a VARCHAR2 column intended for strings of at most n characters, specify VARCHAR2(n).

• When creating a column intended for integers, use the data type NUMBER(38) rather than NUMBER.

Besides acting as constraints and thereby increasing data integrity, length and precision affect storage requirements.

If you give every column the maximum length or precision for its data type, then your application needlessly allocates many megabytes of RAM. For example, suppose that a query selects 10 VARCHAR2(4000) columns and a bulk fetch operation returns 100 rows. The RAM that your application must allocate is 10 x 4,000 x 100—almost 4 MB. In contrast, if the column length is 80, the RAM that your application must allocate is 10 x 80 x 100—about 78 KB. This difference is significant for a single query, and your application will process many queries concurrently. Therefore, your application must allocate the 4 MB or 78 KB of RAM for each connection.

Therefore, do not give a column the maximum length or precision for its data type only because you might need to increase that property later. If you must change a column after creating it, then use the ALTER TABLE statement (described in Oracle Database SQL Language Reference). For example, to increase the length of a column, use:

ALTER TABLE table_name MODIFY column_name VARCHAR2(larger_number)

7.1.3 How the Correct Data Type Improves Performance

The correct data type improves performance because the incorrect data type can result in the incorrect execution plan. (For information about the execution plan, see Section 2.2.3.)

Example 7-1 performs the same conceptual operation—selecting rows whose dates are between December 31, 2000 and January 1, 2001—for three columns with different data types and shows the execution plan for each query. In the three execution plans, compare Rows (cardinality), Cost, and Operation.

CREATE TABLE t (str_date, date_date, number_date, data)
AS
SELECT TO_CHAR(dt+rownum,'yyyymmdd')              str_date,    -- VARCHAR2
       dt+rownum                                  date_date,   -- DATE
       TO_NUMBER(TO_CHAR(dt+rownum,'yyyymmdd'))   number_date, -- NUMBER
       RPAD('*',45,'*')                           data
FROM (SELECT TO_DATE('01-jan-1995', 'dd-mm-yyyy') dt
      FROM all_objects)
ORDER BY DBMS_RANDOM.VALUE
/

CREATE INDEX t_str_date_idx ON t(str_date);
CREATE INDEX t_date_date_idx ON t(date_date);
CREATE INDEX t_number_date_idx ON t(number_date);

BEGIN
  DBMS_STATS.GATHER_TABLE_STATS (
    'HR',
    'T',
    method_opt => 'for all indexed columns size 254',
    cascade => TRUE
  );
END;
/

SET AUTOTRACE ON EXPLAIN

SELECT * FROM t WHERE str_date BETWEEN '20001231' AND '20010101'
ORDER BY str_date;

STR_DATE DATE_DATE NUMBER_DATE DATA
-------- --------- ----------- ---------------------------------------------
20001231 31-DEC-00    20001231 *********************************************
20010101 01-JAN-01    20010101 *********************************************
 
2 rows selected.
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 948745535
 
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |   236 | 11092 |   216   (8)| 00:00:01 |
|   1 |  SORT ORDER BY     |      |   236 | 11092 |   216   (8)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| T    |   236 | 11092 |   215   (8)| 00:00:01 |
---------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter("STR_DATE"<='20010101' AND "STR_DATE">='20001231')

SELECT * FROM t WHERE number_date BETWEEN 20001231 AND 20010101;
ORDER BY str_date;

STR_DATE DATE_DATE NUMBER_DATE DATA
-------- --------- ----------- ---------------------------------------------
20001231 31-DEC-00    20001231 *********************************************
20010101 01-JAN-01    20010101 *********************************************
 
2 rows selected.
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 948745535
 
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |   234 | 10998 |   219  (10)| 00:00:01 |
|   1 |  SORT ORDER BY     |      |   234 | 10998 |   219  (10)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| T    |   234 | 10998 |   218   (9)| 00:00:01 |
---------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter("NUMBER_DATE"<=20010101 AND "NUMBER_DATE">=20001231)

SELECT * FROM t WHERE date_date
  BETWEEN TO_DATE('20001231','yyyymmdd')
  AND     TO_DATE('20010101','yyyymmdd');
  ORDER BY str_date;

STR_DATE DATE_DATE NUMBER_DATE DATA
-------- --------- ----------- ---------------------------------------------
20001231 31-DEC-00    20001231 *********************************************
20010101 01-JAN-01    20010101 *********************************************
 
2 rows selected.
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 2411593187
 
--------------------------------------------------------------------------------
 
| Id  | Operation                            | Name            | Rows  | Bytes |
|   1 |  SORT ORDER BY                       |                 |     1 |    47 |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| T               |     1 |    47 |
|*  3 |    INDEX RANGE SCAN                  | T_DATE_DATE_IDX |     1 |       |
|   0 | SELECT STATEMENT                     |                 |     1 |    47 |
 
------------------------
 Cost (%CPU)| Time     |
 
     4  (25)| 00:00:01 |
     4  (25)| 00:00:01 |
     3   (0)| 00:00:01 |
     2   (0)| 00:00:01 |
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("DATE_DATE">=TO_DATE(' 2000-12-31 00:00:00',
   'syyyy-mm-dd hh24:mi:ss') AND
 
  "DATE_DATE"<=TO_DATE(' 2001-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

7.3.1 Floating-Point Number Components

The formula for a floating-point value is:

(-1)sign.significand.baseexponent

For example, the floating-point value 4.31 is represented:

(-1)0.431.10-2

The components of the preceding representation are:

7.3.2 Floating-Point Number Formats

A floating-point number format specifies how the components of a floating-point number are represented, thereby determining the range and precision of the values that the format can represent. The range is the interval bounded by the smallest and largest values and the precision is the number of significant digits. Both range and precision are finite. If a floating-point number is too precise for a given format, then the number is rounded.

How the number is rounded depends on the base of its format, which can be either decimal or binary. A number stored in decimal format is rounded to the nearest decimal place (for example, 1000, 10, or 0.01). A number stored in binary format is rounded to the nearest binary place (for example, 1024, 512, or 1/64).

NUMBER values are stored in decimal format. For calculations that need decimal rounding, use the NUMBER data type.

Native floating-point values are stored in binary format.

Table 7-2 shows the range and precision of the IEEE 754 single- and double-precision formats and Oracle Database NUMBER. Range limits are expressed as positive numbers, but they also apply to absolute values of negative numbers. (The notation "number e exponent" means number * 10^exponent.)

^Footnote 1 These numbers are from the IEEE Numerical Computation Guide.

(-1)sign 2E (bit0 bit1 bit2 ... bitp-1)

7.3.3 Representing Special Values with Native Floating-Point Data Types

The IEEE 754 standard supports the special values shown in Table 7-5.

Each value in Table 7-5 is represented by a specific bit pattern, except NaN. NaN, the result of any undefined operation, is represented by many bit patterns. Some of these bits patterns have the sign bit set and some do not, but the sign bit has no meaning.

The IEEE 754 standard distinguishes between quiet NaNs (which do not raise additional exceptions as they propagate through most operations) and signaling NaNs (which do). The IEEE 754 standard specifies action for when exceptions are enabled and action for when they are disabled.

In Oracle Database, exceptions cannot be enabled. Oracle Database acts as the IEEE 754 standard specifies for when exceptions are disabled. In particular, Oracle Database does not distinguish between quiet and signaling NaNs. You can use Oracle Call Interface (OCI) to retrieve NaN values from Oracle Database, but whether a retrieved NaN value is signaling or quiet depends on the client platform and is beyond the control of Oracle Database.

The IEEE 754 standard defines these classes of special values:

• Zero

• Subnormal

• Normal

• Infinity

• NaN

The values in each class in the preceding list are larger than the values in the classes that precede it in the list (ignoring signs), except NaN. NaN is unordered with other classes of special values and with itself.

In Oracle Database:

• All NaNs are quiet.

• Any non-NaN value < NaN

• Any NaN == any other NaN

• All NaNs are converted to the same bit pattern.

• -0 is converted to +0.

• IEEE 754 exceptions are not raised.

7.3.4 Comparing Native Floating-Point Values

When comparing numeric expressions, Oracle Database uses numeric precedence to determine whether the condition compares NUMBER, BINARY_FLOAT, or BINARY_DOUBLE values. For information about numeric precedence, see Oracle Database SQL Language Reference. For general information about comparing numeric expressions, see Oracle Database SQL Language Reference.

Comparisons ignore the sign of zero (-0 equals +0).

7.3.5 Arithmetic Operations with Native Floating-Point Data Types

IEEE 754 does not require floating-point arithmetic to be exactly reproducible. Therefore, results of operations can be delivered to a destination that uses a range greater than the range that the operands of the operation use.

You can compute the result of a double-precision multiplication at an extended double-precision destination, but the result must be rounded as if the destination were single-precision or double-precision. The range of the result (that is, the number of bits used for the exponent) can use the range supported by the wider (extended double-precision) destination; however, this might cause a double-rounding error in which the least significant bit of the result is incorrect.

This situation can occur only for double-precision multiplication and division on hardware that implements the IA-32 and IA-64 instruction set architecture. Therefore, except for this case, arithmetic for these data types is reproducible across platforms. When the result of a computation is NaN, all platforms produce a value for which IS NAN is true. However, all platforms do not have to use the same bit pattern.

7.3.6 Conversion Functions for Native Floating-Point Data Types

Oracle Database defines functions that convert between floating-point and other data types, including string formats that use decimal precision (but precision might be lost during the conversion). For example:

• TO_BINARY_DOUBLE, described in Oracle Database SQL Language Reference

• TO_BINARY_FLOAT, described in Oracle Database SQL Language Reference

• TO_CHAR, described in Oracle Database SQL Language Reference

• TO_NUMBER, described in Oracle Database SQL Language Reference

Oracle Database can raise exceptions during conversion. The IEEE 754 standard defines these exceptions:

• Invalid

• Inexact

• Divide by zero

• Underflow

• Overflow

However, Oracle Database does not raise these exceptions for native floating-point data types. Generally, operations that raise exceptions produce the values described in Table 7-6.

7.3.7 Client Interfaces for Native Floating-Point Data Types

Oracle Database supports native floating-point data types in these client interfaces:

• SQL and PL/SQL

  Support for BINARY_FLOAT and BINARY_DOUBLE includes their use as attributes of Abstract Data Types (ADTs), which you create with the SQL statement CREATE TYPE (fully described in Oracle Database PL/SQL Language Reference).

• Oracle Call Interface (OCI)

  For information about using BINARY_FLOAT and BINARY_DOUBLE with OCI, see Oracle Call Interface Programmer's Guide.

• Oracle C++ Call Interface (OCCI)

  For information about using BINARY_FLOAT with OCCI, see Oracle C++ Call Interface Programmer's Guide.

  For information about using BINARY_DOUBLE with OCCI, see Oracle C++ Call Interface Programmer's Guide.

• Pro*C/C++ precompiler

  To use BINARY_FLOAT and BINARY_DOUBLE, set the Pro*C/C++ precompiler command line option NATIVE_TYPES to YES when you compile your application. For information about the NATIVE_TYPES option, see Pro*C/C++ Programmer's Guide.

• Oracle JDBC

  For information about using BINARY_FLOAT and BINARY_DOUBLE with Oracle JDBC, see Oracle Database JDBC Developer's Guide.

7.4.1 Displaying Current Date and Time

The simplest way to display the current date and time is:

SELECT TO_CHAR(SYSDATE, format_model) FROM DUAL

The default format model depends on the initialization parameter NLS_DATE_FORMAT.

The standard Oracle Database default date format is DD-MON-RR. The RR datetime format element lets you store 20th century dates in the 21st century by specifying only the last two digits of the year. For example, in the datetime format DD-MON-YY, 13-NOV-54 refers to the year 1954 in a query issued between 1950 and 2049, but to the year 2054 in a query issued between 2050 and 2149.

The simplest way to display the current date and time using a format model is:

SELECT TO_CHAR(SYSDATE, format_model) FROM DUAL

Example 7-2 uses TO_CHAR with a format model to display SYSDATE in a format with the qualifier BC or AD. (By default, SYSDATE is displayed without this qualifier.)

SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY BC') NOW FROM DUAL;
 

NOW
-----------------------
18-MAR-2009 AD
 
1 row selected.

7.4.2 Inserting and Displaying Dates

When you display and insert dates, Oracle recommends using the TO_CHAR and TO_DATE functions, respectively, with datetime format models.

Example 7-3 creates a table with a DATE column and inserts a date into it, specifying a format model. Then the example displays the date with and without specifying a format model.

DROP TABLE dates;
CREATE TABLE dates (d DATE);
 

INSERT INTO dates VALUES (TO_DATE('OCT 27, 1998', 'MON DD, YYYY'));
 

SELECT d FROM dates;
 

D
---------
27-OCT-98
 
1 row selected.

SELECT TO_CHAR(d, 'YYYY-MON-DD') D FROM dates;

D
--------------------
1998-OCT-27
 
1 row selected.

7.4.3 Inserting and Displaying Times

When you display and insert times, Oracle recommends using the TO_CHAR and TO_DATE functions, respectively, with datetime format models.

In a DATE column:

• The default time is 12:00:00 A.M. (midnight).

  The default time applies to any value in the column that has no time portion, either because none was specified or because the value was truncated.

• The default day is the first day of the current month.

  The default date applies to any value in the column that has no date portion, because none was specified.

Example 7-4 creates a table with a DATE column and inserts three dates into it, specifying a different format model for each date. The first format model has both date and time portions, the second has no time portion, and the third has no date portion. Then the example displays the three dates, specifying a format model that includes both date and time portions.

DROP TABLE birthdays;
CREATE TABLE birthdays (name VARCHAR2(20), day DATE);
 

INSERT INTO birthdays (name, day)
VALUES ('Annie',
        TO_DATE('13-NOV-92 10:56 A.M.','DD-MON-RR HH:MI A.M.')
       );
 
INSERT INTO birthdays (name, day)
VALUES ('Bobby',
        TO_DATE('5-APR-02','DD-MON-RR')
       );
 
INSERT INTO birthdays (name, day)
VALUES ('Cindy',
        TO_DATE('8:25 P.M.','HH:MI A.M.')
       );
 

SELECT name,
       TO_CHAR(day, 'Mon DD, RRRR') DAY,
       TO_CHAR(day, 'HH:MI A.M.') TIME
FROM birthdays;
 

NAME                 DAY                   TIME
-------------------- --------------------- ----------
Annie                Nov 13, 1992          10:56 A.M.
Bobby                Apr 05, 2002          12:00 A.M.
Cindy                Nov 01, 2010          08:25 P.M.
 
3 rows selected.

7.4.4 Arithmetic Operations with Datetime Data Types

The results of arithmetic operations on datetime values are determined by the rules in Oracle Database SQL Language Reference.

SQL has many datetime functions that you can use in datetime expressions. For example, the function ADD_MONTHS returns the date that is a specified number of months from a specified date. For the complete list of datetime functions, see Oracle Database SQL Language Reference.

7.4.5 Conversion Functions for Datetime Data Types

Table 7-8 summarizes the SQL functions that convert to or from datetime data types. For more information about these functions, see Oracle Database SQL Language Reference.

7.4.6 Importing, Exporting, and Comparing Datetime Types

You can import, export, and compare TIMESTAMP WITH TIME ZONE and TIMESTAMP WITH LOCAL TIME ZONE values without worrying about time zone offsets, because the database stores these values in normalized format.

When importing, exporting, and comparing DATE and TIMESTAMP values, you must adjust them to account for any time zone differences between source and target databases, because the database does not store their time zones.

7.5.1 Representing Spatial Data

Spatial data is used by location-enabled applications, geographic information system (GIS) applications, and geoimaging applications. For information about representing spatial data in Oracle Database, see Oracle Database SQL Language Reference.

7.5.2 Representing Multimedia Data

Oracle Multimedia lets Oracle Database store, manage, and retrieve images, audio, video, or other heterogeneous media data. For information about representing multimedia data in Oracle Database, see Oracle Database SQL Language Reference.

7.5.3 Representing Large Amounts of Data

For representing large amounts of data, Oracle Database provides:

• Large Objects (LOBs)

• LONG and LONG RAW Data Types (for backward compatibility)

7.5.4 Representing Searchable Text

Rather than writing low-level code to do full-text searches, you can use Oracle Text. Oracle Text provides indexing, word and theme searching, and viewing capabilities for text in query applications and document classification applications. You can also use Oracle Text to search XML data.

7.5.5 Representing XML Data

If you have information stored as files in XML format, or want to store an ADT in XML format, then you can use the Oracle-supplied type XMLType, described in Oracle Database SQL Language Reference.

With XMLType values, you can use:

• XMLType member functions (see Oracle XML DB Developer's Guide).

• SQL XML functions (see Oracle Database SQL Language Reference)

• PL/SQL DBMS_XML packages (see Oracle Database PL/SQL Packages and Types Reference)

7.5.6 Representing Dynamically Typed Data

Some languages allow data types to change at runtime, and some let a program check the type of a variable. For example, C has the union keyword and the void * pointer, and Java has the typeof operator and wrapper types such as Number.

In Oracle Database, you can create variables and columns that can hold data of any type and test their values to determine their underlying representation. For example, a single table column can have a numeric value in one row, a string value in another row, and an object in another row.

You can use the Oracle-supplied ADT SYS.ANYDATA to represent values of any scalar type or ADT. SYS.ANYDATA has methods that accept scalar values of any type, and turn them back into scalars or objects. Similarly, you can use the Oracle-supplied ADT SYS.ANYDATASET to represent values of any collection type. For more information about these ADTs, see Oracle Database Object-Relational Developer's Guide.

To check and manipulate type information, use the DBMS_TYPES package (described in Oracle Database PL/SQL Packages and Types Reference), as in Example 7-5.

With OCI, use the OCIAnyData and OCIAnyDataSet interfaces, described in Oracle Call Interface Programmer's Guide.

CREATE OR REPLACE TYPE employee_type AS
  OBJECT (empno NUMBER, ename VARCHAR2(10));
/
 
DROP TABLE mytab;
CREATE TABLE mytab (id NUMBER, data SYS.ANYDATA);
 
INSERT INTO mytab (id, data)
VALUES (1, SYS.ANYDATA.ConvertNumber(5));
 
INSERT INTO mytab (id, data)
VALUES (2, SYS.ANYDATA.ConvertObject(Employee_type(5555, 'john')));
 
CREATE OR REPLACE PROCEDURE p IS
  CURSOR cur IS SELECT id, data FROM mytab;
  v_id                        mytab.id%TYPE;
  v_data                      mytab.data%TYPE;
  v_type                      SYS.ANYTYPE;
  v_typecode                  PLS_INTEGER;
  v_typename                  VARCHAR2(60);
  v_dummy                     PLS_INTEGER;
  v_n                         NUMBER;
  v_employee                  employee_type;
  non_null_anytype_for_NUMBER exception;
  unknown_typename            exception;
BEGIN
  FOR x IN cur LOOP
    FETCH cur INTO v_id, v_data;
    EXIT WHEN cur%NOTFOUND;
 
    /* typecode signifies type represented by v_data.
       GetType also produces a value of type SYS.ANYTYPE with methods you
       can call to find precision and scale of a number, length of a
       string, and so on. */
 
       v_typecode := v_data.GetType (v_type /* OUT */);
 
    /* Compare typecode to DBMS_TYPES constants to determine type of data
       and decide how to display it. */
  
    CASE v_typecode
      WHEN DBMS_TYPES.TYPECODE_NUMBER THEN
        IF v_type IS NOT NULL THEN  -- This condition should never happen.
          RAISE non_null_anytype_for_NUMBER;
        END IF;
 
      -- For each type, there is a Get method.
      v_dummy := v_data.GetNUMBER (v_n /* OUT */);
      DBMS_OUTPUT.PUT_LINE
        (TO_CHAR(v_id) || ': NUMBER = ' || TO_CHAR(v_n) );
 
       WHEN DBMS_TYPES.TYPECODE_OBJECT THEN
         v_typename := v_data.GetTypeName();
         IF v_typename NOT IN ('HR.EMPLOYEE_TYPE') THEN
           RAISE unknown_typename;
         END IF;
         v_dummy := v_data.GetObject (v_employee /* OUT */);
         DBMS_OUTPUT.PUT_LINE
           (TO_CHAR(v_id) || ': user-defined type = ' || v_typename ||
            ' ( ' || v_employee.empno || ', ' || v_employee.ename || ' )' );
    END CASE;
  END LOOP;
EXCEPTION
  WHEN non_null_anytype_for_NUMBER THEN
    RAISE_Application_Error (-20000,
      'Paradox: the return AnyType instance FROM GetType ' ||
      'should be NULL for all but user-defined types');
  WHEN unknown_typename THEN
    RAISE_Application_Error( -20000, 'Unknown user-defined type ' ||
      v_typename || ' - program written to handle only HR.EMPLOYEE_TYPE');
END;
/
 
SELECT t.data.gettypename() AS "Type Name" FROM mytab t;
 

Type Name
--------------------------------------------------------------------------------
SYS.NUMBER
HR.EMPLOYEE_TYPE
 
2 rows selected.

7.5.7 Representing ANSI, DB2, and SQL/DS Data

SQL statements that create tables and clusters can use ANSI data types and data types from the IBM products SQL/DS and DB2 (except those noted after this paragraph). Oracle Database converts the ANSI or IBM data type to the equivalent Oracle data type, records the Oracle data type as the name of the column data type, and stores the column data in the Oracle data type. For conversion details, see Oracle Database SQL Language Reference.

7.7.1 ARGn Data Type

In the view V$SQLFN_METADATA, the column DATATYPE is the data type of the function (that is, the data type that the function returns). This data type can be an Oracle data type, data type family (see Section 7.7.3), or ARGn. ARGn is the data type of the nth argument of the function. For example:

• The MAX function (described in Oracle Database SQL Language Reference) returns a value that has the data type of its first argument, so the MAX function has return data type ARG1.

• The DECODE function (described in Oracle Database SQL Language Reference) returns a value that has the data type of its third argument, so the DECODE function has data type ARG3.

7.7.2 DISP_TYPE Data Type

In the view V$SQLFN_METADATA, the column DISP_TYPE is the data type of an argument that can be any expression. An expression is either a single value or a combination of values and SQL functions that has a single value.

7.7.3 SQL Data Type Families

Often, a SQL function argument can have any data type in a data type family. Table 7-11 shows the SQL data type families and their member data types.