|
 |
 |
 |
 |
Microsoft Knowledge Base Article
This article contents is Microsoft Copyrighted material.
©2005-©2007 Microsoft Corporation. All rights reserved. Terms
of Use |
Trademarks
Article ID: 259941 - Last Review: October 17, 2003 - Revision: 2.1 FIX: 50 or More Output Parameters Produces "Unspecified Error" with the OLE DB Provider for OracleThis article was previously published under Q259941
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 "Unspecified Error" 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.
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.
To temporarily work around this problem, you can use the Microsoft OLE DB Provider for ODBC.
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:
- 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:
and
fname16(percount) := singleperson.fname16;
- Start a new Visual Basic Standard EXE Project. Form 1 is created by default.
- From the Project Menu, under References, select Microsoft ActiveX Data Objects Library 2.5.
- Draw the following controls on Form1 and set their Properties as listed here:
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
- 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 "Provider=MSDAORA.1;Password=tiger;User ID=scott;Data Source=dseOracle8i;Persist Security Info=True"
If cn.State = 1 Then
MsgBox "OleDb Orcl Connection is Open"
End If
End Sub
Private Sub cmdCnODBC_Click()
If cn.State = 1 Then
cn.Close
Set cn = Nothing
End If
cn.ConnectionString = "dsn=kilburn;uid=scott;pwd=tiger"
cn.Open
If cn.State = 1 Then
MsgBox "ODBC Connection is Open"
End If
End Sub
Private Sub cmd49Params_Click()
'With ssn16 as 49th parameter below, runs without errors:
strSQL = "{call pk_storedProcedure.sp_storedProcedure (?, {resultset 4, ssn, fname, lname," & _
"ssn1, fname1, lname1," & _
"ssn2, fname2, lname2," & _
"ssn3, fname3, lname3," & _
"ssn4, fname4, lname4," & _
"ssn5, fname5, lname5," & _
"ssn6, fname6, lname6," & _
"ssn7, fname7, lname7," & _
"ssn8, fname8, lname8," & _
"ssn9, fname9, lname9," & _
"ssn10, fname10, lname10," & _
"ssn11, fname11, lname11," & _
"ssn12, fname12, lname12," & _
"ssn13, fname13, lname13," & _
"ssn14, fname14, lname14," & _
"ssn15, fname15, lname15," & _
"ssn16} ) }"
With cmd
Set .ActiveConnection = cn
.CommandText = strSQL
.CommandType = adCmdText
Set param = .CreateParameter("sIn", adBSTR, adParamInput, 50, "Sam")
.Parameters.Append param
End With
Set rs = cmd.Execute
Debug.Print rs(1).Value
Debug.Print rs(0).Value
MsgBox "Done 49"
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 = "{call pk_storedProcedure.sp_storedProcedure (?, {resultset 4, ssn, fname, lname," & _
"ssn1, fname1, lname1," & _
"ssn2, fname2, lname2," & _
"ssn3, fname3, lname3," & _
"ssn4, fname4, lname4," & _
"ssn5, fname5, lname5," & _
"ssn6, fname6, lname6," & _
"ssn7, fname7, lname7," & _
"ssn8, fname8, lname8," & _
"ssn9, fname9, lname9," & _
"ssn10, fname10, lname10," & _
"ssn11, fname11, lname11," & _
"ssn12, fname12, lname12," & _
"ssn13, fname13, lname13," & _
"ssn14, fname14, lname14," & _
"ssn15, fname15, lname15," & _
"ssn16, fname16} ) }"
With cmd
Set .ActiveConnection = cn
.CommandText = strSQL
.CommandType = adCmdText
Set param = .CreateParameter("sIn", adBSTR, adParamInput, 50, "Sam")
.Parameters.Append param
End With
Set rs = cmd.Execute
Debug.Print rs(1).Value
Debug.Print rs(0).Value
MsgBox "Done 50"
Exit Sub
MyError:
i = 1
StrTmp = StrTmp & vbCrLf & "VB Error # " & vbCrLf & Str(Err.Number) & vbCrLf
StrTmp = StrTmp & vbCrLf & " Generated by " & vbCrLf & Err.Source & vbCrLf
StrTmp = StrTmp & vbCrLf & " Description " & vbCrLf & Err.Description
' Display Error Information for each Error object.
Set Errs1 = cn.Errors
For Each errLoop In Errs1
With errLoop
StrTmp = StrTmp & vbCrLf & "Error #" & i & ":"
StrTmp = StrTmp & vbCrLf & " ADO Error #" & .Number
StrTmp = StrTmp & vbCrLf & " Description " & .Description
StrTmp = StrTmp & vbCrLf & " Source " & .Source
i = i + 1
End With
Next
txtErrorInfo.Text = StrTmp
End Sub
- 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.
To Test with 50 Output Parameters- Click the Cn OleDb Provider for Oracle command button to open a connection using the OLEDB Provider for Oracle.
- Click the Run With 50 Output Parameters command button to run the stored procedure which returns 50 output parameters.
The "Unspecified Error" message will appear. - Click the Cn Using ODBC command button to open a connection using ODBC.
- 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.
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
Community Feedback System
Very often, it takes hours to solve a problem. Very often, you've looked high
and low, and have tried a lot of solutions. When you finally found it, chances
are, it was because someone else helped you. Here's your chance to give back.
Use our community feedback tool to let others know what worked for you and what
didn't.
Please also understand that the community feedback system is not warranted to be
correct, it's simply a system that we've built to let people try and help each
other. If something in a feedback response doesn't make sense to you, or you're
not comfortable making changes that the feedback talks about (like registry
edits), please consult a professional.
Thank you for using kbAlertz.com Feedback System.
-- Scott Cate
|
 |
 |
 |
 |
 |
 |
 |
| |