SQL Pass-Through Facility Specifics for Oracle

Key Information

For general information about this feature, see SQL Pass-Through Facility.

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 option to identify each connection. If you do not specify an alias, the default alias, oracle, is used.
• Here are the database-connection-options for the CONNECT statement.
  • PASSWORD=
  • PATH=
  • USER=
  • READBUFF=
  • PRESERVE_COMMENTS=

CONNECT Statement Examples

This example uses the alias DBCON for the DBMS connection. The connection alias is optional.

proc sql;
   connect to oracle as dbcon
       (user=myusr1 password=mypwd1 buffsize=100
        path='mysrv1');
quit;

This next example connects to Oracle and sends it two EXECUTE statements to process.

proc sql;
   connect to oracle (user=myusr1 password=mypwd1);
   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 myusr1) 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=myusr1 password=mypwd1);
select *
   from connection to oracle
     (select * from customers
      where customer like '1%');
    disconnect from oracle;
quit;

In this example, the PRESERVE_COMMENTS option is specified after the USER= and PASSWORD= options. 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=myusr1
        password=mypwd1 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 an older 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);