BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
chinyong
Calcite | Level 5

 

I had problem using SAS SQL Passthrough facilities for PCFILES with DSN (ODBC) connection to Oracle database.

 

My SAS environment only have "SAS/ACCESS for PC Files" and the "SAS PC File Server".

 

I'm manage to setup the oracle connection through SAS PC File Server --> DSN (ODBC) --> Oracle Database.

 

It only works if the SQL statement is simple or do not contain *. I had tested the same sql statements in oracle database and it ran just a few seconds.

 

 

Case 1: Simple Count = Works and SAS returned result in 1 seconds.

proc sql;
connect to PCFILES (SERVER=localhost DSN=dsnname USER=typeuserhere PWD=typepasswordhere);
create table temp as
select * from connection to PCFILES
(select count(*) cnt from my_table
where rownum<10);
disconnect from PCFILES;
quit;

 

Case 2: With * = Not working, run indefinitely

proc sql; 
connect to PCFILES (SERVER=localhost DSN=dsnname USER=typeuserhere PWD=typepasswordhere);
create table temp as
select * from connection to PCFILES
(select * from my_table
where rownum<10);
disconnect from PCFILES;
quit;

 

Case 3: complex oracle sql - few table joins, oracle functions etc. = Not Working, run indefinitely

proc sql; 
connect to PCFILES (SERVER=localhost DSN=dsnname USER=typeuserhere PWD=typepasswordhere);
create table temp as
select * from connection to PCFILES
(with t as (
select '12345' cli from dual union all
select '67890' cli from dual union all
select '98765' cli from dual)
select t.cli, max(pol.pol) keep (dense_rank last order by iss_dt) lst_pol,
ca.addr_1, ca.addr_2, ca.addr_3, ca.addr_4, ca.zip_code, ca.country_cd
from t, cpl, pol, ca
where t.cli=cpl.cli
and cpl.pol=pol.pol
and in_force(pol.status)='Y'
and cpl.cli=ca.cli
group by t.cli,ca.addr_1, ca.addr_2, ca.addr_3, ca.addr_4, ca.zip_code, ca.country_cd
);
disconnect from PCFILES;
quit;

 

Font in blue color=Passthrough SQL and suppose to process by Oracle.

 

if i amend the SQL and make some syntax error on purpose, i can get error returned by Oracle. It just doesnt work if everything is correct (i tested the same SQL by connect to Oracle directly).

 

Example of syntax error returned by SQL Passthrough: ERROR: CLI describe error: [Oracle][ODBC][Ora]ORA-00904: "xxxxxx": invalid identifier

 

the SQL seem to correctly processed by Oracle but it just hang somewhere.

 

anyone facing this error or do you have any suggestion to solve this? I'm not sure if this problem occur in SAS/ACCESS for Oracle... if yes, it defeat the purpose for me to request for purchase on SAS/ACCESS for Oracle.

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
LinusH
Tourmaline | Level 20

SASTRACE is of little use when it comes to explicit pass through. It's tailored for implicit pass through. Which should be the primary choice. Explicit should only be used if critical parts of the query isn't pass through automatically.

 

That said, everything with the parentheses is the concern of the underlying RDBMS, you need to start your research there, not in SAS.

Data never sleeps

View solution in original post

5 REPLIES 5
Peter_C
Rhodochrosite | Level 12

see what info is provided in the saslog with the sastrace option.. as in the usage note at http://support.sas.com/kb/15/056.html

 

chinyong
Calcite | Level 5

thanks for suggestion. will try to look at it once i got chance later.

hopefully able to find the root cause on the hanging issue.

it just doesn't make sense that a sql take seconds to process by oracle took forever in SAS SQL passthough facility.

but if i added any syntax error in any part of sql randomly, the error is returned as per it processed by oracle.

the problem even more strange where i can pass a simple Oracle specific sql (non ANSI SQL) successfully through SAS sql passthough facility.

LinusH
Tourmaline | Level 20

SASTRACE is of little use when it comes to explicit pass through. It's tailored for implicit pass through. Which should be the primary choice. Explicit should only be used if critical parts of the query isn't pass through automatically.

 

That said, everything with the parentheses is the concern of the underlying RDBMS, you need to start your research there, not in SAS.

Data never sleeps
Peter_C
Rhodochrosite | Level 12

thank you @LinusH for pointing out the confusion I caused by combining 2 ideas without clarification.

 

For examining any implicit pass-thru problem, I'm sure @LinusH would agree that the SASTRACE options are the best way to go.

 

For a quick sql solution that does not need the problems associated with implict  pass-thru, the explicit way might be worth trying separately.

chinyong
Calcite | Level 5

Thanks @LinusH & @Peter_C!!!

 

I tried different odbc now and i think you are right. it seem like not related to SAS.

 

with Oracle ODBC Driver

- Only simple sql works.

- does't support complex sql and select * (hanging indefinitely).

 

with Microsoft ODBC driver for Oracle:

- Most of the SQL works.

- except all SQL must start with "select" statement and cannot start with the Oracle's "With clause".

 

2nd solution works as long as I'm not using Oracle's "With Clause". I think i can survive without the "With Clause" as it can be replaced with subquery anyway (with a little overhead).

 

still curious why Oracle ODBC Driver doesn't work with SAS but Microsoft does.

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 3598 views
  • 1 like
  • 3 in conversation