Using bind variables in PROC SQL query to Oracle database server

[BLAIS]

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

[SASKiwi]

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

[LinusH]

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.

[BLAIS]

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]

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]

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]

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]

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]

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]

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

[Tom · Accepted solution]

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]

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.