Oracle Reference

Understanding the Table Services Driver for Oracle

The table services driver for Oracle enables table services to read and update legacy Oracle tables. In addition, the driver creates Oracle tables that can be accessed by both table services and Oracle.
The driver for Oracle supports most of the FedSQL functionality. The driver also enables an application to submit native Oracle SQL statements.
The driver for Oracle is a remote driver, which means that it connects to a server process in order to access data. The process might be running on the same machine as the table services, or it might be running on another machine in the network.
The table services driver for Oracle uses shared libraries that are referenced as shared objects in UNIX. You must add the location of the shared libraries to one of the system environment variables, and set any other environment variables required by the Oracle client libraries. The following Bourne shell commands provide an example:
ORAENV_ASK=NO; export ORAENV_ASK
ORACLE_HOME=/dbi/oracle/11g; export ORACLE_HOME
SASORA=V9; export SASORA
PATH=$ORACLE_HOME/bin:/bin:/usr/bin:/usr/ccs/
   bin:/opt/bin:$PATH; export PATH
TMPDIR=/var/tmp; export TMPDIR
LD_LIBRARY_PATH=/usr/openwin/lib:$ORACLE_HOME/
   lib:$LD_LIBRARY_PATH; export LD_LIBRARY_PATH
TWO_TASK=oraclev11; export TWO_TASK

Data Service Connection Options for Oracle

Overview

To access data that is hosted on the table services, a client must submit a connection string, which defines how to connect to the data. The data service connection arguments for an Oracle server include connection options and advanced options.

Connection Options

Connection options are used to establish a connection to a data source. Specify one or more connection options. Here is an example:
driver=sql;conopts=(driver=oracle;
catalog=acat;uid=myuid;pwd=myPass9;
path=oraclev11.abc.123.com:1521/ORA11G)
The driver for Oracle supports the following connection options.
Option
Description
CATALOG
CATALOG=catalog—identifier;
Specifies an arbitrary identifier for an SQL catalog, which groups logically related schemas. Any identifier is valid such as catalog=oracle_test. You must specify a catalog. For the Oracle database, this is a logical catalog name to use as an SQL catalog identifier.
Note: The FedSQL language processor automatically quotes SQL identifiers that do not meet the regular naming convention as defined in SAS FedSQL Reference Guide.
DRIVER
DRIVER=ORACLE;
Identifies the data service to which you want to connect, which is an Oracle database.
Note: You must specify the driver.
PATH
PATH=database-specification;
Specifies the Oracle connect identifier. A connect identifier can be a net service name, a database service name, or a net service alias.
UID
UID=user-id;
Specifies an optional Oracle user ID. If the user ID contains blanks or national characters, enclose it in quotation marks. If you omit an Oracle user ID and password, the default Oracle user ID OPS$sysid is used, if it is enabled.
PWD
PWD=password;
Specifies an optional Oracle database password that is associated with the Oracle user ID. PWD= is always used with UID= and the associated password is case-sensitive. If you omit PWD=, the password for the default Oracle user ID OPS$sysid is used, if it is active.

Advanced Connection Options

The driver for Oracle supports the following advanced connection options.
Option
Description
CT_PRESERVE
CT_PRESERVE = STRICT | SAFE | FORCE | FORCE_COL_SIZE
Enables users to control how data types are mapped. Note that data type mapping is disabled when CT_PRESERVE is set to STRICT. If the requested type does not exist on the target database, an error is returned. Here are the options:
  • STRICT The requested type must exist in the target database. No type promotion occurs. If the type does not exist, an error is returned.
  • SAFE Target data types are upscaled only if they do not result in a loss of precision or scale. When character encodings are changed, the new column size is recalculated to ensure all characters can be stored in the new encoding.
  • FORCE This is the default for all drivers. The best corresponding target data type is chosen, even if it could potentially result in a loss of precision or scale. When character encodings are changed, the new column size is recalculated to ensure all characters can be stored in the new encoding.
  • FORCE_COL_SIZE This option is the same as FORCE, except that the column size for the new encoding is the same as the original encoding. This option can be used to avoid column size creep. However, the resulting column might be too large or too small for the target data.
DEFAULT_ATTR
DEFAULT_ATTR=(attr=value;...)
Used to specify connection handle or statement handle attributes that are supported for initial connect-time configuration, where attr=value corresponds to any of the following options:
  • CURSORS=n- Connection handle option. This option controls the driver’s use of client-side, result set cursors. The possible values are 0, 1, or 2.
    0 Causes the driver to use client-side static cursor emulation if a scrollable cursor is requested but the database server cannot provide one.
    1 Causes the driver to always use client-side static cursor emulation if a scrollable cursor is requested. The database server’s native cursor is not used.
    2 (Default) Causes the driver to never use client-side static cursor emulation if a scrollable cursor is requested. The database server’s native cursor is used if available. Otherwise, the cursor is forward-only.
    Example: DEFAULT_ATTR=(CURSORS=2)
  • USE_EVP=n - Statement handle option. This option optimizes the driver for large result sets. The possible values are 0 (OFF) or 1 (ON), which is the default. Example: DEFAULT_ATTR=(USE_EVP=0)
  • XCODE_WARN=n - Statement handle option. Used to warn about possible character transcoding errors that occur during row input or output operations. Possible values are 0 (returns an error), 1 (returns a warning), or 2 (ignore transaction errors). 0 is the default. Example: DEFAULT_ATTR=(XCODE_WARN=1)
DRIVER_TRACE
DRIVER_TRACE='API | SQL | ALL'
Requests tracing information, which logs transaction records to an external file that can be used for debugging purposes. The driver writes a record of each command that is sent to the database to the trace log based on the specified tracing level, which determines the type of tracing information. Here are the tracing levels:
  • ALL Activates all trace levels.
  • API Specifies that API method calls be sent to the trace log. This option is most useful if you are having a problem and need to send a trace log to SAS Technical Support for troubleshooting.
  • DRIVER Specifies that driver-specific information be sent to the trace log.
  • SQL Specifies that SQL statements that are sent to the database management system (DBMS) be sent to the trace log. Tracing information is DBMS specific, but most table services drivers log SQL statements such as SELECT and COMMIT.
Default: Tracing is not activated.
Note: If you activate tracing, you must also specify the location of the trace log with DRIVER_TRACEFILE=. Note that DRIVER_TRACEFILE= is resolved against the TRACEFILEPATH set in ALTER SERVER. TRACEFILEPATH is relative to the server's content root location.
(Optional) You can control trace log formatting with DRIVER_TRACEOPTIONS=.
Interaction: You can specify one trace level, or you can concatenate more than one by including the | (OR) symbol. For example, driver_trace='api|sql' generates tracing information for API calls and SQL statements.
DRIVER_TRACEFILE
DRIVER_TRACEFILE=’filename’;
Used to specify the name of the text file for the trace log. Include the filename and extension in single or double quotation marks (for example, driver_tracefile='\mytrace.log').
Default: The default TRACEFILE location applies to a relative filename, and it is placed relative to TRACEFILEPATH.
Requirement: DRIVER_TRACEFILE is required when activating tracing using DRIVER_TRACE.
Interaction: (Optional) You can control trace log formatting with DRIVER_TRACEOPTIONS=.
DRIVER_TRACEOPTIONS
DRIVER_TRACEOPTIONS=APPEND | THREADSTAMP | TIMESTAMP;
Specifies options in order to control formatting and other properties for the trace log:
  • APPEND Adds trace information to the end of an existing trace log. The contents of the file are not overwritten.
  • THREADSTAMP Prepends each line of the trace log with a thread identification.
  • TIMESTAMP Prepends each line of the trace log with a time stamp.
Default: The trace log is overwritten with no thread identification or time stamp.
ORA_ENCODING
ORA_ENCODING=UNICODE;
Specifies that the Oracle data be returned in Unicode to table services. UNICODE is the default setting and is independent of the NLS_LANG environment variable setting.
ORNUMERIC
ORANUMERIC=NO | YES
Specifies how numbers that are read from or inserted into the Oracle NUMBER column are treated. This option defaults to YES so that a NUMBER column with precision or scale is described as TKTS_NUMERIC. This option can be specified as both a connection option and a table option. When specified as both a connection and table option, the table option value overrides the connection option.
  • NO Indicates that the numbers are treated as TKTS_DOUBLE values. They might not have precision beyond 14 digits.
  • YES Indicates that non-integer values with explicit precision are treated as TKTS_NUMERIC values. This is the default setting.
USE_CACHED_CATALOG
USE_CACHED_CATALOG=YES | NO;
Specifies whether to use the cached catalog rather than compiling a new catalog on every run. Setting this option to YES can improve the performance of the TKTSForeignKeys API. The default setting is YES.
Note: Before you can use this option, you must complete the following steps:
  1. Create a materialized view. See the example code in Creating a Materialized View (USE_CACHED_CATALOG).
  2. Use the ALTER DSN statement to add the USE_CACHED_CATALOG connection option.

Creating a Materialized View (USE_CACHED_CATALOG)

The following example shows you how to create a materialized view. Use this script if USE_CACHED_CATALOG is set to YES above.
/*-----------------------SAS_CACHED_CATALOG.SQL--------------------------------*/
/* This script is used to create the materialized and the synonym needed to 
   get the ForeignKey metadata.  Work with your DBA to set this up. 
   Materialized views can be complex and so thorough understanding will help us
   use them effectively. Especially deciding how to do the refreshes.
   Here we provide the simplest possible steps to create the required materialized
   view and the command to refresh it manually. The materialized view below can 
   be created in any schema with any name. Feel free to add whatever REFRESH 
   options suits your purpose. Note that you might need additional steps based 
   on the REFRESH option setting. Here we provide the simplest possible way to 
   do this.  The PUBLIC synonym pointing to this Materialized view must be
   named "SAS_CACHED_FK_CATALOG_PSYN". This synonym must be visible to 
   PUBLIC (or the set of users who will be needing Foreignkey metadata) so that
   it is accessible from any   schema.  
*/

Create materialized view SAS_CACHED_FK_CATALOG_MATVIEW REFRESH ON DEMAND as SELECT
PKAC.OWNER as PKTABLE_SCHEM,
PKAC.TABLE_NAME as PKTABLE_NAME,
PKACC.COLUMN_NAME as PKCOLUMN_NAME,
FKAC.OWNER as FKTABLE_SCHEM,
FKAC.TABLE_NAME as FKTABLE_NAME,
FKACC.COLUMN_NAME as FKCOLUMN_NAME,
FKACC.POSITION as KEY_SEQ,
FKAC.CONSTRAINT_NAME as FK_NAME,
PKAC.CONSTRAINT_NAME as PK_NAME 
from 
sys.all_constraints PKAC, sys.all_constraints FKAC,
sys.all_cons_columns PKACC, sys.all_cons_columns FKACC

where  

FKAC.r_constraint_name=PKAC.constraint_name and
FKAC.constraint_name=FKACC.constraint_name and
PKAC.constraint_name=PKACC.constraint_name and PKAC.constraint_type='P' and
FKAC.constraint_type='R' and FKAC.owner=FKACC.owner and PKAC.owner=PKACC.owner
and PKAC.table_name=PKACC.table_name and FKAC.table_name=FKACC.table_name and
FKACC.position = PKACC.position ;

/* The synonym name *must* be SAS_CACHED_FK_CATALOG_PUBLIC_SYNONYM */
create public synonym  SAS_CACHED_FK_CATALOG_PSYN for SAS_CACHED_FK_CATALOG_MATVIEW;
grant all on  SAS_CACHED_FK_CATALOG_PSYN to PUBLIC;

/*---------Manual REFRESH of the Materialized View----------------------------*/
/* Note there are several ways to do this, consult with your DBA.
   Here are a couple of ways:
*/
execute DBMS_MVIEW.REFRESH('SAS_CACHED_FK_CATALOG_MATVIEW');
execute DBMS_SNAPSHOT.REFRESH('SAS_CACHED_FK_CATALOG_MATVIEW', '?');

Oracle Wire Protocol Driver Usage Notes

Wire protocol ODBC drivers communicate directly with a database server without having to communicate through a client library. When you configure the ODBC drivers on Windows or UNIX, you can set certain options. SAS runs best when these options are selected. Some, but not all, are selected by default.
Windows
The options are located on the Advanced or Performance tabs in the ODBC Administrator.
UNIX
The options are available when you are configuring data sources using the ODBC Administrator tool. Values can also be set by editing the odbc.ini file in which their data sources are defined.
Note: When you use a wire protocol driver to create an ODBC connection, the following special considerations apply:
  1. A DSN configuration that uses a wire protocol driver with the catalog option selected returns only the schemas that have associated tables or views. To list all existing schemas, create a DSN without selecting the catalog option.
  2. Verify that the Enable Bulk Load option is active in the ODBC DSN for databases that support this option. The Enable Bulk Load option is not enabled by default in the newer wire protocol drivers. As a result, insert performance suffers.
When configuring an ODBC DSN using the Oracle Wire Protocol driver, set the following advanced options:
  • Application Using Threads
  • Enable SQLDescribeParam
  • Describe at Prepare
  • Enable N-CHAR Support
  • Enable Scrollable Cursors