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

ID: Q229757

The information in this article applies to:


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.


There are currently two workarounds to this problem as follows:


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


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
   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
   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 

Last Reviewed: May 18, 1999