Previous Page | Next Page

SAS/ACCESS Interface to Oracle

Data Types for Oracle

Overview

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. This section includes information about Oracle data types, null and default values, and data conversions.

For more detailed information about Oracle data types, see the Oracle Database SQL Reference.

SAS/ACCESS Interface to Oracle does not support Oracle MLSLABEL and ROWID data types.

Character Data

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. (The limit is 2,000 characters with an Oracle8 Server.) The Oracle 7 Server CHAR data type is not equivalent to the Oracle Version 6 CHAR data type. The Oracle 7 Server CHAR data type is new with the Oracle 7 Server and uses blank-padded comparison semantics.

CLOB (character large object)

contains varying-length character string data that is similar to type VARCHAR2. Type CLOB is character data of variable length with a maximum length of 2 gigabytes. You can define only one CLOB column per table. Available memory considerations might also limit the size of a CLOB 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 limit is 4,000 characters with an Oracle8 Server.) 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.

Numeric Data

BINARY_DOUBLE

specifies a floating-point double binary 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 double binaries because there is no restriction on the number of digits that can appear after the decimal point. Compared to the NUMBER data type, BINARY_DOUBLE provides substantially faster calculations, plus tighter integration with XML and Java environments.

BINARY_FLOAT

specifies a floating-point single binary 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 single binaries because there is no restriction on the number of digits that can appear after the decimal point. Compared to the NUMBER data type, BINARY_FLOAT provides substantially faster calculations, plus tighter integration with XML and Java environments.

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 because there is no restriction on the number of digits that can appear after the decimal point.

NUMBER(p)

specifies an integer of precision p that can range from 1 to 38 and a scale of 0.

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.

Date, Timestamp, and Interval Data

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'.

TIMESTAMP

contains double binary data that represents the SAS DATETIME value, where d is the fractional second precision that you specify on the column and w is derived from the value of d. The default value of d is 6. Although you can override the default format to view more than six decimal places, the accuracy of thie TIMESTAMP value is not guaranteed. When you update or insert TIMESTAMP into SAS, the value is converted to a string value with the form of DDMONYYYY:HH24:MI:SS:SS, where the fractional second precision defaults to d in the SAS DATETIME format. This value is then inserted into Oracle, using this string:

TO_TIMESTAMP(:"TS",'DDMONYYYY:HH24:MI:SSXFF',
'NLS_DATE_LANGUAGE=American'
)
TIMESTAMP WITH TIME ZONE

contains a character string that is w characters long, where w is derived from the fractional second precision that you specify on the column and the additional width needed to specify the TIMEZONE value. When you update or insert TIMESTAMP into SAS, the value is inserted into the column. The NLS_TIMESTAMP_TZ_FORMAT parameter determines the expected format. An error results if users do not ensure that the string matches the expected (default) format.

TIMESTAMP WITH LOCAL TIME ZONE

contains double binary data that represents the SAS DATETIME value. (This data type is the same as TIMESTAMP.) SAS returns whatever Oracle returns. When you update or insert TIMESTAMP into SAS, the value is assumed to be a number representing the number of months.

Note:   A fix for Oracle Bug 2422838 is available in Oracle 9.2.0.5 and above.  [cautionend]

INTERVAL YEAR TO MONTH

contains double binary data that represents the number of months, where w is based on the Year precision value that you specify on the column: INTERVAL YEAR(p) TO MONTH. When you update or insert TIMESTAMP into SAS, the value is assumed to be a number representing the number of months.

INTERVAL DAY TO SECOND

contains double binary data that represents the number of seconds, where d is the same as the fractional second precision that you specify on the column: INTERVAL DAY(p) TO SECOND(d). The width w is derived based on the values for DAY precision (p) and SECOND d precision.

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.


Examples

Here is a TIMESTAMP example. 

%let PTCONN= %str(user=scott pw=tiger  path=oraclev10);
%let CONN= %str(user=scott pw=tiger  path=oraclev10);

options sastrace=",,," sastraceloc=saslog nostsuffix;

proc sql;
connect to oracle ( &PTCONN);

/*execute ( drop table EMP_ATTENDANCE) by oracle;*/

execute ( create table EMP_ATTENDANCE (  EMP_NAME VARCHAR2(10),
     arrival_timestamp TIMESTAMP, departure_timestamp TIMESTAMP ) ) by oracle;
execute ( insert into EMP_ATTENDANCE values 
    ('John Doe', systimestamp, systimestamp+.2) ) by oracle;
execute ( insert into EMP_ATTENDANCE values 
    ('Sue  Day', TIMESTAMP'1980-1-12 10:13:23.33',
        TIMESTAMP'1980-1-12 17:13:23.33' )) by oracle;
quit;

libname ora  oracle &CONN

proc contents data=ora.EMP_ATTENDANCE; run;

proc sql;
 /* reading TIMESTAMP datatype */
select * from ora.EMP_ATTENDANCE;
quit;

 /* appending to TIMESTAMP datatype */
data work.new; 
 EMP_NAME='New  Bee1';
 ARRIVAL_TIMESTAMP='30sep1998:14:00:35.00'dt; 
 DEPARTURE_TIMESTAMP='30sep1998:17:00:14.44'dt;  output;
 EMP_NAME='New  Bee2'; 
 ARRIVAL_TIMESTAMP='30sep1998:11:00:25.11'dt; 
 DEPARTURE_TIMESTAMP='30sep1998:14:00:35.27'dt;  output;
 EMP_NAME='New  Bee3'; 
 ARRIVAL_TIMESTAMP='30sep1998:08:00:35.33'dt;
 DEPARTURE_TIMESTAMP='30sep1998:17:00:35.10'dt; output;
 format ARRIVAL_TIMESTAMP datetime23.2;
 format DEPARTURE_TIMESTAMP datetime23.2; 
run;

title2 'After append';
proc append data=work.new base=ora.EMP_ATTENDANCE ; run;
proc print data=ora.EMP_ATTENDANCE ; run;

 /* updating TIMESTAMP datatype */
proc sql;
update ora.EMP_ATTENDANCE set ARRIVAL_TIMESTAMP=. where EMP_NAME like '%Bee2%' ;

select * from ora.EMP_ATTENDANCE ;

delete from ora.EMP_ATTENDANCE where EMP_NAME like '%Bee2%' ; 

select * from ora.EMP_ATTENDANCE ;

 /* OUTPUT: Creating a brand new table using Data Step*/
data work.sasdsfsec; c_ts='30sep1998:14:00:35.16'dt; k=1; output;
                     c_ts='.'dt; k=2; output;
                     format c_ts datetime23.2; run;

/* picks default TIMESTAMP type */
options sastrace=",,,d" sastraceloc=saslog nostsuffix;
data ora.tab_tsfsec; set work.sasdsfsec; run;
options sastrace=",,," sastraceloc=saslog nostsuffix;
proc delete data=ora.tab_tsfsec; run;

/* Override the default datatype */
options sastrace=",,,d" sastraceloc=saslog nostsuffix;
data ora.tab_tsfsec (dbtype=(c_ts='timestamp(3)'));
 c_ts='30sep1998:14:00:35'dt;
 format c_ts datetime23.; run;
options sastrace=",,," sastraceloc=saslog nostsuffix;
proc delete data=ora.tab_tsfsec; run;

proc print data=ora.tab_tsfsec; run;

 /* OUTPUT: Brand new table creation with bulkload=yes */
title2 'Test OUTPUT with bulkloader';
proc delete data=ora.tab_tsfsec; run;

/* picks default TIMESTAMP type */
data ora.tab_tsfsec (bulkload=yes); set work.sasdsfsec; run;
proc print data=ora.tab_tsfsec; run;

Here is an INTERVAL YEAR TO MONTH example. 

proc sql;
connect to oracle ( &PTCONN);
execute ( drop table PRODUCT_INFO) by oracle;

execute (
 create table PRODUCT_INFO (  PRODUCT VARCHAR2(20), LIST_PRICE number(8,2), 
      WARRANTY_PERIOD INTERVAL YEAR(2) TO MONTH )
)by oracle;
execute (
  insert into PRODUCT_INFO values ('Dish Washer', 4000, '02-00')
)by Oracle;
execute (
  insert into PRODUCT_INFO values ('TV', 6000, '03-06')
)by Oracle;
quit;

proc contents data=ora.PRODUCT_INFO; run;

 /* Shows WARRANTY_PERIOD as number of months */ 
proc print data=ora.PRODUCT_INFO; run;

 /* Shows WARRANTY_PERIOD in a format just like in Oracle*/ 
proc print data=ora.PRODUCT_INFO(dbsastype=(WARRANTY_PERIOD='CHAR(6)')); run;

 /* Add a new product */  
data new_prods;
  PRODUCT='Dryer';  LIST_PRICE=2000;WARRANTY_PERIOD=12;
run;

proc sql;
insert into ora.PRODUCT_INFO select * from new_prods; 
select * from ora.PRODUCT_INFO;
select * from ora.PRODUCT_INFO  where WARRANTY_PERIOD > 24;
quit;

Here is an INTERVAL DAY TO SECOND. 

proc sql;
connect to oracle ( &PTCONN);
execute ( drop table PERF_TESTS) by oracle;

execute (
 create table PERF_TESTS (  TEST_NUMBER number(4) primary key, 
      TIME_TAKEN INTERVAL DAY TO SECOND )
)by oracle;

execute (
  insert into PERF_TESTS values (1,  '0 00:01:05.000200000')
)by Oracle;

execute (
  insert into PERF_TESTS values (2,  '0 00:01:03.400000000')
)by Oracle;

quit;

proc contents data=ora.PERF_TESTS; run;

 /* Shows TIME_TAKEN as number of seconds */ 
proc print data=ora.PERF_TESTS; run;

 /* Shows TIME_TAKEN in a format just like in Oracle*/ 
proc print data=ora.PERF_TESTS(dbsastype=(TIME_TAKEN='CHAR(25)')); run;

 /* Add a new test*/  
data new_tests;
  TEST_NUMBER=3; TIME_TAKEN=50;
run;

proc sql;
insert into ora.PERF_TESTS select * from new_tests; 
select * from ora.PERF_TESTS;

select * from ora.PERF_TESTS  where TIME_TAKEN < 60;
quit;

Binary Data

RAW(n)

contains raw binary data, where n must be at least 1 and cannot exceed 255 bytes. (In Oracle Version 8, the limit is 2,000 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.

BLOB

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.

Oracle Null and Default Values

Oracle has a special value called NULL. An Oracle NULL value means an absence of information and is analogous to a SAS missing value. When SAS/ACCESS reads an Oracle NULL value, it interprets it as a SAS missing value.

By default, Oracle columns accept NULL values. However, you can define columns so that they cannot contain NULL data. NOT NULL tells Oracle not to add a row to the table unless the row has a value for that column. When creating an Oracle table with SAS/ACCESS, you can use the DBNULL= data set option to indicate whether NULL is a valid value for specified columns.

To control how Oracle handles SAS missing character values, use the NULLCHAR= and NULLCHARVAL= data set options.

For more information about how SAS handles NULL values, see Potential Result Set Differences When Processing Null Data.

LIBNAME Statement Data Conversions

This table shows the default formats that SAS/ACCESS Interface to Oracle assigns to SAS variables when using the LIBNAME statement to read from an Oracle table. These default formats are based on Oracle column attributes.

LIBNAME Statement: Default SAS Formats for Oracle Data Types
Oracle Data Type Default SAS Format
CHAR(n) * $w.* (wherew is the minimum of n and the value of the DBMAX_TEXT= option)
VARCHAR2(n) $w. (where w is the minimum of n and the value of the DBMAX_TEXT= option)
LONG $w. (where w is the minimum of 32767 and the value of the DBMAX_TEXT= option)
CLOB $w.* (where w is the minimum of 32767 and the value of the DBMAX_TEXT= option)
RAW(n) $HEXw.* (where w/2 is the minimum of n and the value of the DBMAX_TEXT= option)
LONG RAW $HEXw. (where w/2 is the minimum of 32767 and the value of the DBMAX_TEXT= option)
BLOB RAW $HEXw. (where w/2 is the minimum of 32767 and the value of the DBMAX_TEXT= option)
BINARY_DOUBLE none
BINARY_FLOAT none
NUMBER none
NUMBER(p) w.
NUMBER(p,s) w.d
DATE DATETIME20.
TIMESTAMP DATETIMEw.d (where d is derived from the fractional-second precision)
TIMESTAMP WITH LOCAL TIMEZONE DATETIMEw.d (where d is derived from the fractional-second precision)
TIMESTAMP WITH TIMEZONE $w)
INTERVAL YEAR TO MONTH w. (where w is derived from the year precision)
INTERVAL DAY TO SECOND w.d (where w is derived from the fractional-second precision)
* The value of the DBMAX_TEXT= option can override these values.

SAS/ACCESS does not support Oracle data types that do not appear in this table.

If Oracle data falls outside valid SAS data ranges, the values are usually counted as missing.

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, SAS/ACCESS allows the procedure or application to determine the format. You can also convert numeric data to character data by using the SQL pass-through facility with the Oracle TO_CHAR function. See your Oracle documentation for more details.

The following table shows the default Oracle data types that SAS/ACCESS assigns to SAS variable formats during output operations when you use the LIBNAME statement.

LIBNAME Statement: Default Oracle Data Types for SAS Formats
SAS Variable Format Oracle Data Type
$w. VARCHAR2(w)
$w. (where w > 4000) CLOB
w.d
 NUMBER(p,s)
any date, time, or datetime format without fractional parts of a second DATE
any date, time, or datetime format without fractional parts of a second TIMESTAMP

To override these data types, use the DBTYPE= data set option during output processing.

ACCESS Procedure Data Conversions

The following table shows the default SAS variable formats that SAS/ACCESS assigns to Oracle data types when you use the ACCESS procedure.

PROC ACCESS: Default SAS Formats for Oracle Data Types
Oracle Data Type Default SAS Format
CHAR(n) $n. (n <= 200) $200. (n > 200)
VARCHAR2(n) $n. (n <= 200) $200. (n > 200)
FLOAT BEST22.
NUMBER BEST22.
NUMBER(p) w.
NUMBER(p, s) w.d
DATE DATETIME16.
CLOB $200.
RAW(n) $n. (n < 200) $200. (n > 200)
BLOB RAW $200.

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.

The following table 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

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.

DBLOAD Procedure Data Conversions

The following table shows the default Oracle data types that SAS/ACCESS assigns to SAS variable formats when you use the .

PROC DBLOAD: Default Oracle Data Types for SAS Formats
SAS Variable Format Oracle Data Type
$w.
 CHAR(n)
w. NUMBER(p)
w.d
 NUMBER(p,s)
all other numerics * NUMBER
datetimew.d
 DATE
datew.
 DATE
time. ** NUMBER
* Includes all SAS numeric formats, such as BINARY8 and E10.0.

** Includes all SAS time formats, such as TODw,d and HHMMw,d.

Previous Page | Next Page | Top of Page

Copyright © 2010 by SAS Institute Inc., Cary, NC, USA. All rights reserved.