You are currently offline, waiting for your internet to reconnect

PRB: Cannot Use Boolean Parameter with Oracle

This article was previously published under Q306530
Retired KB Content Disclaimer
This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.
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:
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.
MORE INFORMATION

Steps to Reproduce Behavior

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.
  1. 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; 					
  2. 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

  1. Create a new Standard EXE project in Visual Basic. Form1 is created by default.
  2. From the Projects menu, click References, and then select the Microsoft ActiveX Data Objects 2.x check box.
  3. Add two command buttons to Form1.
  4. 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 = "Provider=MSDAORA;Data Source=ORACLE816;User ID=User;Password=Password"   Set Cn = New ADODB.Connection   Cn.Open strConnect   Set Cmd = New ADODB.Command   With Cmd      .ActiveConnection = Cn      .CommandText = "TestParam"      .CommandType = adCmdStoredProc      .Parameters.Append .CreateParameter("RET_Result", adBoolean, adParamOutput, 1, 0)      .Execute      Debug.Print .Parameters.Item(0)   End WithEnd SubPrivate 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 = "Provider=MSDAORA;Data Source=ORACLE816;User ID=User;Password=Password"   Set Cn = New ADODB.Connection   Cn.Open strConnect   Set Cmd = New ADODB.Command   With Cmd      .ActiveConnection = Cn      .CommandText = "TestParam1"      .CommandType = adCmdStoredProc      .Parameters.Append .CreateParameter("RET_Result", adVarChar, adParamOutput, 1, 0)      .Execute      Debug.Print .Parameters.Item(0)   End WithEnd Sub					
  5. Ensure that you change the arguments of the above connection strings so that they point to your Oracle database.
  6. Run the code, and click Command1. You receive the above-mentioned error message.
  7. Run the application a second time, and then click Command2. This returns the value of True to the Immediate window.
Logical Boolean Bolean scaler
Properties

Article ID: 306530 - Last Review: 04/05/2006 07:03:50 - Revision: 3.0

  • 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
  • kbdatabase kbdriver kboracle kbprb kbprovider KB306530
Feedback
t> t>"); /ms.js" '="">