BUG: Oracle OLEDB Provider Returns Err: "ORA06502: PL/SQL..."

ID: Q229757

The information in this article applies to:

Microsoft OLE DB Provider for Oracle, version 2.1

SYMPTOMS

When using the Microsoft Oracle OLEDB provider it is possible that you may receive the following error message:

-2147467259 (80004005) "ORA06502: PL/SQL: numeric or value error"

This error message can occur when you are attempting to return a character string of more than 2001 characters from a call to an Oracle stored procedure.

RESOLUTION

There are currently two workarounds to this problem as follows:

Use the Microsoft OLEDB Provider for Oracle that shipped with Microsoft Data Access Component (MDAC) version 2.0.

-or-

Use the Microsoft ODBC for Oracle driver instead of the OLEDB provider.

STATUS

Microsoft has confirmed this to be a bug in the Microsoft products listed at the beginning of this article.

MORE INFORMATION

Steps to Reproduce Behavior

You can reproduce this problem by running the code that follows. You will need to modify the connection string in the code to point to your Oracle database.


   Private Sub Command1_Click()
    
   Dim cmd As ADODB.Command
   Set cmd = New ADODB.Command
    
   Dim cn As ADODB.Connection
   Set cn = New ADODB.Connection
   With cn
      .ConnectionString = "Provider=MSDAORA.1;User ID=Demo;Data Source=<your oracle server alias>;Persist Security Info=False;password=demo"
      'Uncomment the following line to make this work.
      '.ConnectionString = "Provider=MSDASQL;User ID=Demo;Data Source=<your datasource name>;Persist Security Info=False;password=demo"
       .ConnectionTimeout = 10
       .CursorLocation = adUseClient
       .Open
   End With
    
   'Create the stored procedure for testing.
   Set cmd.ActiveConnection = cn
   With cmd
       .CommandType = adCmdText
       .CommandText = "Create or Replace Procedure TEST " & _
                      " (inParam in  varchar2," & _
                      " outParam out varchar2)" & _
                      " is " & _
                      " begin " & _
                      " outParam:= inParam;" & _
                      " end ;"

   End With
   cmd.Execute
    
   Debug.Print "Stored Proc Created"
    
   Set cmd = Nothing
   Set cmd = New ADODB.Command
   With cmd
        Set .ActiveConnection = cn
        .CommandType = adCmdStoredProc
        .CommandText = "TEST"
        .Parameters.Append .CreateParameter("inParam", adLongVarChar, adParamInput, 4000, String(2002, "c"))  '<<2001 works , 2002 fails
        .Parameters.Append .CreateParameter("outParam", adLongVarChar, adParamOutput, 4000, "")
        .Execute  '<<fails here with the Oracle OLEDB provider
           
    End With
    Debug.Print i & "   Output Parameter: " & Len(cmd(1).Value)

End Sub

Additional query words: oracle procedure

Keywords : kbMDAC kbOracle kbProvider kbVS kbGrpVBDB kbGrpMDAC
Version : WINDOWS:2.1
Platform : WINDOWS
Issue type : kbbug