Access Pass Through queris on Oracle Tables

[joel009]

I need to run a validation, checking for existing Serial Numbers, from an Access table to Oracle tables linked to Access via ODBC. The Oracle tables are so large that I started using Pass Through queries in Access to enhance response time.
My problem is I can't figure out how to use the Pass Through query to loop through the Access table, pass the SN as a parameter to the query, usng the Pass Through queries.
This question might be better off in the Access forums, I'm not sure.
Aso, would I be better off having the DBA create a Stored Procedure that I could call from Access?

Any ideas would be appreciated greatly.

Joel

 

[SantaMufasa]

Joel,

My initial reaction is, yes, run your inquiry past the folks in the many Access forums.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[joel009]

Thanks, already posted there.

I am not too familiar with stored procedures. Can one be st up to return a booleen value?

 

[SantaMufasa]

Yes, Joel, you can have a user-defined procedure or function return a boolean expression to a PL/SQL block, but you may not return a boolean to a SQL invocation of a function. I'll illustrate all of that (and the work-around to the SQL), below:

SQL> create or replace procedure tomorrow_is_weekend_T_F(dt date, tf_return in out boolean) is
  2  begin
  3      if to_char(dt+1,'DY') in ('SAT','SUN') then tf_return := true;
  4      else tf_return := false;
  5      end if;
  6  end;
  7  /

Procedure created.

SQL> create or replace function next_day_is_weekend (x date) return varchar2 is
  2      TF boolean;
  3  begin
  4      tomorrow_is_weekend_T_F(x,tf);
  5      if tf then return 'true';
  6      elsif not tf then return 'false';
  7      end if;
  8  end;
  9  /

Function created.

SQL> select next_day_is_weekend(sysdate) from dual;
true

1 row selected.

SQL> select next_day_is_weekend(sysdate-2) from dual;
false

1 row selected.

Let us know if you have questions.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[SantaMufasa]

BTW, Joel, your "boolean" question was excellent, but for future reference, since it had no relation to the "Access" question, please post differing issues in separate postings to make it easier for subsequent searchers to find answers to their questions.

Cheers,

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]