SAS/ACCESS Interface to Oracle

LIBNAME Statement Specifics for Oracle

This section describes the LIBNAME statement that SAS/ACCESS Interface to Oracle supports and includes examples. For details about this feature, see Overview of the LIBNAME Statement for Relational Databases.

Here is the LIBNAME statement syntax for accessing Oracle.

LIBNAME libref oracle <connection-options> <LIBNAME-options>;

Arguments

libref

specifies any SAS name that serves as an alias to associate SAS with a database, schema, server, or group of tables and views.

oracle

specifies the SAS/ACCESS engine name for the Oracle interface.

connection-options

provide connection information and control how SAS manages the timing and concurrence of the connection to the DBMS. Here is how these options are defined.

USER=<'>Oracle-user-name<'>

specifies an optional Oracle user name. If the user name contains blanks or national characters, enclose it in quotation marks. If you omit an Oracle user name and password, the default Oracle user ID OPS$sysid is used, if it is enabled. USER= must be used with PASSWORD=.

PASSWORD=<'>Oracle-password<'>

specifies an optional Oracle password that is associated with the Oracle user name. If you omit PASSWORD=, the password for the default Oracle user ID OPS$sysid is used, if it is enabled. PASSWORD= must be used with USER=.

PATH=<'>Oracle-database-specification<'>

specifies the Oracle driver, node, and database. Aliases are required if you are using SQL*Net Version 2.0 or later. In some operating environments, you can enter the information that is required by the PATH= statement before invoking SAS.

SAS/ACCESS uses the same Oracle path designation that you use to connect to Oracle directly. See your database administrator to determine the databases that have been set up in your operating environment, and to determine the default values if you do not specify a database. On UNIX systems, the TWO_TASK environment variable is used, if set. If neither the PATH= nor the TWO_TASK values have been set, the default value is the local driver.

If you specify the appropriate system options or environment variables for Oracle, you can often omit the connection options from your LIBNAME statements. See your Oracle documentation for details.

LIBNAME-options

define how SAS processes DBMS objects. Some LIBNAME options can enhance performance, while others determine locking or naming behavior. The following table describes the LIBNAME options for SAS/ACCESS Interface to Oracle, with the applicable default values. For more detail about these options, see LIBNAME Options for Relational Databases.

SAS/ACCESS LIBNAME Options for Oracle
Option	Default Value
ACCESS=	none
ADJUST_BYTE_SEMANTIC_COLUMN_LENGTHS=	conditional
ADJUST_NCHAR_COLUMN_LENGTHS=	YES
AUTHDOMAIN=	none
CONNECTION=	SHAREDREAD
CONNECTION_GROUP=	none
DB_LENGTH_SEMANTICS_BYTE=	YES
DBCLIENT_MAX_BYTES=	matches the maximum number of bytes per single character of the SAS session encoding
DBSERVER_MAX_BYTES=	usually 1
DBCOMMIT=	1000 when inserting rows; 0 when updating rows, deleting rows, or appending rows to an existing table
DBCONINIT=	none
DBCONTERM=	none
DBCREATE_TABLE_OPTS=	none
DBGEN_NAME=	DBMS
DBINDEX=	NO Use this option only when the object is a TABLE, not a VIEW. Use DBKEY when you do not know whether the object is a TABLE.
DBLIBINIT=	none
DBLIBTERM=	none
DBLINK=	the local database
DBMAX_TEXT=	1024
DBMSTEMP=	NO
DBNULLKEYS=	YES
DBPROMPT=	NO
DBSLICEPARM=	THREADED_APPS,2
DEFER=	NO
DIRECT_EXE=	none
DIRECT_SQL=	YES
INSERTBUFF=	1 (forced default when REREAD_EXPOSURE=YES); otherwise, 10
LOCKWAIT=	YES
MULTI_DATASRC_OPT=	NONE
OR_ENABLE_INTERRUPT=	NO
OR_UPD_NOWHERE=	YES
PRESERVE_COL_NAMES=	NO
PRESERVE_TAB_NAMES=	NO
READBUFF=	250
READ_ISOLATION_LEVEL=	see Locking in the Oracle Interface
READ_LOCK_TYPE=	NOLOCK
REREAD_EXPOSURE	NO
SCHEMA=	SAS accesses objects in the default and public schemas
SHOW_SYNONYMS=	YES
SPOOL=	YES
SQL_FUNCTIONS=	none
SQL_FUNCTIONS_COPY=	none
SQLGENERATION=	DBMS
UPDATE_ISOLATION_LEVEL=	see Locking in the Oracle Interface
UPDATE_LOCK_TYPE=	NOLOCK
UPDATEBUFF=	1
UTILCONN_TRANSIENT=	NO

Oracle LIBNAME Statement Examples

In this first example, default settings are used for the connection options to make the connection. If you specify the appropriate system options or environment variables for Oracle, you can often omit the connection options from your LIBNAME statements. See your Oracle documentation for details.

libname myoralib oracle;

In the next example, the libref MYDBLIB uses SAS/ACCESS Interface to Oracle to connect to an Oracle database. The SAS/ACCESS connection options are USER=, PASSWORD=, and PATH=. PATH= specifies an alias for the database specification, which SQL*Net requires.

libname mydblib oracle user=testuser password=testpass path=hrdept_002;

proc print data=mydblib.employees; 
   where dept='CSR010';   
run;

Top of Page