| SAS/ACCESS Software for Relational Databases: Reference |
Every column in a table has a name and
a data type. The data type tells ORACLE how much physical storage to set aside for the column and the form in which the data is stored. ORACLE data types fall into three categories: types
for character data, types for numeric data, and types for abstract values such as dates. Each of these types is described in the following
sections.
Note:
The
SAS/ACCESS Interface to ORACLE does not support the following ORACLE data types: MLSLABEL and ROWID. SAS/ACCESS provides
an error message when it attempts to read a table that has at least one column that uses an unsupported data type. ![[cautend]](../common/images/cautend.gif)
-
CHAR (n)
-
contains fixed-length character string data with a length of n, where n must be at least 1 and cannot exceed 255 characters. Note that the
ORACLE7 Server CHAR data type is not equivalent to the ORACLE Version 6 CHAR data type. The ORACLE7 Server CHAR data type is new with ORACLE7 Server and uses blank-padded comparison
semantics.
-
LONG
-
contains varying-length character string data that is similar to type VARCHAR2. Type LONG is character data of variable length with a maximum length of 2 gigabytes.
You can define only one LONG column per table. Available memory considerations might also limit the size of a LONG data
type.
-
VARCHAR2(n)
-
contains character string data with a length of n, where n must be at least 1 and cannot exceed 2000 characters. The VARCHAR2 data type is
equivalent to the ORACLE Version 6 CHAR data type except for the difference in maximum lengths. The VARCHAR2 data type uses nonpadded comparison semantics.
-
NUMBER(p,s)
-
specifies a fixed-point number with an implicit decimal point, where p is the total number of digits (precision) and can range from 1 to 38, and
s is the number of digits to the right of the decimal point (scale) and can range from -84 to 127.
-
NUMBER(p)
-
specifies an integer of precision p that can range from 1 to 38 and a scale of 0.
-
NUMBER
-
specifies a floating-point number with a precision of 38. A floating-point value can either specify a decimal point anywhere from the first to the last digit or omit
the decimal point. A scale value does not apply to floating-point numbers since there is no restriction on the number of digits that can appear after the decimal
point.
-
DATE
-
contains date values. Valid dates are from January 1, 4712 BC to December 31, 4712 AD. The default format is DD-MON-YY, for example
'05-OCT-98'.
-
LONG RAW
-
contains raw binary data of variable length up to 2 gigabytes. Values entered into columns of this type must be inserted as character strings in hexadecimal
notation.
-
RAW(n)
-
contains raw binary data where n must be at least 1 and cannot exceed 255 bytes. Values entered into columns of this type must be inserted as character
strings in hexadecimal notation. You must specify n for this data type.
Note:
For compatibility with other DBMSs, ORACLE
supports the syntax for a wide variety of numeric data types, including DECIMAL, INTEGER, REAL, DOUBLE-PRECISION, and SMALLINT. All forms of numeric data types are actually stored in the same internal
ORACLE NUMBER format. The additional numeric data types are variations of precision and scale. A null scale implies a floating-point number, and a non-null scale implies a fixed-point
number.
See LIBNAME Statement Data Conversions and ACCESS Procedure Data Conversions
for a description of how PROC ACCESS and the LIBNAME statement treat each of these types during input operations.
ORACLE has a special value
called NULL. NULL means an absence of information and is analogous to the SAS System's missing value. By default, columns accept NULL values. However, you can define
columns
so that they cannot contain NULL data. For example, the CREATE TABLE statement for the CUSTOMERS table in Appendix 1 defines the first column, CUSTOMER, as CHAR(8) and NOT NULL. NOT NULL tells
ORACLE not to add a row to the table unless the row has a value for CUSTOMER.
LIBNAME Statement: Default SAS Formats for ORACLE Data Types shows the default SAS System
variable formats that the LIBNAME statement assigns to ORACLE data types during input operations. You can override these input and output data types by using the DBTYPE= data set option during output
processing.
Note:
ORACLE data types that are omitted from this table are not supported by
SAS/ACCESS.
If ORACLE data falls outside valid SAS data ranges,
the values are usually counted as missing.
Note:
SAS automatically converts ORACLE NUMBER types to SAS number formats by using an algorithm that determines the correct scale and
precision. When the scale and precision cannot be determined, the SAS/ACCESS Interface to ORACLE allows the procedure or application to determine the
format.
Note:
Reading an ORACLE table that contains a column of
data type LONG produces a great deal of output. Use caution when working with columns of this
type.
LIBNAME Statement: Default ORACLE Data Types for SAS Formats shows the default ORACLE data types that the LIBNAME statement assigns to SAS
variable formats during output operations.
PROC ACCESS: Default SAS Formats for ORACLE Data Types shows the default SAS System variable formats that the ACCESS procedure assigns to ORACLE data types.
Note:
ORACLE data types that
are omitted from this table are not supported by the SAS/ACCESS Interface.
See "ACCESS Procedure Data Conversions for the NUMBER
Data Type" for more information about NUMBER data type conversions. If ORACLE data fall outside valid SAS data ranges, the values are usually counted as missing.
The general form of an ORACLE number is NUMBER(p,s) where p is the precision and
s is the scale of the number. ORACLE defines precision as the total number of digits, with a valid range of -84 to 127. However, a negative scale means that the number is rounded to the
specified number of places to
the left of the decimal. For example, if the number 1,234.56 is specified as data type NUMBER(8,-2), it is rounded to the nearest hundred and stored as
1,200.
Default SAS Formats for ORACLE NUMBER Data Types shows the correlation between the ORACLE NUMBER data types and the default SAS formats that
are created from that data type.
Default SAS Formats for ORACLE NUMBER Data Types
|
ORACLE NUMBER Data Type |
Rules |
Default SAS Format |
|
NUMBER(p) |
0 < p <= 32 |
(p + 1).0 |
|
NUMBER(p,s) |
p > 0, s < 0, |s| < p
|
(p + |s| + 1).0 |
|
NUMBER(p,s) |
p > 0, s < 0, |s| >= p
|
(p + |s| + 1).0 |
|
NUMBER(p,s) |
p > 0, s > 0, s < p
|
(p + 2).s |
|
NUMBER(p,s) |
p > 0, s > 0, s >= p
|
(s + 3).s |
|
NUMBER(p) |
p > 32 |
BEST22. SAS selects format |
|
NUMBER |
p,s unspecified |
BEST22. SAS selects format |
PROC DBLOAD: Default ORACLE Data Types for SAS Formats shows the default ORACLE data types the DBLOAD procedure assigns to SAS variable formats.
Copyright © 1999 by SAS Institute Inc., Cary, NC, USA. All rights reserved.
