The LIBNAME Statement for Relational Databases SCHEMA= LIBNAME Option 123
Oracle Details: To avoid data integrity problems, it is advisable to set
UPDATE_LOCK_TYPE=TABLE if you set REREAD_EXPOSURE=YES.
ODBC and OLE DB Details: To avoid data integrity problems, it is advisable to set
UPDATE_ISOLATION_LEVEL= S (serializable) if you set REREAD_EXPOSURE=YES.
SCHEMA= LIBNAME Option
Enables you to read database objects, such as tables and views, in the specified schema
Valid in:
the SAS/ACCESS LIBNAME statement
DBMS support:
DB2 UNIX/PC, Informix, ODBC, OLE DB, Oracle, Microsoft SQL
Server, SYBASE, Teradata
Default value:
DBMS-specific
Syntax
SCHEMA=schema-name
Details
If this option is omitted, you connect to the default schema for your DBMS.
The values for SCHEMA= are usually case sensitive, so use care when you specify
this option.
Oracle Details: 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 PRESERVE_TAB_NAMES= NO, SAS converts the SCHEMA=value to
uppercase because all values in the Oracle data dictionary are uppercase unless quoted.
Teradata Details: If you omit this option, a libref points to your default Teradata
database, which often has the same name as your user name. You can use this option to
point to a different database. This option enables you to view or modify a different
user’s DBMS tables or views if you have the required Teradata privileges. (For
example, to read another user’s tables, you must have the Teradata privilege SELECT
for that user’s tables.) The Teradata alias for SCHEMA= is DATABASE=. For more
information about changing the default database, see the DATABASE statement in
your Teradata documentation.
Examples
In the following LIBNAME statement example, the SCHEMA= option causes any
reference in SAS to MyDb.Employee to be interpreted by DB2 as Scott.Employee.
libname mydb db2 SCHEMA=SCOTT;
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=testuser
password=testpass path=’hrdept_002’ schema=john;
In the following example, the Oracle SCHEDULE table resides in the AIRPORTS
schema, and is specified as AIRPORTS.SCHEDULE. To access this table in PROC
