calling stored procedure from c#

From: <codefragment_at_googlemail.com>
Date: Fri, 19 Oct 2007 06:55:34 -0700
Message-ID: <1192802134.723964.84120@q5g2000prf.googlegroups.com>

Hi
  As the subject says, I just want to call a stored procedure with a few arguments. I can seem
to call it ok if it only takes 1 argument and returns a cursor but as soon as I try to add parameters I get erros

My connection string loooks like this:

Provider=OraOLEDB.Oracle;Data Source=ORACTEST;User ID=amortest;Password=amor;PLSQLRSet=1;ChunkSize=500;

And the code looks like this:

                        string connectionString =
GetConnectionString();
                        OleDbConnection dataConnection = new
OleDbConnection();
                        dataConnection.ConnectionString =
connectionString;
                        dataConnection.Open();


                        DataSet dataSet = new DataSet();

OleDbCommand selectCommand = new OleDbCommand("{call TEST.TEST(?)}", dataConnection);
selectCommand.CommandType = CommandType.Text;

OleDbParameter param = new OleDbParameter("pCODE3", OleDbType.UnsignedTinyInt, 8);
param.Value = 1;
param.Direction = ParameterDirection.Input; selectCommand.Parameters.Add(param);

param = new OleDbParameter("pSTR", OleDbType.VarChar, 100); param.Value = "hi";
param.Direction = ParameterDirection.Input; selectCommand.Parameters.Add(param);

                        OleDbDataAdapter adapter = new
OleDbDataAdapter(selectCommand);
                        adapter.Fill(dataSet);


                        return dataSet;

Resulting error Message from nunit

MyCode.BasicTest : System.InvalidOperationException : Command parameter[1] '' is invalid.

----> System.Data.OleDb.OleDbException : Multiple-step OLE DB
operation generated errors. Check each OLE DB status value, if available. No work was done.

My Noddy Stored Procedure

CREATE OR REPLACE PACKAGE "TEST" AS

          TYPE CUR IS REF CURSOR;
        PROCEDURE TEST (pCUR1 OUT CUR,
                pCODE IN number);

END TEST;
/
CREATE OR REPLACE PACKAGE BODY "TEST" AS

        PROCEDURE TEST (pCUR1 OUT CUR,
                pCODE IN number) IS
                BEGIN

BEGIN
OPEN pCUR1 for
select * from tempa where theColumn=pCode;

                END;
        END TEST;
        END TEST;

/ Received on Fri Oct 19 2007 - 08:55:34 CDT