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

From: joel garry <joel-garry_at_home.com>
Date: Tue, 25 Aug 2009 17:02:21 -0700 (PDT)
Message-ID: <0d06636b-5771-47c4-82e5-4dcc0f26be77_at_m7g2000prd.googlegroups.com>

On Aug 25, 3: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:
>
> -------------------------------------------------------
>
> 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

I think you need the 8.1.7.4 patch, but support ended a couple of years ago, so unless you qualify for lifetime support, good luck. And yes, I do mean that sarcastically. Why on earth are you using 8?

jg

--
_at_home.com is bogus.
"Good testing is like a good bikini. You strive for the best effect
with minimal coverage." - Omri Lapidot.
Actually, I think it should be "Good testing is not like a good
bikini.  You want maximal coverage."

Received on Tue Aug 25 2009 - 19:02:21 CDT