Microsoft KB Archive/328514

= FIX: Binding an output parameter of an Oracle stored procedure to adBSTR results in an incorrect value =

Article ID: 328514

Article Last Modified on 11/15/2007

-

APPLIES TO


 * Microsoft OLE DB Provider for Oracle Server 1.0
 * Microsoft ActiveX Data Objects 2.7
 * Microsoft Data Access Components 2.8

-



This article was previously published under Q328514





SYMPTOMS
When you bind a varchar2 type output parameter of an Oracle stored procedure to adBSTR in a Microsoft ADO application, the value that Microsoft OLE DB provider for Oracle (MSDAORA) returns is incorrect. In a Microsoft Visual Basic application the output parameter is &quot;????&quot;, and in a Microsoft Visual C++ application, the output parameter is a truncated ANSI value.



CAUSE
This occurs because of a bug in MSDAORA. Although the output parameter data type is marked as DBTYPE_WSTR internally, the data itself is not converted to Unicode before it is sent to the data conversion layer.



RESOLUTION
A supported hotfix is now available from Microsoft. However, this hotfix is intended to correct only the problem that is described in this article. Apply this hotfix only to systems that are experiencing this specific problem. This hotfix might receive additional testing. Therefore, if you are not severely affected by this problem, we recommend that you wait for the next service pack that contains this hotfix.

To resolve this problem, submit a request to Microsoft Online Customer Services to obtain the hotfix. To submit an online request to obtain the hotfix, visit the following Microsoft Web site:

http://go.microsoft.com/?linkid=6294451

Note If additional issues occur or any troubleshooting is required, you might have to create a separate service request. The usual support costs will apply to additional support questions and issues that do not qualify for this specific hotfix. To create a separate service request, visit the following Microsoft Web site:

http://support.microsoft.com/contactus/?ws=support

The English version of this hotfix has the file attributes (or later) that are listed in the following table. The dates and times for these files are listed in coordinated universal time (UTC). When you view the file information, it is converted to local time. To find the difference between UTC and local time, use the Time Zone tab in the Date and Time tool in Control Panel.

MDAC 2.7
   Date           Version           Size              File name 30-Aug-2002   2.70.9001.20    221,184 bytes     Msdaora.dll The English version of this hotfix has the file attributes (or later) that are listed in the following table. The dates and times for these files are listed in coordinated universal time (UTC). When you view the file information, it is converted to local time. To find the difference between UTC and local time, use the Time Zone tab in the Date and Time tool in Control Panel.

MDAC 2.7 Service Pack 1 (SP1)
   Date           Version           Size              File name 06-12-2003   2.71.9031.45    221,184 bytes     Msdaora.dll The English version of this hotfix has the file attributes (or later) that are listed in the following table. The dates and times for these files are listed in coordinated universal time (UTC). When you view the file information, it is converted to local time. To find the difference between UTC and local time, use the Time Zone tab in the Date and Time tool in Control Panel.

MDAC 2.8
   Date           Version           Size              File name 27-Feb-2004 18:29  2.80.1035.0       225,280  Msdaora.dll 27-Feb-2004 18:29  2000.85.1035.0     24,576  Odbcbcp.dll 27-Feb-2004 18:28  2000.85.1035.0    401,408  Sqlsrv32.dll

Note For a list of all the hotfixes available for MDAC 2.8, click the following article number to view the article in the Microsoft Knowledge Base:

839801 FIX: Hotfixes are available for MDAC 2.8

WORKAROUND
To work around this problem, bind the output parameter of varchar2 to adVarchar instead of to adBSTR.



STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the &quot;Applies to&quot; section.



Steps to reproduce the behavior
  Run the following script to create a stored procedure that is named sp_param: CREATE OR REPLACE PROCEDURE sp_param (IN_PARAM IN VARCHAR2,RET_Result OUT VARCHAR2) AS BEGIN RET_Result := IN_PARAM; END;  In Visual Basic 6.0, create a Standard EXE project. Add a reference to the Microsoft ActiveX Data Objects library. To do this, follow these steps: <ol style="list-style-type: lower-alpha;"> On the Project menu, click Add Reference.</li> Click the COM tab, click to select Microsoft ActiveX Data Objects 2.7 Library, click Select, and then click OK.</li></ol> </li> Add a CommandButton control to the main Form.</li>  Double-click the Command button. Paste the following code in the Command1_Click event handler: Private Sub Command1_Click Dim oConn As New ADODB.Connection Dim oCmd As New ADODB.Command Dim oRsReport As ADODB.Recordset oConn.ConnectionString = &quot;Provider=MSDAORA;Data Source=myOraServer;User Id=scott;Password=tiger;&quot; oConn.Open oCmd.ActiveConnection = oConn oCmd.CommandText = &quot;sp_param&quot; oCmd.CommandType = adCmdStoredProc With oCmd .Parameters.Append .CreateParameter(&quot;in_param&quot;, adVarChar, adParamInput, 50, &quot;Testing 123&quot;) .Parameters.Append .CreateParameter(&quot;ret_result&quot;, adBSTR, adParamOutput, 50) End With Set oRsReport = oCmd.Execute Debug.Print oCmd.Parameters.Item(&quot;ret_result&quot;) oRsReport.Close oConn.Close Set oRsReport = Nothing Set oConn = Nothing Set oCmd = Nothing End Sub </li> Change the connection string as appropriate for your environment. Save and then run the project.</li></ol>

Additional query words: MSDAORA output parameter adBSTR oracle stored procedure

Keywords: kbhotfixserver kbqfe kbmdac280presp1fix kbbug kbfix kboracle kbqfe KB328514

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.