SAS/ACCESS Interface to Oracle

ACCESS Procedure Specifics for Oracle

For general information about this feature, see The ACCESS Procedure for Relational Databases. Oracle examples are available.

The Oracle interface supports all ACCESS procedure statements in line and batch modes. See About ACCESS Procedure Statements.

Here are the ACCESS procedure specifics for Oracle.

The PROC ACCESS step DBMS= value is Oracle .
Here are the database-description-statements that PROC ACCESS uses:

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

specifies an optional Oracle user name. If you omit an Oracle password and user name, the default Oracle user ID OPS$sysid is used if it is enabled. If you specify USER=, you must also specify ORAPW=.

ORAPW= <'>Oracle-password<'>

specifies an optional Oracle password that is associated with the Oracle user name. If you omit ORAPW=, the password for the default Oracle user ID OPS$sysid is used, if it is enabled. If you specify ORAPW=, you must also specify 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 path designations that have are up in your operating environment, and to determine the default value if you do not specify a path designation. 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.
Here is the PROC ACCESS step TABLE= statement:

TABLE= <'><Oracle-table-name><'>;

specifies the name of the Oracle table or Oracle view on which the access descriptor is based. This statement is required. The Oracle-table-name argument can be up to 30 characters long and must be a valid Oracle table name. If the table name contains blanks or national characters, enclose it in quotation marks.

Examples

This example creates an access descriptor and a view descriptor based on Oracle data.

options linesize=80;

libname adlib 'SAS-data-library';
libname vlib 'SAS-data-library';

proc access dbms=oracle;

/* create access descriptor */

   create adlib.customer.access;      
   user=testuser;
   orapw=testpass;
   table=customers;
   path='myorapath';
   assign=yes;
   rename customer=custnum;
   format firstorder date9.;
   list all;

/* create view descriptor */

   create vlib.usacust.view;          
   select customer state zipcode name
          firstorder;
   subset where customer like '1%';
run;

This next example creates another view descriptor that is based on the ADLIB.CUSTOMER access descriptor. You can then print the view.

/* create socust view */

proc access dbms=oracle accdesc=adlib.customer;  
   create vlib.socust.view;
   select customer state name contact;
   subset where state in ('NC', 'VA', 'TX');
run;

/* print socust view */

proc print data=vlib.socust;                        
title 'Customers in Southern States';
run;

Top of Page