How to quote /* and */ comment characters so that they display and are not hidden

[DougZ]

I use SAS to run Oracle queries with SAS/Access for Oracle.  Oracle has things called "hints" which direct the Oracle optimizer to take a different path than what the optimizer would come up with by itself.  Here's the problem: Oracle hints rely on /* and */ to identify them.  Example:

select /* + FULL(MH) */ account_num, cycle_date, balance, etc etc

from myschema.mytable

where blah blah

My Oracle code is within a macro for repeatability.

How do I pass the /* + FULL(MH) */ to the Oracle code without it disappearing?  I tried %str(/)%str(*) + FULL(MH) %str(*)%str(/) but that doesn't work...

[Krueger]

I think this covers what you are looking for. 

https://documentation.sas.com/?docsetId=acreldb&docsetTarget=p1uutrqh4za0sgn1c26jj7z1k6m9.htm&docset...

[DougZ]

Nice feature but sadly it doesn't seem to work within a Proc SQL select statement.

[Tom]

Are you letting SAS generate the Oracle code?

libname mylib oracle ... schema=myschema preserve_comments;
proc sql;
select /* + FULL(MH) */ account_num, cycle_date, balance
from mylib.mytable
;
quit;

Or are you passing the code explicitly to Oracle?

libname mylib oracle .... preserve_comments ;
proc sql;
connect using mylib;
select * from connection to mylib
(
select /* + FULL(MH) */ account_num, cycle_date, balance
from myschema.mytable
)
;
quit;

If the code is in a macro then you need to add some macro quoting to prevent the macro processor from throwing away the comments.

(
select /%str(*) + FULL(MH) %str(*)/ account_num, cycle_date, balance
from myschema.mytable
)

[DougZ]

Good questions. 

I'm passing the query, including any Oracle hints, as a macro variable string.  That way I can re-use the code with many different problem queries that users send my way.

***********************************************************;
* DECLARE THE QUERY HERE ;
************************************************************;
%let the_query=
select @%str(*) + FULL(MHR) %str(*)@@
       %bquote('&query_name_revision') as query_name, spm_loan_key, asset_date, balance, port_type
from is2.mortgage_history
where asset_date >= '31dec2019'
;
 (note: I changed the slashes above to @ to escape the Communities  invalid HTML  message when I hit Post).

… then:

    ******************************************************;
    * Extract data from the ORACLE table (code SQL below) ;
    ******************************************************;
    proc sql;
        ******************************************************;
        * Establish ORACLE connectivity via SAS/Access-Oracle ;
        ******************************************************;
        connect to oracle (path=&ORACLE_OID_STRING  user=&ORACLE_USER  password="&ORAPASS" preserve_comments);
        ******************************************************;
        * Run the query ;
        ******************************************************;
        create table &out  as
        select * from connection to oracle
        (
         &the_query
           );
    quit;

Here is the resolved macro in the log after it ran (it ignored the hints again)

create table OUTLIB.u01bri_IS2_hints_pl1394_data as select * from connection to oracle ( select 'hints_1394' as query_name, spm_loan_key,
asset_date, balance, port_type from is2.mortgage_history where asset_date >= '31dec2019' );
NOTE: Table OUTLIB.U01BRI_IS2_HINTS_PL1394_DATA created, with 19391212 rows and 5 columns.

[DougZ]

I committed a typo in my code above.  It was as I tried to replace the slashes with "@"s to escape the invalid HTML checker on this site.  I added one too many of these "@"s.  Should be: 

select %str(*) + FULL(MHR) %str(*)/

[DougZ]

OK, still trying to work through this... thought I'd try the actual slashes again but that didn't work.  Do the @-to-slash in your head here  : )

select @%str(*) + FULL(MHR) %str(*)@@

[DougZ]

OK, weird... I'm pretty sure I put just one "@" there at the end.  I'll try again, this time with "S" instead of forward-slash

select S%str(*) + FULL(MHR) %str(*)S

[SASKiwi]

Does your program work correctly if you hard code your SQL with the hint included and not define it in a macro variable?

[DougZ]

Yes, that does work, but it defeats the purpose of having a re-usable program that can run any given query.

[Patrick]

@DougZ 

preserve_comments works and I've used it in the past.

May be first execute some test SQL where everything including the hints is hard-coded and make this work. 

When passing in the syntax via a SAS macro variable where you've used %str() and other quoting functions to create the string then eventually use %unquote(&macvar). 

[Tom]

---

@DougZ wrote:

I committed a typo in my code above.  It was as I tried to replace the slashes with "@"s to escape the invalid HTML checker on this site.  I added one too many of these "@"s.  Should be: 

select %str(*) + FULL(MHR) %str(*)/

---

How are you pasting in the text of your code that you are getting HTML errors?

Just click on the Insert Code or Insert SAS Code button and paste the text into the pop-up window.

[Tom]

First question is does it work without the macro (or the macro variables)?  Just run the PROC SQL step directly.

proc sql;
connect to oracle (path=&ORACLE_OID_STRING  user=&ORACLE_USER  password="&ORAPASS" preserve_comments);
create table &out  as
select * from connection to oracle
(select /* + FULL(MHR) */
 'XXXX' as query_name, spm_loan_key, asset_date, balance, port_type
 from is2.mortgage_history
 where asset_date >= '31dec2019'
);
quit;

If that works then what happens when you put it in a file a %INCLUDE it?

If that works what happens if you wrap that code that has the %include into a macro definition and then execute the macro?

If that works then instead of macro this macro that just generate the code you want to run to a file and %include it.

[DougZ]

Yes, it works without the macro.  But when I try to put that query into a file a %INCLUDE it, Oracle rejects it - doesn't like "%include" as a word I think...

MPRINT(QUERY_TEST_TEMPLATE):   proc sql noprint;
MPRINT(QUERY_TEST_TEMPLATE):   connect to oracle (path=u01bri_pte user=n550513 password="{SAS002}878424311C65F34222798BEB2159842C" preserve_comments);
MPRINT(QUERY_TEST_TEMPLATE):   execute ( explain plan for %include './test_query_w_hints.txt' ) by oracle;
ERROR: ORACLE execute error: ORA-00911: invalid character.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.

[Tom]

---

@DougZ wrote:

Yes, it works without the macro.  But when I try to put that query into a file a %INCLUDE it, Oracle rejects it - doesn't like "%include" as a word I think...

MPRINT(QUERY_TEST_TEMPLATE):   proc sql noprint;
MPRINT(QUERY_TEST_TEMPLATE):   connect to oracle (path=u01bri_pte user=n550513 password="{SAS002}878424311C65F34222798BEB2159842C" preserve_comments);
MPRINT(QUERY_TEST_TEMPLATE):   execute ( explain plan for %include './test_query_w_hints.txt' ) by oracle;
ERROR: ORACLE execute error: ORA-00911: invalid character.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.

---

You need to make the include file have the full EXECUTE command. Not hard to do. Say you have a dataset name CODE with a variable name SQL that has the code to run in the remote database.

filename execute temp;
data _null_:
  set code end=eof;
  file execute;
  if _n_=1 then put 'EXECUTE BY ORACLE (';
  put sql ;
  if eof then put ');' ;
run;

proc sql noprint;
connect to oracle (path=u01bri_pte user=n550513 password="{SAS002}878424311C65F34222798BEB2159842C" preserve_comments);
%include execute ;
quit;