what is the difference between direct and pass through connection from sas to oracle database

[chittamsri1_gmail_com]

Pass through:

proc sql;

     connect to oracle (path=dddd user=aaa orapw=cccc);

     create table work.test as

     select * from connection to oracle (

          select...

           from...

          );

     disconnect from oracle;

quit;

Direct:

proc sql;

     connect to oracle (path=dddd user=aaa orapw=cccc);

     execute (drop table A_TABLE) by oracle;

     execute (commit) by oracle;

     disconnect from oracle;

quit;

[sudi]

you can connect SAS to Oracle database using two methods

1>>>LIBNAME statement using the ORACLE engine (provides direct and dynamic

access to the Oracle data).

libname oralib oracle user=scott password=xxxxx

path=V2o7223 preserve_tab-names=yes;

2>>>The SQL Procedure Pass-Through Facility enables you to pass Oracle SQL

statements to an Oracle database for processing.

proc sql;

connect to oracle(user=scott password=tiger

path=dark_o8150);

create table test1 as

select * from connection to Oracle

(select * from Dept where rownum <=5);

quit;

[RW9]

I think you will find that both your examples are pass through as they send the query through to Oracle, and Oracle executes it.

Pass through:

The query will be sent to the databases SQL interpreter and executed there with the result passed back to SAS.  Hence you need to use terminology permitted by the Database Interpreter, and can use additional functions associated or built within the database software - for example when I used OC it had a DECODE() function which I could use in pass through, but not in Direct.

Direct:

This means the query is interpreted and executed by the SAS SQL interpreter.  Hence you can only use syntax/functions which are defined in SAS and standard SQL.  In my example above I would not be able to use DECODE() as it is not in SAS or standard SQL.

There may also be technical difference's to consider as well.  Not sure for instance if there is any delay/gain associated with pass through over direct, would assume that direct is faster.  Also if you access data from the database, then that would need the connection first, at least until you got the data out into a SAS dataset.

[Haikuo]

has explained the difference in how it works. I 'd like to chip in my 2 cents on when and why you choose which.

1. Libname approach (Oracle/ODBC engine): small data, less joins, you get to see the familiar hierarchy SAS library structure.

2. Pass-thru: Big data, lots of joins. you get to enjoy high performance.

In option 1, data will be brought into SAS for processing. In option 2, data will be preprocessed inside database, which is not only saving the I/O, but also inheritably higher efficient comparing to SAS for its optimized index system, esp. when doing Cartesian product joins. SAS is not a database software, so it is NOT good at doing certain things, and SAS knows it,  this is why when SAS promotes its "High Performance Analytics", there is item called " in-database solution".

Personally I used a lot more pass-thru than libname.

Haikuo