1 Using SQL Data Types in Database Applications

Specifying Column Lengths as Bytes or Characters

You can specify the lengths of CHAR and VARCHAR2 columns as either bytes or characters. The lengths of NCHAR and NVARCHAR2 columns are always specified in characters, making them ideal for storing Unicode character literals, where a character might consist of multiple bytes. This table shows some column length specifications and their meanings:

When using a multibyte database character encoding scheme, consider carefully the space required for tables with character columns. If the database character encoding scheme is single-byte, then the number of bytes and the number of characters in a column is the same. If it is multibyte, however, then generally there is no such correspondence. A character might consist of one or more bytes, depending on the specific multibyte encoding scheme and whether shift-in/shift-out control codes are present. To avoid overflowing buffers, specify data as NCHAR or NVARCHAR2 if it might use a Unicode encoding that is different from the database character set.

Choosing Between CHAR and VARCHAR2 Data Types

When choosing a data type for a column that stores alphanumeric data in a table, consider:

• Space usage

  Oracle Database blank-pads values stored in CHAR columns but not values stored in VARCHAR2 columns. Therefore, VARCHAR2 columns use space more efficiently than CHAR columns.

• Performance

  Because of the blank-padding difference, a full table scan on a large table containing VARCHAR2 columns might read fewer data blocks than a full table scan on a table containing the same data stored in CHAR columns. If your application often performs full table scans on large tables containing character data, then you might be able to improve performance by storing data in VARCHAR2 columns rather than in CHAR columns.

• Comparison semantics

  When you need ANSI compatibility in comparison semantics, use the CHAR data type. When trailing blanks are important in string comparisons, use the VARCHAR2 data type.

  See Also:

  Oracle Database SQL Language Reference for more information about comparison semantics for these data types

• Future compatibility

  The CHAR and VARCHAR2 data types are fully supported. Today, the VARCHAR data type automatically corresponds to the VARCHAR2 data type and is reserved for future use.

Floating-Point Number Components

A floating-point number has these components:

• Binary-valued sign

• Signed exponent

• Significand

• Base

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:

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.

Topics:

• Binary Floating-Point Formats

• Special Values for Native Floating-Point Formats

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

Comparison Operators for Native Floating-Point Data Types

Oracle Database defines these comparison operators for native floating-point data types:

• Equal to

• Not equal to

• Greater than

• Greater than or equal to

• Less than

• Less than or equal to

• Unordered

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

Arithmetic Operations with Native Floating-Point Data Types

Oracle Database defines these arithmetic operators for native floating-point data types:

• Multiplication

• Division

• Addition

• Subtraction

• Remainder

• Square root

You can define the mode used to round the result of the operation. Exceptions can be raised when operations are performed. Exceptions can also be disabled.

Formerly, Java required floating-point arithmetic to be exactly reproducible. IEEE 754 does not have this requirement. Therefore, results of operations (including arithmetic 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.

Conversion Functions for 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

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

Client Interfaces for Native Floating-Point Data Types

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

• SQL

• PL/SQL

• Oracle Call Interface (OCI)

• Oracle C++ Call Interface (OCCI)

• Pro*C/C++

• JDBC

Topics:

• OCI Native Floating-Point Data Types SQLT_BFLOAT and SQLT_BDOUBLE

• Native Floating-Point Data Types Supported in ADTs

• Pro*C/C++ Support for Native Floating-Point Data Types

Displaying Current Date and Time

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

SELECT SYSDATE FROM DUAL

The preceding command displays the current date and time in the default date format, which 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 2099.

To display SYSDATE in a nondefault format, use the TO_CHAR function with a datetime format model.

Example 1-1 uses TO_CHAR with a format model to display SYSDATE in a nondefault format, which includes 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.

Displaying and Inserting Dates in Nondefault Formats

Although Oracle Database always stores dates in the default date format (set by the initialization parameter NLS_DATE_FORMAT), you can display and insert dates in nondefault formats by using the TO_CHAR and TO_DATE functions, respectively, with datetime format models.

Example 1-2 creates a table with a DATE column and inserts into it a date specified in a nondefault format. The date is stored in the default format, as the first SELECT statement shows. The second SELECT statement displays the date in a nondefault format.

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.

Displaying and Inserting Times in Nondefault Formats

Although Oracle Database always stores times in the 24-hour format HH24:MI:SS, you can display and insert times in nondefault formats by 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 date 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 1-3 creates a table with a DATE column and inserts into it three dates specified in nondefault formats—one with both date and time portions, one with no time portion, and one with no date portion. The first SELECT statement shows the current date. The second SELECT statement displays the three dates in a nondefault format 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 SYSDATE FROM DUAL;
 

SYSDATE
---------
05-NOV-10
 
1 row selected.

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.

Arithmetic Operations with Datetime Data Types

You can perform arithmetic operations on datetime values. The results of such operations 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.

Conversion Functions for Datetime Data Types

Table 1-8 summarizes the SQL functions that convert to or from datetime data types.

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.

Representing Geographic Data

To represent Geographic Information System (GIS) or spatial data in the database, you can use Oracle Spatial features, including the type MDSYS.SDO_GEOMETRY. You can store the data in the database by using either an object-relational or a relational model. You can use a set of PL/SQL packages to query and manipulate the data.

Representing Multimedia Data

Oracle Multimedia enables Oracle Database to store, manage, and retrieve images, audio, video, or other heterogeneous media data in an integrated fashion with other enterprise information. Oracle Multimedia extends Oracle Database reliability, availability, and data management to multimedia content in traditional, Internet, electronic commerce, and media-rich applications.

Whether you store such multimedia data inside the database as BLOB or BFILE values, or store it externally on a web server or other kind of server, you can use Oracle Multimedia to access the data using either an object-relational or a relational model, and manipulate and query the data using a set of ADTs.

Oracle Multimedia provides the ORDAudio, ORDDoc, ORDImage, ORDImageSignature, ORDVideo, and SI_StillImage ADTs (including methods) for these purposes:

• Extracting metadata and attributes from multimedia data

• Retrieving and managing multimedia data from Oracle Multimedia, web servers, file systems, and other servers

• Performing manipulation operations on image data

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)

Representing Searchable Text

Rather than writing low-level code to do full-text searches, you can use Oracle Text. Oracle Text stores the search data in a special kind of index and lets you query the data with operators and PL/SQL packages. This technology enables you to create your own search engine using data from tables, files, or URLs, and combine the search logic with relational queries. You can also search XML data this way with the XPath notation.

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.

When you create an XMLType column in a table, you can store the XML data in any of these ways:

• In a CLOB column

• As binary XML (stored internally as a CLOB)

• Object relationally

XMLType has member functions that access, extract, and query the XML data using W3C XPath expressions (see Oracle XML DB Developer's Guide). Also, Oracle provides SQL XML functions that manipulate or return whole or partial XML documents (see Oracle Database SQL Language Reference) and these PL/SQL packages (described in Oracle Database PL/SQL Packages and Types Reference):

• DBMS_XMLDOM, for accessing XMLType objects

• DBMS_XMLGEN, for converting the results of a SQL query to a canonical XML format

• DBMS_XMLINDEX, for implementing asynchronous indexing

• DBMS_XMLPARSER, for accessing the contents and structure of XML documents

• DBMS_XMLQUERY, for database-to-XMLType functionality

• DBMS_XMLSAVE, for XML-to-database-type functionality

• DBMS_XMLSCHEMA, for managing XML schemas

• DBMS_XMLSTORE, for storing XML data in relational tables

• DBMS_XMLTRANSLATIONS, for translating strings so that they can be searched or displayed in various languages

Representing Dynamically Typed Data

Some languages allow data types to change at run time, 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, as in Example 1-4. For more information about this package, see Oracle Database PL/SQL Packages and Types Reference.

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
  OPEN 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;
  CLOSE cur;
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.

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.

Querying the ROWID Pseudocolumn

Each table in Oracle Database has a pseudocolumn named ROWID, which can appear in a query in either the SELECT list or WHERE clause.

Example 1-5 creates a table of with a column of the data type ROWID, populates it with rowids by querying the ROWID pseudocolumn inside an INSERT statement, and then displays it. The rowids of the table rows show how they are stored.

DROP TABLE t_tab;  -- in case it exists
CREATE TABLE t_tab (col1 ROWID);
 
INSERT INTO t_tab (col1)
SELECT ROWID
FROM employees
WHERE employee_id > 199;
 

SELECT employee_id, rowid
FROM employees
WHERE employee_id > 199;
 

EMPLOYEE_ID ROWID
----------- ------------------
        200 AAAPeSAAFAAAABTAAC
        201 AAAPeSAAFAAAABTAAD
        202 AAAPeSAAFAAAABTAAE
        203 AAAPeSAAFAAAABTAAF
        204 AAAPeSAAFAAAABTAAG
        205 AAAPeSAAFAAAABTAAH
        206 AAAPeSAAFAAAABTAAI
 
7 rows selected.
 

SELECT * FROM t_tab;
 

COL1
------------------
AAAPeSAAFAAAABTAAC
AAAPeSAAFAAAABTAAD
AAAPeSAAFAAAABTAAE
AAAPeSAAFAAAABTAAF
AAAPeSAAFAAAABTAAG
AAAPeSAAFAAAABTAAH
AAAPeSAAFAAAABTAAI
 
7 rows selected.

ROWID Data Type

In heap-organized tables generated by Oracle Database, the values in the ROWID pseudocolumn have the data type ROWID. Internally, this data type is a structure that stores information that the database server needs to access a row. The format of this structure is either restricted, extended, or external binary.

Topics:

• Restricted Internal ROWID Format

• Extended Internal ROWID Format

• External Binary Internal ROWID Format

struct riddef {
    ub4    ridobjnum; /* data obj#--this field is 
                         unused in restricted ROWIDs */
    ub2    ridfilenum;
    ub1    filler;
    ub4    ridblocknum;
    ub2    ridslotnum;
}

UROWID Data Type

In tables that are foreign (that is, not generated by Oracle Database) or index-organized, the values in the ROWID pseudocolumn have the data type UROWID. This data type stores a universal rowid (urowid).

Urowids for foreign tables (such as DB2 tables accessed through a gateway) are called foreign rowids.

Urowids for index-organized tables (whose rows are stored in index leaves, which can move) are called logical rowids. Oracle Database creates logical rowids based on the primary key of the table. The logical rowids do not change if the primary key does not change.

To store urowids in a table, define a column of data type UROWID for the table and then retrieve the value of the ROWID pseudocolumn into that column.

Data Type Conversion During Assignments

For the assignment

variable := expression

if the data type of expression differs from that of variable, then Oracle Database tries to convert the data type of expression to that of variable. For information about when that is possible, see Oracle Database SQL Language Reference.

A character-to-NUMBER conversion succeeds only if the character string represents a valid number. A character-to-DATE conversion succeeds only if the character string satisfies the session default date format. (For information about the default date format, see "Displaying Current Date and Time".)

Examples

Assume that test_package, its public variable var1, and table1_tab are declared as follows:

CREATE OR REPLACE PACKAGE test_package AS
  var1 CHAR(5);
END;
/
 
DROP TABLE table1_tab;
CREATE TABLE table1_tab (col1 NUMBER);

In the assignment

variable := expression

the data type of expression must be either the same as, or implicitly convertible to, the data type of variable. For example, for this assignment, Oracle Database automatically converts zero to the data type of var1, which is CHAR(5):

var1 := 0;

In the statement

INSERT INTO table1_tab (col1) VALUES (expression)

the data type of expression must be either the same as, or implicitly convertible to, the data type of col1. For example, for this statement, Oracle Database automatically converts the string '19' to the data type of col1, which is NUMBER:

INSERT INTO table1_tab (col1) VALUES ('19')

In the statement

UPDATE table1_tab SET column = expression

the data type of expression must be either the same as, or implicitly convertible to, the data type of column. For example, for this statement, Oracle Database automatically converts the string '30' to the data type of col1, which is NUMBER:

UPDATE table1_tab SET col1 = '30';

In the statement

SELECT column INTO variable FROM table1_tab

the data type of column must be either the same as, or convertible to, the data type of variable. For example, for this statement, Oracle Database automatically converts the value selected from col1, which is 30, to the data type of var1, which is CHAR(5):

SELECT col1 INTO var1 FROM table1_tab WHERE col1 = 30;

Data Type Conversion During Expression Evaluation

When evaluating an expression, Oracle Database can perform the same automatic conversions that it does for assignments. The target data type is determined by the context of the expression. For example, if an expression is the operand of an arithmetic operator, then Oracle Database tries to convert the value of the expression to NUMBER; if the expression is the operand of a string function, then Oracle Database tries to convert the value of the expression to VARCHAR2.

For the assignment

variable := expression

Oracle Database first evaluates expression, using the conversion rules for expressions. If the evaluation succeeds, the result is a single value of a single data type, which Oracle Database tries to assign to variable, using the conversion rules for assignments.

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 "Data Type Families"), 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.

DISP_TYPE Data Type

In the view V$SQLFN_METADATA, 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.

Data Type Families

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