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

On Aug 25, 3:35*pm, Philipp Stiefel <p... (AT) codekabinett (DOT) de> wrote:
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
--
@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."

On Aug 25, 6:35*pm, Philipp Stiefel <p... (AT) codekabinett (DOT) de> wrote:
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.

"Philipp Stiefel" <phil (AT) codekabinett (DOT) de> a écrit dans le message de news: h71vff.5eo.1 (AT) pluto (DOT) ksw.codekabinett.com...
Client 8.1.7 - server 10.2.0 is a not supported combination.
See Metalink note 207303.1

Regards
Michel

joel garry <joel-garry (AT) home (DOT) com> wrote:

The customer is using other third party applications that are
still running with an Oracle 8.1-Server. During the preparation
of the migration of our server to 10g we came to the conclusion,
that the only way we can migrate our server to 10g without
affecting any other application is to stick with the 8.1-Client.

We are running the 10.2-Server/8.1-Client-combination for almost
two years now and it is going very well so far. - However, until
now we were still using DAO/ODBC as data access method. I'm just
in the process of evaluating the migration to ADO.


I'm on site with the customer tomorrow and will discuss our
possibilities to upgrade the clients to Oracle 10. If that is
not an option, I will try the 8.1.7.4-Client. - Supportwise that
won't make matters any worse than they already are with our
current configuration. ;-)


Thanks to all of you for the advice!

Regards
Philipp

Charles Hooper <hooperc2000 (AT) yahoo (DOT) com> wrote:
Thank you! So it looks like our old 8.1-Client is to blame.


I don't think so. I've done this before with Oracle 7.x-
and 8.x-Clients and that has never been the case.

Regards
Philipp