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

Hello,

   Did someone know how to put bind variable (parameters) inside a passtrought query using PROC SQL? I didn't found any information about Oracle bind variable in SAS documentation. Bind variable are the first thing to implement in order to increase Oracle query performance.

Thank you,

MB

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

Time to compile queries is not normally of concern for most data analysis projects.

I am not sure that what you have described about bind variables in Oracle will have much impact on most SAS programs. You are better off using SAS macro variables or macros to generate flexible code from the SAS side.

View solution in original post

11 REPLIES 11
SASKiwi
PROC Star

It would be helpful if you could post an example of your bind variable in Oracle SQL. Are you talking about Oracle hints?

LinusH
Tourmaline | Level 20

As the name implies, SQL pass-thru queries are passed to RDBMS without any other  SAS functionality involved except for taking care of any result set. You can't expect that SAS should have complete set of RDBMS specific SQL documentation. If your choice is to use pass-thru, you should search in the Oracle documentation.

Data never sleeps
BLAIS
Fluorite | Level 6

A bind variable is like a parameter. Oracle use the ":" to find bind variable in a query. Below an example:

      SELECT VIEW_NAME FROM ALL_VIEWS WHERE OWNER=:prmOwner

In Oracle, you can also use substitution variable:

      SELECT VIEW_NAME FROM ALL_VIEWS WHERE OWNER=&prmOwner

The difference between the two solution is PERFORMANCE.

If you send the first query with prmOwner='SYS' first and another time with Owner='SYSTEM', it will not parse and compile the query two time but will reuse the first program (like a function with one parameter).

If you use the second query with different parameter, Oracle will parse and recompile a new program every time. The parsing are done for nothing the second time and will use CPU and memory on the server...

I'am surprise to not found that in SAS since I found that when I'am working in C++, C# and VB.

MB

FriedEgg
SAS Employee

Use Explicit Pass-Through

http://support.sas.com/documentation/cdl/en/acreldb/63647/HTML/default/viewer.htm#a003113595.htm

proc sql;

connect to oracle(user=foo pass=bar path=oracle schema=my_schema);

execute by oracle (

  variable deptno number;
  exec :deptno := 10

);
create table in_sas as select * from connection to oracle

( select * from emp where deptno = :deptno );

disconnect from oracle;

quit;

BLAIS
Fluorite | Level 6

The execute( ) by oracle; solution is probably the best but the code didn't work because this is SQL Plus syntax and not PL/SQL code and only PL/SQL code can be execute.

MB

FriedEgg
SAS Employee

Bind variables are not specific to SQL*Plus in any way.  Also, it is better to use execute by oracle(); instead of execute() by oracle;  There are actual differences between the two syntax's.

If you want to see what bind variables you have in Oracle take a look at the v$sql_bind_capture table.

If you are having trouble with something specific you should share the specific example you are trying to execute or contact SAS Technical Support.

BLAIS
Fluorite | Level 6

If I insert the following line to my code:

     execute by oracle (

          variable deptno number;

          exec :deptno := 10

          );

I receive the following error:

     ERROR: ORACLE execute error: ORA-00900: invalid SQL instruction.

Note that I didn't say that bind variable are only for SQL Plus. I say that the "variable" keyword are not a PL/SQL key word but a SQL Plus keyword.

FriedEgg
SAS Employee

Ah, yes, my mistake, you cannot create the bind variable in the manner through SAS, and really that type of bind variable usage example provides no real benefit to anything anyway.  What it is you are actually trying to accomplish?  Bind variables are most useful inside PL/SQL packages or for sql injection/dynamic sql programs, which offer little utility to most things you would want to do with Oracle through SAS.

Ksharp
Super User

It looks like you need a macro variable to pass the binded value to prmOwner

Tom
Super User Tom
Super User

Time to compile queries is not normally of concern for most data analysis projects.

I am not sure that what you have described about bind variables in Oracle will have much impact on most SAS programs. You are better off using SAS macro variables or macros to generate flexible code from the SAS side.

MarkHawker
Calcite | Level 5

Hi Blais,

Just wondered if you have received a satisfactory answer regarding bind variables in SAS. There must be a way to do this.........

Many thanks.

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 11 replies
  • 6727 views
  • 0 likes
  • 7 in conversation