Microsoft KB Archive/229757

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

Article ID: 229757

Article Last Modified on 10/17/2003

-

APPLIES TO


 * Microsoft OLE DB Provider for Oracle Server 1.0
 * Microsoft OLE DB Provider for Oracle Server 1.0

-



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.



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= ;Persist Security Info=False;password=demo" 'Uncomment the following line to make this work. '.ConnectionString = "Provider=MSDASQL;User ID=Demo;Data Source= ;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

Additional query words: oracle procedure

Keywords: kbbug kbfix kboracle kbprovider kbmdac250fix kbmdacnosweep KB229757

-

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

© Microsoft Corporation. All rights reserved.