SAS/ACCESS Software for Relational Databases: Reference

SQL Procedure Pass-Through Facility: ORACLE Specifics

For a complete description of the PROC SQL statements, see SQL Procedure Pass-Through Facility Statements.

CONNECT statement, see The following section describes the ORACLE-specific arguments that you use in the CONNECT statement.

Arguments to Connect to ORACLE

The CONNECT statement is optional when connecting to ORACLE. 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.

CONNECT TO ORACLE <AS alias> (USER=ORACLE-user-name
PASSWORD=ORACLE-password PATH="ORACLE-path-designation" BUFFSIZE=number-of-rows PRESERVE_COMMENTS);

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= | PASSWORD= | PW= <'>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 ORAPW=, you must also specify USER=.

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

Pass-Through Examples

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

proc sql;
   connect to oracle as dbcon
       (user=scott password=tiger 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=scott 
                      password=tiger);

   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 underlined text, on the ORACLE table CUSTOMERS:

proc sql;    
connect to oracle (user=scott 
                   password=tiger);    
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 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=scott
        password=tiger preserve_comments);    
select *       
   from connection to mycon
     (select /* +indx(empid) all_rows */
          count(*) from employees);
quit;

Chapter Contents
Previous
Next
Top of Page