Microsoft KB Archive/306530

= PRB: Cannot Use Boolean Parameter with Oracle =

Article ID: 306530

Article Last Modified on 4/5/2006

-

APPLIES TO


 * Microsoft Open Database Connectivity 2.5
 * Microsoft Open Database Connectivity 2.5
 * Microsoft Open Database Connectivity 2.5
 * Microsoft Open Database Connectivity 2.5
 * Microsoft OLE DB Provider for Oracle Server 1.0
 * Microsoft OLE DB Provider for Oracle Server 1.0
 * Microsoft OLE DB Provider for Oracle Server 1.0
 * Microsoft OLE DB Provider for Oracle Server 1.0
 * Microsoft OLE DB Provider for Oracle Server 1.0

-



This article was previously published under Q306530



SYMPTOMS
If you try to use the BOOLEAN data type as an input or output parameter type with an Oracle stored procedure, you may receive the following error message:

Wrong number or types of arguments in call to 'SP_Name'

where 'SP_Name' is the name of the store procedure or function.



CAUSE
You cannot use the BOOLEAN data type as an input or output parameter type with an Oracle stored procedure. BOOLEAN is a PL/SQL data type and cannot be passed into or out of the Microsoft ActiveX Data Objects (ADO) Parameters collection.



RESOLUTION
To work around this problem, use a data type that is supported (such as varchar). For a list of supported data types, refer to the online help for Microsoft ODBC Driver for Oracle and OLE DB Provider for Oracle, which are available with the Windows Platform Software Development Kit (SDK). You can download the Windows Platform SDK from the following Microsoft Web site:

http://msdn.microsoft.com



STATUS
This behavior is by design. The Oracle BOOLEAN data type is a PL/SQL data type and not an Oracle Database data type. The Microsoft Oracle Driver and Provider only support database data types.



Create the Stored Procedures
To reproduce the problem, create a stored procedure with an output parameter of type BOOLEAN. You can also reproduce this problem if you use a stored procedure with an input parameter of type BOOLEAN. In addition, you can use the solution that follows to resolve this problem if the input parameter has a type BOOLEAN.   Use the PL/SQL data type of BOOLEAN to create the first stored procedure as follows: CREATE OR REPLACE PROCEDURE TESTPARAM  (                                     RET_Result OUT BOOLEAN                        ) AS BEGIN RET_Result := True; END;   Use a varchar data type to create the second stored procedure as follows: CREATE OR REPLACE PROCEDURE TESTPARAM1 (                                     RET_Result OUT VARCHAR                        ) AS BEGIN RET_Result := 'True'; END; 

Create the Visual Basic Application
 Create a new Standard EXE project in Visual Basic. Form1 is created by default. From the Projects menu, click References, and then select the Microsoft ActiveX Data Objects 2.x check box.</li> Add two command buttons to Form1.</li>  Open the code window that is associated with Form1, and paste the following code in the code window: Private Sub Command1_Click Dim Cn As ADODB.Connection Dim Cmd As ADODB.Command Dim strConnect As String

'Change the connection string to match your Oracle server name 'and logon credentials. strConnect = &quot;Provider=MSDAORA;Data Source=ORACLE816;User ID=User;Password=Password&quot; Set Cn = New ADODB.Connection Cn.Open strConnect Set Cmd = New ADODB.Command With Cmd .ActiveConnection = Cn     .CommandText = &quot;TestParam&quot; .CommandType = adCmdStoredProc .Parameters.Append .CreateParameter(&quot;RET_Result&quot;, adBoolean, adParamOutput, 1, 0) .Execute Debug.Print .Parameters.Item(0) End With End Sub

Private Sub Command2_Click Dim Cn As ADODB.Connection Dim Cmd As ADODB.Command Dim strConnect As String 'Change the connection string to match your Oracle server name 'and logon credentials. strConnect = &quot;Provider=MSDAORA;Data Source=ORACLE816;User ID=User;Password=Password&quot; Set Cn = New ADODB.Connection Cn.Open strConnect Set Cmd = New ADODB.Command With Cmd .ActiveConnection = Cn     .CommandText = &quot;TestParam1&quot; .CommandType = adCmdStoredProc .Parameters.Append .CreateParameter(&quot;RET_Result&quot;, adVarChar, adParamOutput, 1, 0) .Execute Debug.Print .Parameters.Item(0) End With End Sub </li> Ensure that you change the arguments of the above connection strings so that they point to your Oracle database.</li> Run the code, and click Command1. You receive the above-mentioned error message.</li> Run the application a second time, and then click Command2. This returns the value of True to the Immediate window.</li></ol>

Additional query words: Logical Boolean Bolean scaler

Keywords: kboracle kbprovider kbdatabase kbprb kbdriver KB306530

-

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

© Microsoft Corporation. All rights reserved.