This section describes the LIBNAME statement that SAS/ACCESS Interface to Oracle supports. For general information about this feature, see LIBNAME Statement for Relational Databases.
Here is the LIBNAME statement syntax for accessing Oracle.
For general information about the LIBNAME statement that is not specific to SAS/ACCESS, see LIBNAME Statement in SAS Global Statements: Reference.
specifies any SAS name that serves as an alias to associate SAS with a database, schema, server, or group of tables and views.
oraclespecifies the SAS/ACCESS engine name for the Oracle interface.
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.
If you specify the appropriate system options or environment variables for Oracle, you can often omit the options from your LIBNAME statements. See your Oracle documentation for details.
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.
Alias: UID=
Restriction: When you specify <'>Oracle-user-name<'> in the LIBNAME statement, SAS changes the user name to uppercase characters, even if the user name uses quotation marks.
|
User Name Conditions |
What to Know or Do |
|---|---|
|
Enclosed in single quotation marks: ' |
The SAS/ACCESS Oracle
engine ignores the single quotation marks, and Oracle converts characters
to all uppercase (from |
|
Contains lowercase or mixed-case characters:
|
First enclose characters in double quotation marks, and then enclose everything in single quotation marks: '" '" |
|
Contains blanks or national characters:
|
Enclose characters in single or double quotation marks: ' " |
|
Contains a macro reference:
|
Enclose characters in double quotation marks:
|
|
Contains one or more of these SQL*Loader special characters: = @ /
|
Enclose all characters in single quotation marks, and then enclose everything in double quotation marks.
|
Required: If you specify USER=, you must also specify 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.
Alias: ORAPW=, PASS=, PWD=, PW=
Required: If you specify USER=, you must also specify PASSWORD=.
Important: The SQL*Loader
that the BULKLOAD= data set option calls takes user name and password as command-line arguments.
This means that a user who runs the ps command
while the SQL*Loader is running could see the user credentials. To
conceal the user name and password, be sure that the BL_PARFILE= data
set option is also specified.
Tip: If you do not wish to enter your Oracle password in uncoded text, see PROC PWENCODE in the Base SAS Procedures Guide for a method to encode it.
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 also the default values if you do not specify a database. To learn more about how to set up default connections to an Oracle database without specifying a value for the PATH environment variable, refer to the information about TWO_TASK (on UNIX) or LOCAL (on Windows) in the environment variables section of your Oracle documentation.
specifies the number of rows to retrieve from an Oracle table or view with each fetch. Using this option can improve the performance of any query to Oracle. By specifying the value of the READBUFF= option in your SAS programs, you can find the optimal number of rows for a given query on a given table.
Alias: BUFF= and BUFFSIZE= are aliases for READBUFF=.
Default: 250 rows per fetch
Restriction: Although this value can be up to 2,147,483,647 rows per fetch, this depends on available memory. A practical limit for most applications is less, so the total maximum buffer size must not exceed 2GB.
lets you pass additional information (called hints) to Oracle for processing. These hints might direct the Oracle query optimizer to choose the best processing method based on your hint.
You specify PRESERVE_COMMENTS as an option in the CONNECT statement. You then specify the hints in the Oracle SQL query for the CONNECTION TO component. Hints are entered as comments in the SQL query and are passed to and processed by Oracle.
Restriction: This option is valid only in the CONNECTION statement, not in the LIBNAME statement. For the LIBNAME statement, you can use the PRESERVE_COMMENTS= LIBNAME option.
specify how SAS processes DBMS objects. Some LIBNAME options can enhance performance, and others determine locking or naming behavior. The table below describes the LIBNAME options for SAS/ACCESS Interface to Oracle, with the applicable default values. This table also identifies LIBNAME options that are valid in the CONNECT statement in the SQL procedure.For more information, see LIBNAME Options for Relational Databases.
|
Option |
Default Value |
Valid in CONNECT |
|---|---|---|
|
none |
|
|
|
conditional |
● |
|
|
YES |
● |
|
|
none |
● |
|
|
<database-name> |
|
|
|
SHAREDREAD |
● |
|
|
none |
● |
|
|
YES |
● |
|
|
(TABLES VIEWS) |
|
|
|
YES or NO, based on the SAS encoding |
● |
|
|
matches the maximum number of bytes per single character of the SAS session encoding |
● |
|
|
1000 (when inserting rows), 0 (when updating, deleting, or appending to an existing table) |
|
|
|
none |
● |
|
|
none |
● |
|
|
none |
|
|
|
DBMS |
● |
|
|
NO |
|
|
|
none |
|
|
|
none |
|
|
|
the local database |
|
|
|
1024 |
● |
|
|
NO |
|
|
|
YES |
|
|
|
YES |
|
|
|
NO |
● |
|
|
YES or NO, based on the Oracle server encoding |
● |
|
|
usually 1 |
● |
|
|
THREADED_APPS,2 |
|
|
|
NO |
● |
|
|
none |
|
|
|
YES |
|
|
|
500 1 is the forced default when REREAD_EXPOSURE=YES |
|
|
|
YES |
|
|
|
NONE |
|
|
|
NONE |
|
|
|
none |
|
|
|
YES |
● |
|
|
NO |
● |
|
|
YES |
|
|
|
none |
|
|
|
NO |
|
|
|
NO |
● |
|
|
NO |
|
|
|
250 |
● |
|
|
|
||
|
NOLOCK |
● |
|
|
NO |
● |
|
|
SAS accesses objects in the default and public schemas |
|
|
|
NO |
|
|
|
YES |
|
|
|
none |
|
|
|
none |
|
|
|
none |
|
|
|
|
||
|
NOLOCK |
● |
|
|
1 |
|
|
|
NO |
|
This example uses default values 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 this next example, the MYDBLIB libref uses the Oracle interface to connect to an Oracle database. 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=myusr1 password=mypwd1 path=mysrv1;
proc print data=mydblib.employees;
where dept='CSR010';
run;This next example connects to Oracle using the CONNECT_DATA= descriptor.
libname x oracle user=myusr1 pw=mypwd1
path="(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP) (HOST = pinkfloyd) (PORT = 1521))
)
(CONNECT_DATA =
(SID = alien )
)
)"
;