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

Hi all

I'm trying to run code in-database (Oracle) using a Hint. The code as such works BUT: The macro compiler removes the Hint as it looks like a comment (at least MPRINT doesn't print the Hint anymore).

Any suggestions what I would need to change in below code example to get the Hint passed to Oracle?

%macro doit();

  proc sql &cba_iem_sql_opt;

    connect to oracle as con1

    (&DBConnOpts PRESERVE_COMMENTS);

    execute

      (

        insert /*+ append */ into target_table

          select

            Col_A,

            Col_B

          from source_table

      )

      by con1;

    disconnect from con1;

  quit;

%mend;

%doit();

Thanks,

Patrick

1 ACCEPTED SOLUTION

Accepted Solutions
Patrick
Opal | Level 21

With @Tom's guidance and a bit more digging I was now able to make it work.

There were two issues in the code:

1. The place I had the "by con1":  SAS/ACCESS(R) 9.4 for Relational Databases: Reference, Sixth Edition

2. The macro compiler "eating" the hint: 7290 - Oracle hints are not passed when coded in a SAS macro

Below a code sample which would pass the hint to Oracle.

%macro doit();

  proc sql &cba_iem_sql_opt;

    connect to oracle as con1

    (&DBConnOpts PRESERVE_COMMENTS);

    execute by con1

      (

        insert %str(/)%str(*)+ append %str(*)%str(/) into target_table

          select

            Col_A,

            Col_B

          from source_table

      )

     ;

    disconnect from con1;

  quit;

%mend;

%doit();

View solution in original post

6 REPLIES 6
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Hi,

As far as I am aware there isn't a way to pass hints to OC.  They are not actually part of the SQL at all.

I would also question why you are uploading data directly to a DB.  My suggestion would be to create a table in a work area via passthrough.  Then have a DB function which takes this data and processes it into the database, i.e. does pre-checks, updates audit trails etc.

Patrick
Opal | Level 21

As far as I am aware there isn't a way to pass hints to OC

It's pass-through SQL and the Oracle Hint is valid syntax. Option "PRESERVE_COMMENTS" has specifically been introduced to allow passing of hints to a data base (by not removing the Hint as a comment).

I would also question why you are uploading data directly to a DB.

I am not uploading anything. Both source and target are in Oracle (different schemas) and I want to have all of the processing in the data base. The target table is my staging table and I'm truncating it before loading any new data into it.


To ask the question I had to simplify the code. Please let's focus on my question which I believe is about the macro compiler removing the Hint as it "believes" it's a SAS comment.

Tom
Super User Tom
Super User

Did you try using %STR() to mask the /* */ so that the macro doesn't "eat" them?

%macro xx ;

  %Str(/)*+ Oracle hint *%str(/)

%mend xx;

data _null_;

  put "%xx";

run;

Patrick
Opal | Level 21

No, I didn't, but it looks like a good idea. The only thing I could think of so far is storing the whole hint in a macro variable and then use this macro variable - but I kind-of don't like the approach.

I'll give your suggestion a go once I'm on site again and will post the outcome here.

Tom
Super User Tom
Super User

Try something like this:

%macro hint(str) ;

  %nrstr(/)*+ &str *%nrstr(/)

%mend hint;

...

execute

(insert %hint(append) into target_table

  select Col_A

       , Col_B

  from source_table

) by con1;

Patrick
Opal | Level 21

With @Tom's guidance and a bit more digging I was now able to make it work.

There were two issues in the code:

1. The place I had the "by con1":  SAS/ACCESS(R) 9.4 for Relational Databases: Reference, Sixth Edition

2. The macro compiler "eating" the hint: 7290 - Oracle hints are not passed when coded in a SAS macro

Below a code sample which would pass the hint to Oracle.

%macro doit();

  proc sql &cba_iem_sql_opt;

    connect to oracle as con1

    (&DBConnOpts PRESERVE_COMMENTS);

    execute by con1

      (

        insert %str(/)%str(*)+ append %str(*)%str(/) into target_table

          select

            Col_A,

            Col_B

          from source_table

      )

     ;

    disconnect from con1;

  quit;

%mend;

%doit();

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 connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 6 replies
  • 3362 views
  • 3 likes
  • 3 in conversation