SAS/ACCESS Software for Relational Databases: Reference

ACCESS Procedure: ORACLE Specifics

The following section describes the DBMS-specific statements that you use in the SAS/ACCESS Interface to ORACLE.

ACCESS Procedure Statements for ORACLE

To create an access descriptor, you use the DBMS=ORACLE option and the database description statements PATH=, ORAPW=, USER=, and TABLE= in the PROC ACCESS step. The database description statements supply DBMS-specific information to the SAS System. These statements must immediately follow the CREATE statement that specifies the access descriptor to be created.

Database description statements are required only when you create access descriptors. Because ORACLE information is stored in an access descriptor, you do not need to repeat this information when you create view descriptors.

The SAS/ACCESS Interface to ORACLE uses the following procedure statements in line or batch mode:

PROC ACCESS <DBMS=ORACLE | view-descriptor-options>;

CREATE libref.member-name.ACCESS | VIEW <password-option>;

UPDATE libref.member-name.ACCESS | VIEW <password-option>;

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

ORAPW= | ORACLEPW=<'> ORACLE-password<'>;

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

PATH= 'ORACLE-path-designation';

ASSIGN <=> YES | NO | Y | N;

DROP <'>column-identifier-1<'> <...<'>column-identifier-n<'>>

FORMAT <'>column-identifier-1<'><=> SAS-format-name-1
<...<'>column-identifier-n<'><=> SAS-format-name-n>;

QUIT;

RENAME <'>column-identifier-1 <'><=> SAS-variable-name-1
<...<'>column-identifier-n<'><=>SAS-variable-name-n>;

RESET ALL | <'>column-identifier-1<'> <...<'>column-identifier-n<'>>;

SELECT ALL | <'>column-identifier-1<'> <...<'>column-identifier-n<'>>;

SUBSET selection-criteria;

UNIQUE <=> YES | NO | Y | N;

LIST <ALL | VIEW | <'>column-identifier<'>>;

RUN ;

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. See ORACLE Naming Conventions for more information.

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

ORAPW= | ORACLEPW= <'>ORACLE-password<'>;

specifies an optional ORACLE password that is associated with the ORACLE user name. If omitted, the password for the default ORACLE user ID OPS$sysid is used, if it is enabled. ORAPW= must be used with USER=.

TABLE=<'>ORACLE-table<'>;

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 the name in quotation marks.

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.

The values that you specify for the USER=, ORAPW=, TABLE=, and PATH= statements are permanently associated with the access descriptor that you create, and with all the view descriptors that are created from that access descriptor.

ACCESS Procedure Examples

The following 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=scott;
   orapw=tiger;
   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;

The following example creates another view descriptor that is based on the ADLIB.CUSTOMER access descriptor. The view is then printed.

/* 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;

Chapter Contents
Previous
Next
Top of Page