Re: PL/SQL-Functions called with ADO return random junk

From: Charles Hooper <>
Date: Tue, 25 Aug 2009 17:09:47 -0700 (PDT)
Message-ID: <>

On Aug 25, 6:35 pm, Philipp Stiefel <> wrote:
> 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.
> 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:

>     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: Patch 19
> Oracle Client:
> Oracle ODBC Driver:
> ADO: 2.1 (tried 2.8 as well)
> Any ideas what is going on? - Thanks a lot!
> Regards
> Philipp

I tested your code with Oracle and the client using Microsoft Excel 2007. I did not have unexpected results. Try the following temporary changes in the function

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

    cmd.Parameters.Append cmd.CreateParameter("p1", adVarChar, adParamInput, 255, "AAAAAAAAAAA")


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

    cmd("p1") = "BBB"

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

There is a chance that the older Oracle client is returning a null terminated string - in other words, the client application is supposed to search the returned value for Chr(0), and retain only what is to the left of the Chr(0) (this is a standard for several Windows API calls which return strings):
If Instr(cmd.Parameters("p0").Value, Chr(0)) > 0 Then

    testEcho = Left(cmd.Parameters("p0").Value, Instr(cmd.Parameters ("p0").Value, Chr(0)) - 1)

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

Another possibility is that the String variable to which the value is to be returned is defined as a fixed length string, but when that is the case the remaining characters should have been padded with " " rather than pre-existing data.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Tue Aug 25 2009 - 19:09:47 CDT

Original text of this message