How to return Cursor and another parameter from Oracle Procedure.

Topics: Data Access Application Block
Jun 17, 2014 at 2:25 PM
Edited Jun 17, 2014 at 2:25 PM
For this example I use a simple Procedure:
create or replace 
PROCEDURE PR_SHOWAULAS
(
  TOTALROWS OUT NUMBER,
  RESULTCURSOR OUT SYS_REFCURSOR
)
AS 
BEGIN
  open RESULTCURSOR for
    select * from tblaulas_aul;

    
  select count(*) into TOTALROWS from tblaulas_aul;
  
END PR_SHOWAULAS;
If in the above procedure have only the output parameter cursor
create or replace 
PROCEDURE PR_SHOWAULAS
(
  RESULTCURSOR OUT SYS_REFCURSOR
)
AS 
BEGIN
....
I only need to do this:
object[] parameters = new object[] { null };

var result = defDatabase.ExecuteSprocAccessor("PR_SHOWAULAS", rowMapper, parameters);

var totalResult = result.ToList();
And everything works right, however I have another output parameter in my procedure, If I trying to do the same, doesn't work.
int totalCount = Int32.MinValue;
object[] parameters = new object[] { totalCount,  null };

var result = defDatabase.ExecuteSprocAccessor("PR_SHOWAULAS", rowMapper, parameters);

var totalResult = result.ToList();
It retrieves only the collection (cursor), I check if totalCount has updated his value but hasn't.

How to solve this?
Thanks.
Jun 18, 2014 at 5:49 AM
Accessors do not support output parameters. If you want to use output parameters then you will have to use other methods that work with DbCommand.

~~
Randy Levy
entlib.support@live.com
Enterprise Library support engineer
Support How-to