Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Calling oracle function from Access passthrough query does not return list

oatsMar 21 2009 — edited Jun 22 2011
Thanks 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;
This post has been answered by Satyaki_De on Mar 23 2009
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jul 20 2011
Added on Mar 21 2009
18 comments
4,307 views