PL/SQL-Functions called with ADO return random junk

I'm trying to call Oracle functions from a VBA-Application using ADO over ODBC. I've done this a couple of times in previous projects and don't remember any serious problems. This time however I ran into something that drives me nuts...

I am calling several PL/SQL-Functions that return VarChar2-Data. The first time I call any function, everything is alright. However the second (and subsequent times) I call that function and the return value is shorter than the previous value, some random junk and fragments of the previous value are appended to the correct result.

Example:
1st call:
 Expected Result: 'AAAAAAAAAAAA'
 Actual Result: 'AAAAAAAAAAAA'
correct!

2nd call:
 Expected Result: 'BBB'
 Actual Result: 'BBB  AAAA'
what is this?!

If any ORA-Error occurs with that connection, fragments of the oracle error message are appended to the results of the function.

If I call the function from a PL/SQL-Block or a select-statement the results of the function are correct.

I can reproduce this behavior with the most basic sample code:

CREATE OR REPLACE FUNCTION Echo (

    input VARCHAR2)
  RETURN VARCHAR2
IS
BEGIN
  RETURN input;
END;

Public Function testEcho(ByVal strInput As String) As String

    Dim cn As ADODB.Connection
    Dim cmd As ADODB.Command
    Set cn = New ADODB.Connection
    Set cmd = New ADODB.Command     

    cn.Open "DSN=MyDSNName", "MyUser", "MyPwd"     

    Set cmd.ActiveConnection = cn     

    cmd.CommandType = adCmdStoredProc
    cmd.CommandText = "MySchema.Echo"     

    cmd.Parameters.Append cmd.CreateParameter("p0", adVarChar, adParamReturnValue, 255)

    cmd.Parameters.Append cmd.CreateParameter("p1", adVarChar, adParamInput, Len(strInput), strInput)

    cmd.Execute     

    testEcho = cmd.Parameters("p0").Value

End Function

My environment is as follows:

Oracle Server: 10.2.0.4 Patch 19
Oracle Client: 8.1.7.0.0
Oracle ODBC Driver: 8.01.07.00
ADO: 2.1 (tried 2.8 as well)

Any ideas what is going on? - Thanks a lot!

Regards
Philipp Received on Tue Aug 25 2009 - 17:35:07 CDT