Oracle Database Discussions

Hi,

I hope somebody can help. I have discovered what I think is possibly a bug with the Oracle ODBC driver.

I parse and fetch all the rows from a select statment which returns around 220,000 rows. This works absolutely fine unless one of the fields I have selected returns the result of a user-defined function which has a return type of varchar2.

eg:

where "my_function" is declared thus:

create or replace
function my_function( phrase in varchar2 )
RETURN VARCHAR2
IS
.....
END;

then if I run the following query via ODBC:

select my_function( field_a )
from my_table

I find that the amount of memory used by my program absolutely balloons until it runs out of memory (hits the windows 2GB limit).

My investigations have shown that this is because the ODBC driver is unable to determine the size of the data returned by the function (in this never more than 32 characters) and so appears to allocate enough space for the maximum length of a varchar2.

I can prove this is the case because I do not see the same problems if I change nothing else in my program except the select statement to:

select cast( my_function( field_a ) as varchar2(32) )
from my_table

I have tried reducing the size of the fetch buffer size in the ODBC driver configuration but this make no discernable difference.
Is this a known issue? And if so, other than using the cast operator is there a work-around?

Thanks

Tony