Microsoft KB Archive/259941

= FIX: 50 or More Output Parameters Produces &quot;Unspecified Error&quot; with the OLE DB Provider for Oracle =

Article ID: 259941

Article Last Modified on 10/17/2003

-

APPLIES TO


 * Microsoft Data Access Components 2.5
 * Microsoft Data Access Components 2.5 Service Pack 1
 * 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

-



This article was previously published under Q259941



SYMPTOMS
Under the following conditions:
 * you are using the Microsoft OLE DB Provider for Oracle to connect to Oracle 8i database -and-


 * you are running a stored procedure inside of a package that returns 50 or more output parameters

an &quot;Unspecified Error&quot; is generated by the Microsoft OLE DB Provider for Oracle.

The error does not occur when the stored procedure returns 49 or fewer output parameters. It also does not occur if 50 or more output parameters are returned but you are using the Microsoft OLE DB Provider for ODBC.



CAUSE
Because of changes in Oracle between version 7.3/8.0 and version 8i, the Microsoft OLE DB Provider for Oracle does not communicate with 8i in the same manner it did with 7.3/8.0.



RESOLUTION
To temporarily work around this problem, you can use the Microsoft OLE DB Provider for ODBC.



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 Microsoft OLE DB Provider for Oracle included in Microsoft Data Access Components (MDAC) 2.6.



Steps to Reproduce Behavior
In Oracle's SQL*Plus utility, execute the following:   Create a table: DROP TABLE storedprocedureTable;

CREATE TABLE storedprocedureTable ( ssn    NUMBER(9) PRIMARY KEY, fname   VARCHAR2(1000), lname   VARCHAR2(2000), ssn1     NUMBER(9), fname1   VARCHAR2(1000), lname1   VARCHAR2(2000), ssn2     NUMBER(9), fname2   VARCHAR2(1000), lname2   VARCHAR2(2000), ssn3     NUMBER(9), fname3   VARCHAR2(1000), lname3   VARCHAR2(2000), ssn4     NUMBER(9), fname4   VARCHAR2(1000), lname4   VARCHAR2(2000), ssn5     NUMBER(9), fname5   VARCHAR2(1000), lname5   VARCHAR2(2000), ssn6     NUMBER(9), fname6   VARCHAR2(1000), lname6   VARCHAR2(2000), ssn7     NUMBER(9), fname7   VARCHAR2(1000), lname7   VARCHAR2(2000), ssn8     NUMBER(9), fname8   VARCHAR2(1000), lname8   VARCHAR2(2000), ssn9     NUMBER(9), fname9   VARCHAR2(1000), lname9   VARCHAR2(2000), ssn10     NUMBER(9), fname10   VARCHAR2(1000), lname10   VARCHAR2(2000), ssn11     NUMBER(9), fname11   VARCHAR2(1000), lname11   VARCHAR2(2000), ssn12     NUMBER(9), fname12   VARCHAR2(1000), lname12  VARCHAR2(2000), ssn13    NUMBER(9), fname13  VARCHAR2(1000), lname13  VARCHAR2(2000), ssn14    NUMBER(9), fname14  VARCHAR2(1000), lname14  VARCHAR2(2000), ssn15    NUMBER(9), fname15  VARCHAR2(1000), lname15  VARCHAR2(2000), ssn16    NUMBER(9), fname16  VARCHAR2(1000) );                     Insert some values into the table:  INSERT INTO storedprocedureTable (ssn,fname,lname)  VALUES(555662222,'Sam','Goodwin');

INSERT INTO storedprocedureTable (ssn,fname,lname) VALUES(555882222,'Kent','Clark');

INSERT INTO storedprocedureTable (ssn,fname,lname) VALUES(666223333,'Jane','Doe');

INSERT INTO storedprocedureTable (ssn,fname,lname) VALUES(666223331, NULL, 'Doe');   Create a package for 50 output parameters: CREATE OR REPLACE PACKAGE pk_storedProcedure AS TYPE tssn is TABLE of NUMBER(10) INDEX BY BINARY_INTEGER; TYPE tfname is TABLE of VARCHAR2(1000) INDEX BY BINARY_INTEGER; TYPE tlname is TABLE of VARCHAR2(2000) INDEX BY BINARY_INTEGER;

TYPE tssn1 is TABLE of NUMBER(10) INDEX BY BINARY_INTEGER; TYPE tfname1 is TABLE of VARCHAR2(1000) INDEX BY BINARY_INTEGER; TYPE tlname1 is TABLE of VARCHAR2(2000) INDEX BY BINARY_INTEGER;

TYPE tssn2 is TABLE of NUMBER(10) INDEX BY BINARY_INTEGER; TYPE tfname2 is TABLE of VARCHAR2(1000) INDEX BY BINARY_INTEGER; TYPE tlname2 is TABLE of VARCHAR2(2000) INDEX BY BINARY_INTEGER;

TYPE tssn3 is TABLE of NUMBER(10) INDEX BY BINARY_INTEGER; TYPE tfname3 is TABLE of VARCHAR2(1000) INDEX BY BINARY_INTEGER; TYPE tlname3 is TABLE of VARCHAR2(2000) INDEX BY BINARY_INTEGER;

TYPE tssn4 is TABLE of NUMBER(10) INDEX BY BINARY_INTEGER; TYPE tfname4 is TABLE of VARCHAR2(1000) INDEX BY BINARY_INTEGER; TYPE tlname4 is TABLE of VARCHAR2(2000) INDEX BY BINARY_INTEGER;

TYPE tssn5 is TABLE of NUMBER(10) INDEX BY BINARY_INTEGER; TYPE tfname5 is TABLE of VARCHAR2(1000) INDEX BY BINARY_INTEGER; TYPE tlname5 is TABLE of VARCHAR2(2000) INDEX BY BINARY_INTEGER;

TYPE tssn6 is TABLE of NUMBER(10) INDEX BY BINARY_INTEGER; TYPE tfname6 is TABLE of VARCHAR2(1000) INDEX BY BINARY_INTEGER; TYPE tlname6 is TABLE of VARCHAR2(2000) INDEX BY BINARY_INTEGER;

TYPE tssn7 is TABLE of NUMBER(10) INDEX BY BINARY_INTEGER; TYPE tfname7 is TABLE of VARCHAR2(1000) INDEX BY BINARY_INTEGER; TYPE tlname7 is TABLE of VARCHAR2(2000) INDEX BY BINARY_INTEGER;

TYPE tssn8 is TABLE of NUMBER(10) INDEX BY BINARY_INTEGER; TYPE tfname8 is TABLE of VARCHAR2(1000) INDEX BY BINARY_INTEGER; TYPE tlname8 is TABLE of VARCHAR2(2000) INDEX BY BINARY_INTEGER;

TYPE tssn9 is TABLE of NUMBER(10) INDEX BY BINARY_INTEGER; TYPE tfname9 is TABLE of VARCHAR2(1000) INDEX BY BINARY_INTEGER; TYPE tlname9 is TABLE of VARCHAR2(2000) INDEX BY BINARY_INTEGER;

TYPE tssn10 is TABLE of NUMBER(10) INDEX BY BINARY_INTEGER; TYPE tfname10 is TABLE of VARCHAR2(1000) INDEX BY BINARY_INTEGER; TYPE tlname10 is TABLE of VARCHAR2(2000) INDEX BY BINARY_INTEGER;

TYPE tssn11 is TABLE of NUMBER(10) INDEX BY BINARY_INTEGER; TYPE tfname11 is TABLE of VARCHAR2(1000) INDEX BY BINARY_INTEGER; TYPE tlname11 is TABLE of VARCHAR2(2000) INDEX BY BINARY_INTEGER;

TYPE tssn12 is TABLE of NUMBER(10) INDEX BY BINARY_INTEGER; TYPE tfname12 is TABLE of VARCHAR2(1000) INDEX BY BINARY_INTEGER; TYPE tlname12 is TABLE of VARCHAR2(2000) INDEX BY BINARY_INTEGER;

TYPE tssn13 is TABLE of NUMBER(10) INDEX BY BINARY_INTEGER; TYPE tfname13 is TABLE of VARCHAR2(1000) INDEX BY BINARY_INTEGER; TYPE tlname13 is TABLE of VARCHAR2(2000) INDEX BY BINARY_INTEGER;

TYPE tssn14 is TABLE of NUMBER(10) INDEX BY BINARY_INTEGER; TYPE tfname14 is TABLE of VARCHAR2(1000) INDEX BY BINARY_INTEGER; TYPE tlname14 is TABLE of VARCHAR2(2000) INDEX BY BINARY_INTEGER;

TYPE tssn15 is TABLE of NUMBER(10) INDEX BY BINARY_INTEGER; TYPE tfname15 is TABLE of VARCHAR2(1000) INDEX BY BINARY_INTEGER; TYPE tlname15 is TABLE of VARCHAR2(2000) INDEX BY BINARY_INTEGER;

TYPE tssn16 is TABLE of NUMBER(10) INDEX BY BINARY_INTEGER; TYPE tfname16 is TABLE of VARCHAR2(1000) INDEX BY BINARY_INTEGER;

PROCEDURE sp_storedProcedure (ifname in varchar2,       ssn    OUT     tssn,         fname   OUT     tfname,         lname   OUT     tlname,        ssn1    OUT     tssn1,         fname1  OUT     tfname1,         lname1  OUT     tlname1,        ssn2    OUT     tssn2,         fname2  OUT     tfname2,         lname2  OUT     tlname2,        ssn3    OUT     tssn3,         fname3  OUT     tfname3,         lname3  OUT     tlname3,        ssn4    OUT     tssn4,         fname4  OUT     tfname4,         lname4  OUT     tlname4,        ssn5    OUT     tssn5,         fname5  OUT     tfname5,         lname5  OUT     tlname5,        ssn6    OUT     tssn6,         fname6  OUT     tfname6,         lname6  OUT     tlname6,        ssn7    OUT     tssn7,         fname7  OUT     tfname7,         lname7  OUT     tlname7,        ssn8    OUT     tssn8,         fname8  OUT     tfname8,         lname8  OUT     tlname8,        ssn9    OUT     tssn9, fname9 OUT     tfname9, lname9 OUT     tlname9, ssn10  OUT     tssn10, fname10 OUT    tfname10, lname10 OUT    tlname10, ssn11  OUT     tssn11, fname11 OUT    tfname11, lname11 OUT    tlname11, ssn12  OUT     tssn12, fname12 OUT    tfname12, lname12 OUT    tlname12, ssn13  OUT     tssn13, fname13 OUT    tfname13, lname13 OUT    tlname13, ssn14  OUT     tssn14, fname14 OUT    tfname14, lname14 OUT    tlname14, ssn15  OUT     tssn15, fname15 OUT    tfname15, lname15 OUT    tlname15, ssn16  OUT     tssn16, fname16 OUT    tfname16); END pk_storedProcedure; /  NOTE: To create a package for only 49 output parameters, simply eliminate the last item (the following two lines of code) in the create statement above:  TYPE tfname16 is TABLE of VARCHAR2(1000) INDEX BY BINARY_INTEGER; as well as the last parameter (the following line of code) in the stored procedure above:  fname16 OUT     tfname16);   Create the package body with stored procedures for 50 output parameters: CREATE OR REPLACE PACKAGE BODY pk_storedProcedure AS PROCEDURE sp_storedProcedure (ifname in varchar2,       ssn    OUT     tssn,         fname   OUT     tfname,         lname   OUT     tlname,        ssn1    OUT     tssn1,         fname1      OUT     tfname1,         lname1      OUT     tlname1,        ssn2    OUT     tssn2,         fname2      OUT     tfname2,         lname2      OUT     tlname2,        ssn3    OUT     tssn3,         fname3      OUT     tfname3,         lname3      OUT     tlname3,        ssn4    OUT     tssn4,         fname4      OUT     tfname4,         lname4      OUT     tlname4,        ssn5    OUT     tssn5,         fname5      OUT     tfname5,         lname5      OUT     tlname5,        ssn6    OUT     tssn6,         fname6      OUT     tfname6,         lname6      OUT     tlname6,        ssn7    OUT     tssn7,         fname7      OUT     tfname7,         lname7      OUT     tlname7,        ssn8    OUT     tssn8,         fname8      OUT     tfname8, lname8     OUT     tlname8, ssn9   OUT     tssn9, fname9     OUT     tfname9, lname9     OUT     tlname9, ssn10   OUT     tssn10, fname10    OUT     tfname10, lname10    OUT     tlname10, ssn11   OUT     tssn11, fname11    OUT     tfname11, lname11    OUT     tlname11, ssn12   OUT     tssn12, fname12    OUT     tfname12, lname12    OUT     tlname12, ssn13   OUT     tssn13, fname13    OUT     tfname13, lname13    OUT     tlname13, ssn14   OUT     tssn14, fname14    OUT     tfname14, lname14    OUT     tlname14, ssn15   OUT     tssn15, fname15    OUT     tfname15, lname15    OUT     tlname15, ssn16   OUT     tssn16, fname16    OUT     tfname16 ) IS CURSOR person_cur IS     SELECT *    FROM scott.storedprocedureTable         where fname = ifname;     percount NUMBER DEFAULT 1;     BEGIN        FOR singleperson IN person_cur         LOOP            ssn(percount) := singleperson.ssn;             fname(percount) := singleperson.fname;             lname(percount) := singleperson.lname;             ssn1(percount) := singleperson.ssn1;             fname1(percount) := singleperson.fname1;             lname1(percount) := singleperson.lname1;             ssn2(percount) := singleperson.ssn2;             fname2(percount) := singleperson.fname2;             lname2(percount) := singleperson.lname2;             ssn3(percount) := singleperson.ssn3;             fname3(percount) := singleperson.fname3;             lname3(percount) := singleperson.lname3;             ssn4(percount) := singleperson.ssn4;             fname4(percount) := singleperson.fname4; lname4(percount) := singleperson.lname4; ssn5(percount) := singleperson.ssn5; fname5(percount) := singleperson.fname5; lname5(percount) := singleperson.lname5; ssn6(percount) := singleperson.ssn6; fname6(percount) := singleperson.fname6; lname6(percount) := singleperson.lname6; ssn7(percount) := singleperson.ssn7; fname7(percount) := singleperson.fname7; lname7(percount) := singleperson.lname7; ssn8(percount) := singleperson.ssn8; fname8(percount) := singleperson.fname8; lname8(percount) := singleperson.lname8; ssn9(percount) := singleperson.ssn9; fname9(percount) := singleperson.fname9; lname9(percount) := singleperson.lname9; ssn10(percount) := singleperson.ssn10; fname10(percount) := singleperson.fname10; lname10(percount) := singleperson.lname10; ssn11(percount) := singleperson.ssn11; fname11(percount) := singleperson.fname11; lname11(percount) := singleperson.lname11; ssn12(percount) := singleperson.ssn12; fname12(percount) := singleperson.fname12; lname12(percount) := singleperson.lname12; ssn13(percount) := singleperson.ssn13; fname13(percount) := singleperson.fname13; lname13(percount) := singleperson.lname13; ssn14(percount) := singleperson.ssn14; fname14(percount) := singleperson.fname14; lname14(percount) := singleperson.lname14; ssn15(percount) := singleperson.ssn15; fname15(percount) := singleperson.fname15; lname15(percount) := singleperson.lname15; ssn16(percount) := singleperson.ssn16; fname16(percount) := singleperson.fname16; percount := percount + 1; END LOOP; END; END; / NOTE: To create a package for only 49 output parameters, simply eliminate the following two lines from the script above: fname16    OUT     tfname16 ) and  fname16(percount) := singleperson.fname16;                      Start a new Visual Basic Standard EXE Project. Form 1 is created by default.</li> From the Project Menu, under References, select Microsoft ActiveX Data Objects Library 2.5.</li>  Draw the following controls on Form1 and set their Properties as listed here: <pre class="fixed_text">CONTROL               NAME                 CAPTION / OTHER PROPERTIES Command Button       cmdCnOracleProvider     Cn OleDb Provider for Oracle Command Button       cmdCnODBC               Cn Using ODBC Command Button       cmd49Params             Run With 49 Output Parameters Command Button       cmd50Params             Run With 50 Output Parameters Text Box             txtErrorInfo            Set Multi-Line to True                                             Set Scroll Bars to 3-Both </li>  Copy the following code into the General Declarations section of the code window.

NOTE: Put your server name, user ID, and password information in the connection string. Option Explicit Dim cn As New ADODB.Connection Dim rs As New ADODB.Recordset Dim cmd As New ADODB.Command Dim param As New ADODB.Parameter Dim strSQL As String Dim i As Integer Dim StrTmp As String Dim errLoop As Error Dim strError As String Dim Errs1 As Object

Private Sub cmdCnOracleProvider_Click

If cn.State = 1 Then cn.Close Set cn = Nothing End If

cn.CursorLocation = adUseClient

cn.Open &quot;Provider=MSDAORA.1;Password=tiger;User ID=scott;Data Source=dseOracle8i;Persist Security Info=True&quot;

If cn.State = 1 Then MsgBox &quot;OleDb Orcl Connection is Open&quot; End If

End Sub

Private Sub cmdCnODBC_Click

If cn.State = 1 Then cn.Close Set cn = Nothing End If

cn.ConnectionString = &quot;dsn=kilburn;uid=scott;pwd=tiger&quot; cn.Open

If cn.State = 1 Then MsgBox &quot;ODBC Connection is Open&quot; End If

End Sub

Private Sub cmd49Params_Click

'With ssn16 as 49th parameter below, runs without errors:

strSQL = &quot;{call pk_storedProcedure.sp_storedProcedure (?, {resultset 4, ssn, fname, lname,&quot; & _                                    &quot;ssn1, fname1, lname1,&quot; & _                                    &quot;ssn2, fname2, lname2,&quot; & _                                    &quot;ssn3, fname3, lname3,&quot; & _                                    &quot;ssn4, fname4, lname4,&quot; & _                                    &quot;ssn5, fname5, lname5,&quot; & _                                    &quot;ssn6, fname6, lname6,&quot; & _                                    &quot;ssn7, fname7, lname7,&quot; & _                                    &quot;ssn8, fname8, lname8,&quot; & _                                    &quot;ssn9, fname9, lname9,&quot; & _                                    &quot;ssn10, fname10, lname10,&quot; & _                                    &quot;ssn11, fname11, lname11,&quot; & _ &quot;ssn12, fname12, lname12,&quot; & _ &quot;ssn13, fname13, lname13,&quot; & _ &quot;ssn14, fname14, lname14,&quot; & _ &quot;ssn15, fname15, lname15,&quot; & _ &quot;ssn16} ) }&quot; With cmd Set .ActiveConnection = cn   .CommandText = strSQL    .CommandType = adCmdText Set param = .CreateParameter(&quot;sIn&quot;, adBSTR, adParamInput, 50, &quot;Sam&quot;)    .Parameters.Append param End With

Set rs = cmd.Execute Debug.Print rs(1).Value Debug.Print rs(0).Value

MsgBox &quot;Done 49&quot;

End Sub

Private Sub cmd50Params_Click

On Error GoTo MyError

'With 50 Output Parameters below, the 'Run-Time Error '-2147467259(80004005)': Unspecified Error 'occurs - See Text Box for more detailed info

strSQL = &quot;{call pk_storedProcedure.sp_storedProcedure (?, {resultset 4, ssn, fname, lname,&quot; & _                                    &quot;ssn1, fname1, lname1,&quot; & _                                    &quot;ssn2, fname2, lname2,&quot; & _                                    &quot;ssn3, fname3, lname3,&quot; & _                                    &quot;ssn4, fname4, lname4,&quot; & _                                    &quot;ssn5, fname5, lname5,&quot; & _                                    &quot;ssn6, fname6, lname6,&quot; & _                                    &quot;ssn7, fname7, lname7,&quot; & _                                    &quot;ssn8, fname8, lname8,&quot; & _                                    &quot;ssn9, fname9, lname9,&quot; & _                                    &quot;ssn10, fname10, lname10,&quot; & _                                    &quot;ssn11, fname11, lname11,&quot; & _ &quot;ssn12, fname12, lname12,&quot; & _ &quot;ssn13, fname13, lname13,&quot; & _ &quot;ssn14, fname14, lname14,&quot; & _ &quot;ssn15, fname15, lname15,&quot; & _ &quot;ssn16, fname16} ) }&quot;

With cmd Set .ActiveConnection = cn   .CommandText = strSQL .CommandType = adCmdText Set param = .CreateParameter(&quot;sIn&quot;, adBSTR, adParamInput, 50, &quot;Sam&quot;) .Parameters.Append param End With

Set rs = cmd.Execute Debug.Print rs(1).Value Debug.Print rs(0).Value

MsgBox &quot;Done 50&quot;

Exit Sub

MyError:

i = 1

StrTmp = StrTmp & vbCrLf & &quot;VB Error # &quot; & vbCrLf & Str(Err.Number) & vbCrLf StrTmp = StrTmp & vbCrLf & &quot;  Generated by &quot; & vbCrLf & Err.Source &  vbCrLf StrTmp = StrTmp & vbCrLf & &quot;  Description  &quot; & vbCrLf &  Err.Description

' Display Error Information for each Error object. Set Errs1 = cn.Errors For Each errLoop In Errs1 With errLoop StrTmp = StrTmp & vbCrLf & &quot;Error #&quot; & i & &quot;:&quot; StrTmp = StrTmp & vbCrLf & &quot;  ADO Error   #&quot; & .Number StrTmp = StrTmp & vbCrLf & &quot;  Description  &quot; & .Description StrTmp = StrTmp & vbCrLf & &quot;  Source       &quot; & .Source i = i + 1 End With Next

txtErrorInfo.Text = StrTmp

End Sub </li> NOTE: Prior to running the project, be certain to run the appropriate Oracle scripts for the number of output parameters (49 or 50) that you intend to test.

Run the project.</li></ol>

To Test with 50 Output Parameters

 * 1) Click the Cn OleDb Provider for Oracle command button to open a connection using the OLEDB Provider for Oracle.
 * 2) Click the Run With 50 Output Parameters command button to run the stored procedure which returns 50 output parameters.

The &quot;Unspecified Error&quot; message will appear.
 * 1) Click the Cn Using ODBC command button to open a connection using ODBC.
 * 2) Click the Run With 50 Output Parameters command button to run the stored procedure which returns 50 output parameters. Notice that the error does not occur.

To Test These Scenarios with 49 Output Parameters
Run the appropriate Oracle scripts to create the same items with 49 rather than 50 output parameters as noted earlier. (These scripts can also be edited to use anywhere from 1 to 48 output parameters as well.)

The third-party products that are discussed in this article are manufactured by companies that are independent of Microsoft. Microsoft makes no warranty, implied or otherwise, regarding the performance or reliability of these products.

Keywords: kbbug kbfix KB259941

-

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

© Microsoft Corporation. All rights reserved.