LIBNAME Statement Specifics for Oracle

Overview

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

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 it, the password for the default Oracle user ID OPS$sysid is used, if it is enabled. If the password contains spaces or nonalphanumeric characters, you must enclose it in quotation marks. If you specify USER=, you must specify PASSWORD=.

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, and 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
DB_OBJECTS= LIBNAME option	(TABLES VIEWS)
DBCLIENT_MAX_BYTES=	matches the maximum number of bytes per single character of the SAS session encoding
DBCOMMIT=	1000 when inserting rows; 0 when updating, deleting, or appending to an existing table
DBCONINIT=	none
DBCONTERM=	none
DBCREATE_TABLE_OPTS=	none
DBGEN_NAME=	DBMS
DBINDEX=	NO
DBLIBINIT=	none
DBLIBTERM=	none
DBLINK=	the local database
DBMAX_TEXT=	1024
DBMSTEMP=	NO
DBNULLKEYS=	YES
DBPROMPT=	NO
DBSERVER_MAX_BYTES=	usually 1
DBSLICEPARM=	THREADED_APPS,2
DEFER=	NO
DIRECT_EXE=	none
DIRECT_SQL=	YES
INSERTBUFF=	1 is the forced default when REREAD_EXPOSURE=YES; otherwise, 10
LOCKWAIT=	YES
MULTI_DATASRC_OPT=	NONE
OR_BINARY_DOUBLE=
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;