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

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: Tue, 25 Aug 2009 17:09:47 -0700 (PDT)
Message-ID: <91924039-4e64-4ec2-88ee-0a68e1b8fb96_at_e18g2000vbe.googlegroups.com>

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

(snip)
>
>     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

I tested your code with Oracle 10.2.0.4 and the 11.1.0.6 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")

    cmd.Execute

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

    cmd("p1") = "BBB"
    cmd.Execute

    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)
Else

    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