Oracle cursor problem using DAAB Stored Procedure

[Guest]

Hi,
I'm using the Enterprise Library June 2005 - Data Access Application Block
(DAAB) to connect to Oracle 9i Database.
When I try to ExecuteDataset method with a stored procedure that returns a
cursor I got an error.
This is VB.NET code:
------------------------------------- VB.NET CODE
----------------------------------------
Dim dbCmdWrapper As DBCommandWrapper =
dbPMRORA.GetStoredProcCommandWrapper("APP.GetList")
dbCmdWrapper.AddOutParameter("p_cursor", OracleType.Cursor, 0)
Dim dsCandidates As DataSet = Nothing
dsCandidates = dbPMRORA.ExecuteDataSet(dbCmdWrapper)

'~~~~~~~~~ Also Tryed this for the parameter ~~~~~~~~~~~~~~
'dbCmdWrapper.AddOutParameter("p_cursor", OracleType.Cursor, 2000)
'dbCmdWrapper.AddOutParameter("p_cursor", DBType.Object,2000)
'---------------------------- End VB.NET Code
-------------------------------------------
So I tried to call the oracle stored procedure using different parameters
and without adding the parameter but none of the method works and I got the
following error:
------------------------ Error Message
-------------------------------------------------
[OracleException: ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'GETLIST'
------------------------ Error Message
-------------------------------------------------

This the the oracle stored procedure

------------------------ Oracle Package
------------------------------------------------
Create or Replace PACKAGE APP
IS
TYPE t_generic_cursor IS REF CURSOR;
PROCEDURE getList(
p_cursor OUT t_generic_cursor
);
END;
/
Create or Replace PACKAGE BODY APP
IS
PROCEDURE getList(
p_cursor OUT t_generic_cursor
)
IS
v_sql VARCHAR2(2000);
BEGIN
v_sql := 'Select * From tbCandidate';
-- Open the ref cursor
OPEN p_cursor FOR v_sql;
END ;
END app;
------------------------ Oracle Package

 

[Peter Huang [MSFT]]

Hi

Because DAAB is originally for SQL server so it may do not work well with
Oracle.
I think you may try to Trace in the oracle side to see what is passed into
the Oracle.
You may try to use T_CURSOR as the link below.
Retrieving Data Using the DataReader
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/htm
l/cpcontheadonetdatareader.asp

Best regards,

Peter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Guest]

Well I think I found the solution on
http://www.gotdotnet.com/codegaller...threadid=0dc416cf-790a-45e0-a81f-52a71dc37dff

And to recap it, basically to make you DAAB get the Ref Cursor output
parameter from the stored procedure you don't have to add any parameter to
the DBCommandWrapper object because DAAB will add one automatically for you,
but the name of the out parameter in your stored procedure must be cur_OUT
which must be delared as a Ref Cursor. This information is already documented
in the Enterprise Library Release Notes Word document.
But this automatic feature will only add one output cursor parameter to your
command object, my question will be:
How about if you have multiple output cursor parameters returned by the
Oracle stored procedure how should your code handle the situation.

Thanks Peter.

 

[Peter Huang [MSFT]]

Hi

Thanks for your knowledge sharing, I think the whole community will benifit
from your experience.

Best regards,

Peter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Terence Watt]

Hello,
did u find a way to handle multiple output cursor parameters returned by the

Oracle stored procedure?
Thanks