SQL Pass-Through Facility Specifics for Oracle

Key Information

For general information about this feature, see SQL Pass-Through Facility. Oracle examples are available.

Here are the SQL pass-through facility specifics for the Oracle interface.

The dbms-name is oracle.
The CONNECT statement is optional. If you omit it, 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 Oracle interface 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.
Here are the database-connection-arguments for the CONNECT statement.

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. If you do not wish to enter your Oracle password in uncoded text, see PROC PWENCODE in the 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 value can be up to 2,147,483,647 rows per fetch, although a practical limit for most applications is less, depending on the available memory.

PRESERVE_COMMENTS

lets you 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. You then specify the hints in the Oracle SQL query for the CONNECTION TO component. 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.

Examples

This 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;

This next 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;

As shown in highlighted text, this example performs a query on the CUSTOMERS Oracle table:

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 to process the query. 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;

Hints are not preserved in this next example, which uses the prior style of syntax:

execute ( delete /*+ FIRST_ROWS */ from test2 where num2=1)
           by &db

Using the new syntax, hints are preserved in this example:

execute by &db
           ( delete /*+ FIRST_ROWS */ from test2 where num2=2);