SAS/ACCESS for Oracle

Pass-Through Facility Specifics for Oracle

See Overview of the Pass-Through Facility for general information about this feature. Oracle examples are available.

The Pass-Through Facility specifics for Oracle are as follows:

The dbms-name is oracle .
The CONNECT statement is optional. If you omit the CONNECT statement, an implicit connection is made with your OPS$sysid, if it is enabled. When you omit a CONNECT statement, an implicit connection is performed when the first EXECUTE statement or CONNECTION TO component is passed to Oracle. In this case you must use the default DBMS name oracle .
The interface to Oracle can connect to multiple databases (both local and remote) and to multiple user IDs. If you use multiple simultaneous connections, you must use an alias argument to identify each connection. If you do not specify an alias, the default alias, oracle , is used.
The database-connection-arguments for the CONNECT statement are as follows:

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

specifies an optional Oracle user name. If you specify USER=, you must also specify PASSWORD=.

PASSWORD= <'>Oracle-password<'>

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

ORAPW= is an alias for this option.

Note: If you do not wish to enter your Oracle password in uncoded text, see PROC PWENCODE in Base SAS Procedures Guide for a method to encode it.

BUFFSIZE=number-of-rows

specifies the number of rows to retrieve from an Oracle table or view with each fetch. Using this argument can improve the performance of any query to Oracle.

By setting the value of the BUFFSIZE= argument in your SAS programs, you can find the optimal number of rows for a given query on a given table. The default buffer size is 250 rows per fetch. The limit is 32,767 rows per fetch, although a practical limit for most applications is less, depending on the available memory.

PRESERVE_COMMENTS

enables you to 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 argument in the CONNECT statement. Then you specify the hints in the CONNECTION TO component's Oracle SQL query. The hints are entered as comments in the SQL query and are passed to and processed by Oracle.

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 been set 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.

The following example uses the alias DBCON for the DBMS connection (the connection alias is optional):

proc sql;
   connect to oracle as dbcon
       (user=testuser password=testpass buffsize=100 
        path='myorapath');
quit;

The following example connects to Oracle and sends it two EXECUTE statements to process.

proc sql;
   connect to oracle (user=testuser password=testpass);
   execute (create view whotookorders as
      select ordernum, takenby,  
             firstname, lastname, phone
         from orders, employees
         where orders.takenby=employees.empid)
      by oracle;
   execute (grant select on whotookorders 
            to testuser) by oracle;
   disconnect from oracle;
quit;

The following example performs a query, shown in highlighted text, on the Oracle table CUSTOMERS:

proc sql;    
connect to oracle (user=testuser password=testpass);    
select *       
   from connection to oracle          
     (select * from customers
      where customer like '1%');
    disconnect from oracle; 
quit;

In this example, the PRESERVE_COMMENTS argument is specified after the USER= and PASSWORD= arguments. The Oracle SQL query is enclosed in the required parentheses. The SQL INDX command identifies the index for the Oracle query optimizer to use in processing the query. Note that multiple hints are separated with blanks.

proc sql;    
connect to oracle as mycon(user=testuser
        password=testpass preserve_comments);    
select *       
   from connection to mycon
     (select /* +indx(empid) all_rows */
          count(*) from employees);
quit;

Top of Page