|SAS/ACCESS Interface to Oracle|
For general information about this feature, see Overview of the SQL Pass-Through Facility. Oracle examples are available.
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.
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=.
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 of BUFFSIZE= 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.
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.
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.
proc sql; connect to oracle as dbcon (user=testuser password=testpass buffsize=100 path='myorapath'); quit;
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;
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:
Using the new syntax, hints are preserved in this example: