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, nulls, default values, and data conversions.

Supported Oracle Data Types

Here are the data types that the Oracle engine supports.

• Character data:

  CHAR (n)	NVARCHAR(n)
  CLOB (character large object)	VARCHAR2(n)
  NCHAR(n)

• Numeric data:

  BINARY_DOUBLE	NUMBER(p)
  BINARY_FLOAT	NUMBER(p,s)
  NUMBER

  Note: When performing calculations on numeric values and when storing numeric values, SAS maintains up to 15 digits of precision. When you read values that contain more than 15 decimal digits of precision from a database into SAS, the values that SAS reads are rounded to meet this condition. When you use a large numeric value in a WHERE clause, this rounding can cause unexpected results, such as not selecting desired rows. For noncomputational purposes, such as storing ID values or credit card numbers, you can read the data in as character data. For more information, see Your Options When Choosing Your Needed Degree of Precision.
• Date, timestamp, and interval data:

  DATE, TIMESTAMP	INTERVAL YEAR TO MONTH
  TIMESTAMP WITH TIME ZONE	INTERVAL DAY TO SECOND
  TIMESTAMP WITH LOCAL TIME ZONE

• Binary data:

  RAW(n)

  BLOB

SAS/ACCESS Interface to Oracle does not support the Oracle MLSLABEL data type.

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.

For detailed information about Oracle data types, see your Oracle documentation.

Example 1: Timestamp

%let PTCONN= %str(user=myusr1 pw=mypwd1 path=mysrv1);
%let MYCONN= %str(user=myusr1 pw=mypwd1  path=mysrv1);

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 &MYCONN;

proc contents data=ora.EMP_ATTENDANCE; run;

proc sql;
/* Read TIMESTAMP data type */
select * from ora.EMP_ATTENDANCE;
quit;

 /* Append to TIMESTAMP data type */
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;

/* Uupdate TIMESTAMP data type */
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 datasets library=ora;
  delete tab_tsfsec;run;

/* Override the default data type */
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 datasets library=ora;
  delete tab_tsfsec;run;

proc print data=ora.tab_tsfsec; run;

/* Output:  Create a new table with bulkload=yes */
title2 'Test OUTPUT with bulkloader';
proc datasets library=ora;
  delete tab_tsfsec;run;

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

Example 2: Interval Year to Month

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;
/* Show WARRANTY_PERIOD as number of months */
proc print data=ora.PRODUCT_INFO; run;

/* Show WARRANTY_PERIOD in a format as 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;

Example 3: 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;

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

/* Show 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;

Default Data Types

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.

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 table below shows the default Oracle data types that SAS/ACCESS assigns to SAS variable formats during output operations when you use the LIBNAME statement.

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

ACCESS Procedure Data Conversions

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

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.

This table shows the correlation between the Oracle NUMBER data types and the default SAS formats that are created from that data type.

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 specifies 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

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