ACCESS Procedure Specifics for Oracle

Overview

For general information about this feature, see ACCESS Procedure. 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=.

  Restriction: PROC ACCESS does not change the value that you specify in USER= to uppercase if you enclose it within single quotation marks. This is a behavior differs from how it is handled in the LIBNAME statement.

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

  Restriction: PROC ACCESS does not change the value that you specify in ORAPW= to uppercase if you enclose it within single quotation marks. This is a behavior differs from how this is handled in the LIBNAME statement.

  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 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, see the information about TWO_TASK (on UNIX) or LOCAL (on Windows) in the environment variables section of your Oracle documentation.

• 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 option 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-library';
libname vlib 'SAS-library';

proc access dbms=oracle;
/* Create an access descriptor */
   create adlib.customer.access;
   user=myusr1;
   orapw=mypwd1;
   table=customers;
   path='mysrv1';
   assign=yes;
   rename customer=custnum;
   format firstorder date9.;
   list all;

/* Create a 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 the 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 the socust view */
proc print data=vlib.socust;
title 'Customers in Southern States';
run;