PL/SQL-Functions called with ADO return random junk

From: Philipp Stiefel <>
Date: Wed, 26 Aug 2009 00:35:07 +0200
Message-ID: <>

Hi Everyone,

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.

1st call:
 Expected Result: 'AAAAAAAAAAAA'
 Actual Result: 'AAAAAAAAAAAA'

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:


    input VARCHAR2)
  RETURN input;

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)


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

End Function

My environment is as follows:

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

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

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

Original text of this message