Oracle passthrough syntax frustration

[DougZ]

Here's the background.  I'm on Linux 9.4 (TS1M3) and I want to run a set of my users' typical queries via SAS/Access for Oracle from a cron job, then store the results (in perm SAS file) hourly to monitor performance over time.  The first query I have started with has a couple variables that may change next year, so I decided to put it into a macro with parameters for those vars.  The macro(s) will be called from within the Proc SQL block.

But I'm running into a lot of unexpected issues.  Apparently SAS/Access for Oracle (despite SAS's rich annual fees) doesn't recognize Oracle SQL very well.  For example,

select clean_book_ind, book_type,

    case when gov_ind = 'F' and risk_type_cd not in ('FMG','FNG','GNF','GNV','GNR') then 'FHAP'

    else 'OTHER'

    end private_ind,

sum(balance) upb ... (etc)

had to be changed to include "as" for the tags:

select clean book_ind, book_type,

    case when gov_ind = 'F' and risk_type_cd not in ('FMG','FNG','GNF','GNV','GNR') then 'FHAP'

    else 'OTHER'

    end AS private_ind,

sum(balance) AS upb ... (etc)

so for a lengthy query there is a lot of manual re-work.

Then I couldn't pass in a single-ticks date: where asset_date = %bquote('&asset_date')... (it was '31jan2018) I had to use %unquote(%bquote('&asset_date')).

Next, having cleaned up those issues, I got

ERROR: ORACLE prepare error: ORA-24333: zero iteration count.

Googled it, got to Usage Note 15432: "ORA-24333: zero iteration count" error,

tried that kind of syntax,

(proc sql;
   connect to oracle (user=scott pw=tiger);
   execute (
     create table newemp as select * from emp
   ) by oracle;
   disconnect from oracle;
 quit;)

(unfortunately no "create table work.blahblah as select * from connection to oracle"  lines)

and am now getting

"ERROR: ORACLE execute error: ORA-00900: invalid SQL statement"

but then I copied the SQL statement from my SAS log, pasted it into my Windows SQL Client (Squirrel) and it ran just fine.

Yep, I am frustrated with this product - it's not living up to the reputation it *should* have for the money we are paying - and I don't know what to do next.

[LinusH]

I can understand you frustration, but I think we need to sort some things up.
I believe your first code excerpt is using SAS SQL, accessing a SAS libname, that just happen in this case to be Oracle. SAS SQL complies with the ANSI 92 standard. So for this case, I think you just have to accept the RDBMS AGNOSTIC approach of SAS.

For explicit SQL pass through, anything that goes inside the execute block is totally out of SAS control. What your problem is in this case is hard to tell by the information provided, but using the Oracle DBA would be my first option.

[DougZ]

Linus,

thank you for replying - much appreciated!!

But I don't think it's correct.  First, SAS should be implementing whatever Oracle implements as its SQL, not the ANSI form that causes errors.  That is what I am paying SAS for -- "SAS/Access for Oracle".

Next, I am not using a SAS libname here;  this is an Oracle passthrough code block.

Finally, since my SQL runs when using Squirrel or Oracle SQL Developer Windows software, my Oracle DBAs will say, "That's a SAS problem, not an Oracle problem" - and they're right.

Still frustrated...

[DougZ]

OK - I solved it.  The ORA-00900: invalid SQL statement message went away after I switched my program parameters to point to the correct database  : o

My frustration with the "SAS/Access for Oracle" product still stands.

[ChrisNZ]

@DougZ I don't understand what you are saying.

> First, SAS should be implementing whatever Oracle implements as its SQL, not the ANSI form.  

Not it shouldn't. I have SAS/Access to Oracle, SAS/Access to Teradata, SAS/Access to DB2 on my server. 

I do not want the SAS language to change, and which functions should it then implement? Teradata's or IBM's version?

> I am not using a SAS libname here;  this is an Oracle passthrough code block.

Using a libname does not mean explicit pass-through. You only do explicit pass-through when you submit code using 

execute by  or  from connection to .

> since my SQL runs when using Squirrel [], my Oracle DBAs will say, "That's a SAS problem".

If you use explicit pass-through, and as long as they point to the same libname schema, the same SQL queries that run in Squirrel will run from SAS. Period.

If you don't, you then run SAS code, and you must use SAS syntax. And you can leverage SAS functions in your SQL, like intnx() or putn(). SAS has over 500 functions when Oracle stays in the double digits, so I must prefer the richness of SAS thank you very much.

You don't expect Oracle to understand SAS syntax, and likewise SAS has it own syntax, different from that of Oracle, SQL Server, Hive, DB2, MySQL, etc.

You really must understand the difference between implicit and explicit pass-through.

You'll then reap the benefits of both words when you use the strengths of each, like:

proc sql;

  connect using ORALIB;                               /* sas-only syntax here */

  create table TAB as select intnx(...)               /* sas function         */

  from connection to ORALIB 

  (

     select  ...                                   /* oracle-only syntax here */

     rank() over(...)                              /* oracle window function  */

  );

quit;

[Sven111]

SAS actually doesn't really recognize Oracle SQL syntax at all, and that's by design.  SAS talks to Oracle with either implicit pass-through SQL, in which case your code is SAS SQL only, or you use explicit pass-through SQL and then SAS doesn't do anything except pass the Oracle SQL code you specify on verbatim (well after doing any macro processing). 

proc sql;
   connect to oracle (user=scott pw=tiger);
   execute (
     create table newemp as select * from emp
   ) by oracle;
   disconnect from oracle;
 quit;

Also, the above code you provided wouldn't result in "create table work.blahblah as select * from connection to oracle", it's explicit pass-through SQL, so you'd be creating the table in your Oracle DB, not in SAS at all.  SAS has no idea what your doing, it's just a dumb pipe in this instance.  Depending on your SQL_TRACE options you may see some diagnostic info in the SAS log though.

I do have issues with quoting between Oracle and SAS though, primarily because I use tons of macros and Oracle requires everything to be single quoted and SAS won't resolve macro vars inside single quotes.  I generally just leave my macro vars unquoted and slap %STR(%') before and after any text I want to send to Oracle.  That takes care of 98% of the problems I've had, the other times I have to be a bit more creative.