Use asp.net to call oracle stored procedure. Visual Studio 2005

Question

We have a oracle stored procedure that resides in oracle 10.2 database. The stored procedur contains certain inputs and outputs parameters. We are trying to create an asp.net webapplication to execute the stored procedure and bind the result that comes from the output paramerters into a gridview. But no luck.

Here is what I have done so far

Oracle.DataAccess.dll

I installed the Oracle Data Access Components (ODAC) with Oracle Developer Tools for Visual Studio and used it to call the oracle stored procedure.

** Here is the error I am getting **
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'CALC_NUMBERVOTES'
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'CALC_NUMBERVOTES'
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'CALC_NUMBERVOTES'
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'CALC_NUMBERVOTES'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

any help is really appreciated.
oralce stored procedure code

PROCEDURE calc_numbervotes (  
  i_Office                IN  ELECTIONRESULTS.office%TYPE,  
  o_candidate             OUT tblcandidate ,  
  o_party                 OUT tblparty,  
  o_votes                 OUT tblvotes,  
  o_percAll               OUT tblpercAll)  
IS  
/***************Find Number of candidate per office and party******************/  
 CURSOR c1 is  
  SELECT distinct candidate, party,sum(votes) totalvotes  
   FROM ELECTIONRESULTS  
   WHERE candidate not in ('Registered Voters' )  
   AND office = i_office  
   AND Precinct <> ' '  
   GROUP BY candidate,party  
   ORDER BY sum(votes) desc;  
/****************** Find total county votes per candidate *********************/  
 CURSOR c2 is  
  SELECT sum(votes) from ELECTIONRESULTS  
  WHERE candidate !='Registered Voters'  
  AND office       = i_Office  
  AND Precinct    <> ' ';  

  recCount     NUMBER DEFAULT 0;  
  totvotesall  NUMBER;  
  totvotescandidateprec NUMBER;  

 BEGIN  

  OPEN   c2;  
    FETCH c2 into totvotesall;  
   CLOSE c2;  
   ************************************************************/  
   FOR rec  in c1 LOOP  

   recCount:= recCount + 1;  

       o_candidate(recCount):= rec.candidate;  
       o_party(recCount)    := rec.party;  
       o_votes(recCount)    := rec.totalvotes;  

    if rec.totalvotes = 0 then  
       o_percAll(recCount)  := 0;  
    else  

       o_percAll(recCount)  := round((rec.totalvotes/totvotesall)*100,2);  

    end if;  

  END LOOP;  
 END calc_numbervotes;  

asp.net code

Dim constr As String = "data source=ds;user id=uid;password=pwd;"  
Dim orclCon As OracleConnection  
orclCon = New OracleConnection(constr)  
Dim objCmd As OracleCommand = New OracleCommand()  
objCmd.Connection = orclCon  
objCmd.CommandText = "pkg_calc_votes.calc_numbervotes"  
objCmd.CommandType = CommandType.StoredProcedure  
objCmd.Parameters.Add(New OracleParameter("i_office", OracleDbType.NVarchar2,  255)).Value  = "U.S. CONGRESS 05"  
objCmd.Parameters.Add(New OracleParameter("o_party", OracleDbType.NVarchar2, 10)).Direction = ParameterDirection.Output  
objCmd.Parameters.Add(New OracleParameter("o_candidate", OracleDbType.NVarchar2, 255)).Direction = ParameterDirection.Output  
objCmd.Parameters.Add(New OracleParameter("o_votes", OracleDbType.Decimal, 10)).Direction = ParameterDirection.Output  
objCmd.Parameters.Add(New OracleParameter("o_percAll", OracleDbType.Double, 10)).Direction = ParameterDirection.Output  
orclCon.Open()  
Dim orclDtRdr As OracleDataReader = Nothing  
orclDtRdr = objCmd.ExecuteReader()  
gvCursor.DataSource() = orclDtRdr  
gvCursor.DataBind()  
orclDtRdr.Close()  
orclCon.Close()  

The stored proceder is selecting all the data from a table in the database except for one value which the _o_percAll_. this parameter is being calculated in the sp.

Thank you, Eyad

Answer 1

Your stored procedure has four output parameters that are pl/sql associative arrays (presumably, although you don't show how exactly tblcandidate etc. are defined in your code sample). So, in your .Net code, you need to specify that you are binding the four output parameters as pl/sql associative arrays, thusly:

o_party.CollectionType = OracleCollectionType.PLSQLAssociativeArray

and so on. See here for further details:

OracleCommand Object - Array Binding details

Answer 2

To use ExecuteReader(), your stored procedure needs to return a result set. Your stored procedure doesn't seem to do that. Consider doing this, which returns a result set without the use of output parameters:

CURSOR votes_cur is  
  SELECT sum(votes) from ELECTIONRESULTS  
  WHERE candidate !='Registered Voters'  
  AND office       = i_Office  
  AND Precinct    <> ' ';  

  totvotesall  NUMBER;  

  OPEN   votes_cur;  
  FETCH votes_curinto totvotesall;  
  CLOSE votes_cur;  

  SELECT distinct candidate, party,
    sum(votes) totalvotes,
    round((sum(votes)/totvotesall)*100,2) votesPerc
   FROM ELECTIONRESULTS  
   WHERE candidate not in ('Registered Voters' )  
   AND office = i_office  
   AND Precinct <> ' '  
   GROUP BY candidate,party  
   ORDER BY sum(votes) desc;  

My PL/SQL skill is nowhere near my T-SQL skill, but I think this gets you close. You might even be able to remove the cursor, like so:

totvotesall  NUMBER;  

SELECT totvotesall = sum(votes)
FROM ELECTIONRESULTS  
WHERE candidate !='Registered Voters'  
AND office       = i_Office  
AND Precinct    <> ' ';