Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: C# and Oracle 8i - returns from stored procedures

Re: C# and Oracle 8i - returns from stored procedures

From: tojo <tojo_at_hotmail.com>
Date: Thu, 27 Jan 2005 22:42:43 +0100
Message-ID: <MPG.1c637ebee89f692d989680@news.t-online.de>


In article <1106848663.578907.176310_at_c13g2000cwb.googlegroups.com>, bret.colburn_at_lmco.com says...
> I'm accessing a stored procedure that returns two parameters, the first
> is a string and the second is a cursor. I get the cursor data but not
> the string. Any ideas?
>
>
> Current code as follows :
>
>
> OracleConnection conn = new OracleConnection(connString);
> DataSet myDataSet = new DataSet();
> conn.Open();
> OracleCommand cmd = conn.CreateCommand();
> cmd.CommandText = "dag_select.mpe_list";
> cmd.CommandType = CommandType.StoredProcedure;
> cmd.Parameters.Add("p_mtype",OracleType.LongVarChar,32).Value = "";
> cmd.Parameters.Add("p_mname",OracleType.LongVarChar,32).Value = "";
> // length included in next Add because I was getting exceptions without
> it
> cmd.Parameters.Add("p_rc",OracleType.LongVarChar,50).Direction =
> ParameterDirection.Output;
> cmd.Parameters.Add("p_rowset",OracleType.Cursor).Direction =
> ParameterDirection.Output;
> OracleDataAdapter adapt = new OracleDataAdapter(cmd);
> adapt.Fill(myDataSet);
> // at this point I thought I'd have two tables in my dataset, the first
> containing only the string from the first parameter, the second
> containing the cursor data. I'm only getting one table and it contains
> the cursor data.
>
>
> for (int count = 0; count < myDataSet.Tables[0].Rows.Count; count++)
> {
> // process rows from cursor
> }
>
>
> More info:
> using C#.net, Oracle 8i, Windows 2000
>
>

Bret, here are some things to try:

  1. Are you declaring the parameters in the same order as defined in your PL/SQL procedure spec? Maybe you could post your procedure spec
  2. I seem to recall that OLEDB cursor parameters are bound automatically. So maybe something is wrong with the way you're binding your p_rc parameter. Did you try using plain old OracleType.VarChar? 3 Always start out simple when debugging. Try a procedure that just returns one VarChar with no inputs, then add a cursor, maybe an input or two, etc. etc.
  3. If you're using C#, why not use the .NET Provider? You'll have to upgrade your client to 9i or higher, but you can still use your 8i server.
    • Tom
Received on Thu Jan 27 2005 - 15:42:43 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US