Calling oracle function from Access passthrough query does not return list
oatsMar 21 2009 — edited Jun 22 2011Thanks to the help I received in an earlier post I've created an oracle 10g function that returns a list after executing the sql it contains. It works in oracle, using sql developer.
I need to have the list that it returns show up in MS Access via a passthrough query. It's not working so far. The connection string etc is ok, I'm able to use passthrough queries to run sql strings successfully. But when I try to call the function via the Access passthrough query at first nothing seems to happen (ie no list appears) and if I try to run it again, there is a odbc error 'call in progress. Current operation canceled'. There are only the three records in the table. I'm missing something, can anyone spot it?
The passthrough query looks like this
select * from fn_testvalues from dual
Again that runs in oracle.
To create the testing table and 2 functions see below.
CREATE TABLE t_values (MyValue varchar2(10));
Table created
INSERT INTO t_values (
SELECT 'Merced' c1 FROM dual UNION ALL
SELECT 'Pixie' FROM dual UNION ALL
SELECT '452' FROM dual);
3 rows inserted
CREATE OR REPLACE FUNCTION fn_isnum(p_val VARCHAR2) RETURN NUMBER IS
n_val NUMBER;
BEGIN
n_val := to_number(p_val);
RETURN 1;
EXCEPTION
WHEN OTHERS THEN
RETURN 0;
END;
/
Function created
testing the table:
SELECT val, fn_isnum(MyValue ) isnum
FROM t_values;
VAL ISNUM
---------- ----------
Merced 0
Pixie 0
452 1
Now the function that is called in the passthrough query:
create or replace function fn_testvalues
return sys_refcursor is
rc sys_refcursor;
begin
open rc for
Select t_values.*, fn_isnum(MyValue) IsNum from t_values;
return(rc);
end fn_testvalues;