FIX: Oracle OLEDB Provider Returns Err: "ORA06502: PL/SQL..." (229757)


The information in this article applies to:

  • Microsoft OLE DB Provider for Oracle 2.1
  • Microsoft OLE DB Provider for Oracle 2.5
This article was previously published under Q229757

SYMPTOMS

When using the Microsoft Oracle OLEDB provider it is possible that you may receive the following error message:
ORA-06502: PL/SQL: numeric or value error ORA-06512: at "TNT.PACK_TEST", line 19 ORA-06512: at line 1
This error message can occur when you are attempting to return a character string of more than 4001 characters from a call to an Oracle stored procedure.

RESOLUTION

To work around this problem, please use the Microsoft ODBC for Oracle driver instead of the OLEDB provider.

STATUS

Microsoft has confirmed that this is a bug in the Microsoft products that are listed at the beginning of this article. This problem was corrected in the MS OLEDB Provider for Oracle found in MDAC 2.6 or later.

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, 5000, String(4002, "c"))  '<<4001 works , 4002 fails
        .Parameters.Append .CreateParameter("outParam", adLongVarChar, adParamOutput, 5000, "")
        .Execute  '<<fails here with the Oracle OLEDB provider
           
    End With
    Debug.Print i & "   Output Parameter: " & Len(cmd(1).Value)

End Sub
Modification Type:MajorLast Reviewed:10/17/2003
Keywords:kbbug kbfix kbMDAC250fix kbMDACNoSweep kbOracle kbProvider KB229757
©2003 Microsoft Corporation. All rights reserved.