Help using Base SAS procedures

Using bind variables in PROC SQL query to Oracle database server

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 11
Accepted Solution

Using bind variables in PROC SQL query to Oracle database server

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


Accepted Solutions
Solution
‎09-06-2012 07:38 AM
Super User
Super User
Posts: 7,076

Re: Using bind variables in PROC SQL query to Oracle database server

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


All Replies
Super User
Posts: 3,260

Re: Using bind variables in PROC SQL query to Oracle database server

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

Super User
Posts: 5,437

Re: Using bind variables in PROC SQL query to Oracle database server

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
Occasional Contributor
Posts: 11

Re: Using bind variables in PROC SQL query to Oracle database server

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=Smiley TonguermOwner

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

Trusted Advisor
Posts: 1,301

Re: Using bind variables in PROC SQL query to Oracle database server

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;

Occasional Contributor
Posts: 11

Re: Using bind variables in PROC SQL query to Oracle database server

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

Trusted Advisor
Posts: 1,301

Re: Using bind variables in PROC SQL query to Oracle database server

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.

Occasional Contributor
Posts: 11

Re: Using bind variables in PROC SQL query to Oracle database server

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.

Trusted Advisor
Posts: 1,301

Re: Using bind variables in PROC SQL query to Oracle database server

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.

Super User
Posts: 10,044

Re: Using bind variables in PROC SQL query to Oracle database server

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

Solution
‎09-06-2012 07:38 AM
Super User
Super User
Posts: 7,076

Re: Using bind variables in PROC SQL query to Oracle database server

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.

New Contributor
Posts: 2

Re: Using bind variables in PROC SQL query to Oracle database server

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.

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 11 replies
  • 3189 views
  • 0 likes
  • 7 in conversation