Previous Page | Next Page

SAS/ACCESS Interface to Oracle

SQL Pass-Through Facility Specifics for Oracle

Key Information

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

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


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 

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;

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; 

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

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

Previous Page | Next Page | Top of Page