Chapter Contents

Previous

Next

LIBNAME Statement: ORACLE Specifics

Valid:

Anywhere

Arguments

libref

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

SAS/ACCESS-engine-name

is the SAS/ACCESS engine name for your DBMS, in this case,

oracle

SAS/ACCESS engines are implemented differently in different operating environments. The engine name is required.

SAS/ACCESS-engine-connection-options

are options that you specify to connect to ORACLE. If the connection options contain characters that are not allowed in SAS names, enclose the values of the options in quotation marks. If you specify the appropriate system options or environment variables for ORACLE, you can often omit the SAS/ACCESS engine connection options. See your ORACLE documentation for details.

SAS/ACCESS-LIBNAME-options

are options that apply to the processing of objects and data in a DBMS, such as its tables or indexes. For example, the UPDATEBUFF= option enables you to specify the number of rows to update or delete in a single ORACLE UPDATE or DELETE transaction. Support for many of these options is DBMS specific.

Some SAS/ACCESS LIBNAME options have the same names as SAS/ACCESS data set options. When you specify an option in the LIBNAME statement, it applies to objects and data that are referenced by the libref. A SAS/ACCESS data set option applies only to the data set on which it is specified. If a like-named option is specified in both the SAS/ACCESS engine LIBNAME statement and after a data set name (which references a DBMS table or view), the SAS System uses the value that is specified later, on the data set name. See Data Set Options: ORACLE Specifics for more information.

Details

The LIBNAME statement associates a libref with a SAS/ACCESS engine to access tables or views in a database management system (DBMS). The SAS/ACCESS engine enables you to connect to a particular DBMS and to specify a DBMS table or view name in a two-level SAS name.

For example, in MYDBLIB.EMPLOYEES_Q2, MYDBLIB is a SAS libref that points to a particular group of DBMS objects, and EMPLOYEES_Q2 is a DBMS table name in the default schema. When you specify MYDBLIB.EMPLOYEES_Q2 in a DATA step or procedure, you dynamically access the DBMS table. Beginning in Version 7, SAS software supports reading, updating, creating, and deleting DBMS tables dynamically.

To disassociate or clear a libref from a DBMS, use a LIBNAME statement, specifying the libref (for example, MYDBLIB) and the CLEAR options as follows:

libname mydblib CLEAR;

SAS/ACCESS Engine Connection Options

The SAS/ACCESS engine connection options for ORACLE are as follows:

	USER=
	PASSWORD=
	PATH=

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

specifies an optional ORACLE user name. If the user name contains blanks or national characters, enclose the name 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=.

USER= can also be specified with the USERNAME= alias.

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

PASSWORD= can also be specified with the PW=, PASS=, ORACLEPW=, and ORAPW= aliases.

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 the SAS System.

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 PATH= nor TWO_TASK have been set, the default value is the local driver.

SAS/ACCESS LIBNAME Options

When you specify any of the following options in the LIBNAME statement, the option is applied to all objects (such as tables and views) in the database that the libref represents.

The SAS/ACCESS interface to ORACLE supports all of the SAS/ACCESS LIBNAME options listed in SAS/ACCESS LIBNAME Options. In addition to the supported options, the following LIBNAME options are used only in the interface to ORACLE or have ORACLE-specific aspects to them:

	DBINDEX=
	DBLINK=
	DBPROMPT=
	INSERTBUFF=
	LOCKWAIT=
	ORACLE_73_OR_ABOVE=
	PRESERVE_COL_NAMES=
	PRESERVE_TAB_NAMES=
	READ_ISOLATION_LEVEL=
	READ_LOCK_TYPE=
	READBUFF=
	SCHEMA=
	SHOW_SYNONYMS=
	SPOOL=
	UPDATE_ISOLATION_LEVEL=
	UPDATE_LOCK_TYPE=
	UPDATEBUFF=

DBINDEX=YES | NO

indicates whether SAS calls the DBMS to find indexes on the specified table.

If you omit DBINDEX=, the default value is DBINDEX=NO.

For a full description of this option, refer to the LIBNAME option, DBINDEX=.

DBLINK=database-link

specifies a link in the local database that enables access to tables and views in a remote database.

If you omit DBLINK=, SAS accesses objects in the local database.

A link is a database object that is used to identify an object stored in a remote database. A link contains stored path information and may also contain user name and password information for connecting to the remote database.

DBPROMPT=YES | NO

specifies whether SAS displays a prompting window that enables you to enter SAS/ACCESS engine connection options instead of specifying them on the LIBNAME statement.

If you omit DBPROMPT=, the default value is DBPROMPT=NO. The SAS/ACCESS Interface to ORACLE allows you to enter 30 characters for the USERNAME and PASSWORD and up to 70 characters for the PATH, depending on your platform.

For a full description of this option, refer to the LIBNAME option, DBPROMPT= .

INSERTBUFF=positive-integer

specifies the number of rows in a single ORACLE insert operation.

If you omit INSERTBUFF=, the default value is INSERTBUFF=10. SAS allows the maximum number that is allowed by ORACLE.

Note:   When you assign a value that is greater than INSERTBUFF=1, the SAS application notes that indicate the success or failure of the insert operation may be incorrect because these notes only represent information for a single insert, even when multiple inserts are performed.  

Note:   If specified, the value of the DBCOMMIT= option overrides the value of INSERTBUFF=.  

LOCKWAIT=YES | NO

specifies whether to wait indefinitely until rows are available for locking.

If you specify LOCKWAIT=YES, SAS waits until rows are available for locking. If you specify LOCKWAIT=NO, SAS does not wait and returns an error to indicate that the lock is not available. If you omit LOCKWAIT=, the default value is LOCKWAIT=YES.

ORACLE_73_OR_ABOVE=YES | NO

specifies whether the ORACLE server version is 7.3 or later.

If you specify ORACLE_73_OR_ABOVE=YES or omit this option, SAS can use the SERIALIZABLE isolation level for update locking that is available in ORACLE 7.3 and above. Users with version 7.3 or above may set the ORACLE_73_OR_ABOVE= option to either YES or NO.

For Oracle versions prior to 7.3, updates without locking are performed as they were in SAS Version 6. In Version 6, a row is updated with an additional WHERE clause to ensure that the row has not changed since the time it was read. The update fails if the row has changed. For versions 7.3 and above, updates are performed in serializable transactions. An update on a row automatically fails if the row has been changed since the time the serializable transaction started. (This is not always true; due to current, published ORACLE bug 440366, sometimes an update on a row fails even if the row has not changed. ORACLE offers the following solution: When creating a table, users can increase the number of INITRANS to at least 3 for the table.)

In a scenario where ORACLE_73_OR_ABOVE= is incorrectly set to YES when it should be NO, the Oracle engine detects this error and automatically makes the assumption that the Oracle version is below 7.3. In a scenario where ORACLE_73_OR_ABOVE= is incorrectly set to NO when it should be YES, the Oracle engine does not detect the incorrect setting. The update is performed without using a serializable transaction.

The advantages of setting ORACLE_73_OR_ABOVE=YES are that no extra WHERE clause overhead is incurred, and WHERE clause floating point number comparison problems (precision problems) are avoided.

ORACLE_73_OR_ABOVE= can also be specified with the ORACLE_73= alias.

See also READ_ISOLATION_LEVEL=, UPDATE_ISOLATION_LEVEL=.

PRESERVE_COL_NAMES=YES | NO

preserves spaces, special characters, and mixed case in column names.

If you omit PRESERVE_COL_NAMES=, the default value is PRESERVE_COL_NAMES=NO. If you want to preserve the case or allow characters that are not supported in SAS names, such as '$', in your column names, set PRESERVE_COL_NAMES=YES.

For a full description of this option, refer to the LIBNAME option, PRESERVE_COL_NAMES=.

PRESERVE_TAB_NAMES=YES | NO

preserves spaces, special characters, and mixed case in table names.

If you omit PRESERVE_TAB_NAMES=, the default value is PRESERVE_TAB_NAMES=NO. If you want to preserve case or allow characters that are not supported in SAS names, such as '$', in your object names, including table names, schema names, and link names, set PRESERVE_TAB_NAMES=YES.

For a full description of this option, refer to the LIBNAME option, PRESERVE_TAB_NAMES=.

READ_ISOLATION_LEVEL=READCOMMITTED | SERIALIZABLE

specifies which isolation level for ORACLE to use when it reads tables and views.

If you are using ORACLE Version 7.3 or later, you can set READ_ISOLATION_LEVEL= to READCOMMITTED or SERIALIZABLE. If you specify READ_ISOLATION_LEVEL=READCOMMITTED, SAS uses the standard method of read locking that is available in all ORACLE versions. If you specify READ_ISOLATION_LEVEL=SERIALIZABLE, SAS uses the SERIALIZABLE method of read locking that is available in ORACLE versions 7.3 and later. The ORACLE_73= option must be set to ORACLE_73=YES to use the SERIALIZABLE isolation level.

The SPOOL= option overrides the READ_ISOLATION_LEVEL= option. If SPOOL=DBMS, SAS automatically sets READ_ISOLATION_LEVEL=SERIALIZABLE for ORACLE 7.3 or later and to READ_ONLY for prior versions. If you omit READ_ISOLATION_LEVEL=, the default value is based on the value of the SPOOL= option. In all other cases, the default value is READCOMMITTED.

When READ_ISOLATION_LEVEL is set to SERIALIZABLE, the CONNECTION option must be set to UNIQUE. If not, an error occurs.

Note:   This option should be rarely needed because the SAS/ACCESS engine chooses the appropriate isolation level based on other locking options.  

See also UPDATE_ISOLATION_LEVEL=.

READ_LOCK_TYPE=NOLOCK |ROW | TABLE

specifies how a table is locked during read operations.

If you omit READ_LOCK_TYPE=, the default value is READ_LOCK_TYPE=NOLOCK. If you specify READ_LOCK_TYPE=NOLOCK, table locking is not used during the reading of tables and views. If you specify READ_LOCK_TYPE=ROW, the ORACLE "ROW SHARE" table lock is used during the reading of tables and views. If you specify READ_LOCK_TYPE=TABLE, the ORACLE "SHARE" table lock is used during the reading of tables and views.

When READLOCK_TYPE is set to either TABLE or ROW, the CONNECTION option must be set to UNIQUE. If not, an error occurs.

For a full description of this option, refer to the LIBNAME option, READ_LOCK_TYPE.

READBUFF=positive-integer

specifies the number of rows in a single ORACLE fetch.

If you omit READBUFF=, the default value is READBUFF=25. SAS allows the maximum number that is allowed by ORACLE.

READBUFF= can also be specified with the BUFFSIZE= alias.

SCHEMA=schema-name

specifies a schema name to be used when referring to database objects. SAS can access another user's database objects by using a specified schema name.

If you omit SCHEMA=, SAS accesses objects in the default and public schemas. If PRESERVE_TAB_NAMES=NO, SAS converts the SCHEMA= value to uppercase because all values in the ORACLE data dictionary are uppercase unless quoted.

SHOW_SYNONYMS=<YES | NO>

When set to NO, PROC DATASETS shows only tables and views for the current user or schema, if the SCHEMA= option is specified. The default value is NO. When set to YES, PROC DATASETS shows only the synonyms that represent the tables and views for the current user or schema, if the SCHEMA= option is specified.

Instead of submitting PROC DATASETS, you can click on the libref for the SAS Explorer window to get this same information.

SPOOL=YES | NO | DBMS

specifies whether SAS creates a utility spool file during read operations that are performed with the specified libref.

If you omit SPOOL=, the default value is SPOOL=YES. If SPOOL=DBMS, the SAS/ACCESS Interface to ORACLE satisfies the two-pass requirement by starting a read-only transaction. SPOOL=YES and SPOOL=DBMS have comparable performance results for ORACLE; however, SPOOL=DBMS does not use any disk space.

When SPOOL is set to DBMS, the CONNECTION option must be set to UNIQUE. If not, an error occurs.

For a full description of this option, refer to the LIBNAME option, SPOOL=.

UPDATE_ISOLATION_LEVEL=READCOMMITTED | SERIALIZABLE

specifies which isolation level for ORACLE to use when it updates tables and views.

If you are using ORACLE Version 7.3 or later, you can set UPDATE_ISOLATION_LEVEL to READCOMMITTED or SERIALIZABLE. If you specify UPDATE_ISOLATION_LEVEL=READCOMMITTED, SAS uses the standard method of update locking that is available in all ORACLE versions. If you specify UPDATE_ISOLATION_LEVEL=SERIALIZABLE, SAS uses the SERIALIZABLE method of update locking that is available in ORACLE versions 7.3 and later. The ORACLE_73 option must be set to ORACLE_73=YES to use the SERIALIZABLE isolation level.

If you omit UPDATE_ISOLATION_LEVEL=, the default value is based on the value of the UPDATE_LOCK_TYPE= option. If UPDATE_LOCK_TYPE=NOLOCK, SAS automatically sets UPDATE_ISOLATION_LEVEL=SERIALIZABLE for ORACLE 7.3 or later and READ_ONLY for prior versions. In all other cases, the default value is READCOMMITTED.

Note:   This option should be rarely needed because the SAS/ACCESS engine chooses the appropriate isolation level based on other locking options.  

UPDATE_LOCK_TYPE=NOLOCK | ROW | TABLE

specifies how a table is locked during update operations.

If you omit UDPATE_LOCK_TYPE, the default value is UPDATE_LOCK_TYPE=NOLOCK. If you specify UPDATE_LOCK_TYPE=NOLOCK, table locking is not used during the reading of tables and views for update. If you specify UPDATE_LOCK_TYPE=ROW, the ORACLE "ROW SHARE" table lock is used during the reading of tables and views for update. If you specify UPDATE_LOCK_TYPE=TABLE, the ORACLE "EXCLUSIVE" table lock is used during the reading of tables and views for update.

If UPDATE_LOCK_TYPE=NOLOCK and ORACLE_73=YES, updates are performed using serializable transactions. If UPDATE_LOCK_TYPE=NOLOCK and ORACLE_73=NO, updates are performed using an extra WHERE clause to ensure that the row has not been updated since it was first read. Updates might fail when UPDATE_LOCK_TYPE=NOLOCK because other users might modify a row after the row was read for update.

If the ORACLE_73_OR_ABOVE= option is incorrectly set to YES (meaning that the Oracle server version is below 7.3), the Oracle engine detects this, and the update is performed as if ORACLE_73_OR_ABOVE= were correctly set.

For a full description of this option, refer to the LIBNAME option, UPDATE_LOCK_TYPE=.

UPDATEBUFF=positive-integer

specifies the number of rows in a single ORACLE update/delete operation.

If you omit UPDATEBUFF=, the default value is UPDATEBUFF=1. SAS allows the maximum that ORACLE allows.

Example: Specifying a LIBNAME Statement to Access ORACLE Data

In this example, the libref MYDBLIB uses the SAS/ACCESS Interface to ORACLE to connect to an ORACLE database. The SAS/ACCESS engine connection options are USER=, PASSWORD=, and PATH=, where PATH= specifies an alias for the database specification (as required by SQL*Net).

libname mydblib oracle user=scott 
   password=tiger path='hrdept_002';

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

To access an ORACLE object in another schema, use the SCHEMA= option as in the following example. The schema name is typically a person's user name or ID.

libname mydblib oracle user=gona
   password=twins path='hrdept_002' schema=john;

proc sql; 
select * from mydblib.superv
   where jobcat='BC';

Chapter Contents

Previous

Next

Top of Page

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