C# - Pl/SQL Problem with out parameter in stored procedure

Question

I have a stored procedure which looks like this:

    PROCEDURE semifinished_lable_data(p_piece_num_id     IN INTEGER,
                                        p_piece_id         OUT STRING,
                                        p_tickness         OUT NUMBER)
   IS begin
    select p_piece_id,p_tickness into p_piece_id,p_tickness from piece p where p.piece_num_id=p_piece_num_id;
   end;

I've executed it from the database and it worked perfectly. When I execute it from c# app I don't get any value back.

Then when I researched the problem , I added that : cmd.BindByName = true But this time i got the following error

multiple instances of named argument in list

C# codes :

 OracleCommand cmd = new OracleCommand();
            cmd.Connection = conn;
            cmd.InitialLONGFetchSize = 1000;
            var seciliProsedur = "QUA_PRINTING.semifinished_lable_data";
  cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = seciliProsedur;
            cmd.BindByName = true;
            object piece_num_id = gridViewMain.GetFocusedRowCellValue(gridViewMain.FocusedColumn);
 OracleParameter PAR_ID1 = new OracleParameter();
            PAR_ID1.ParameterName = "p_piece_id ";
            PAR_ID1.OracleDbType = OracleDbType.Int32;
            PAR_ID1.Direction = System.Data.ParameterDirection.Input;
            PAR_ID1.Value =piece_num_id;
            PAR_ID1.Size = 100;
            cmd.Parameters.Add(PAR_ID1);
cmd.Parameters.Add("p_piece_id", OracleDbType.Varchar2, 1000, null, ParameterDirection.Output);
cmd.Parameters.Add("p_tickness", OracleDbType.Double,200, null, ParameterDirection.Output);

 conn.Open();


            var da = new OracleDataAdapter(cmd);
            var dt = new DataTable();
            da.Fill(dt);

How can I solve this problem ?

PAR_ID1.ParameterName = "p_piece_id "; has to be PAR_ID1.ParameterName = "p_piece_num_id"; — Srinika Pinnaduwage
– Srinika Pinnaduwage, Commented Jun 18, 2020 at 14:11
Yes , I fix it but execute it from c# app I don't get any value back again. — Hakan özel
– Hakan özel, Commented Jun 18, 2020 at 14:18
Your parameters / data types etc doesn't seems to be "proper", names not matching, lot of places to look for. Check the values of variables are there before execution. If still cannot use SQL Profiler to see what is the query executed in the server. — Srinika Pinnaduwage
– Srinika Pinnaduwage, Commented Jun 18, 2020 at 15:34

Wernfried Domscheit · Accepted Answer · 2020-06-18 15:56:53Z

Actually I never used CommandType = CommandType.StoredProcedure.

Try this

cmd.CommandType = CommandType.Text;
seciliProsedur = "BEGIN QUA_PRINTING.semifinished_lable_data(:p_piece_num_id  , :p_piece_id, :p_tickness); END;"

OracleParameter PAR_ID1 = new OracleParameter();
PAR_ID1.ParameterName = "p_piece_num_id";
...
cmd.Parameters.Add("p_piece_id", OracleDbType.Varchar2, ParameterDirection.Output);
cmd.Parameters("p_piece_id").DbType = DbType.Varchar2;
cmd.Parameters.Add("p_tickness", OracleDbType.Varchar2, 240, null, ParameterDirection.Output);
cmd.Parameters("p_tickness").DbType = DbType.String;

I think you must set DbType and OracleDbType property.

Then another problem of your code is

var da = new OracleDataAdapter(cmd);
var dt = new DataTable();
da.Fill(dt);

Your procedure does not return any resultSet (i.e. RefCursor), thus above commands do not work. Use

cmd.ExecuteNonQuery()

Collectives™ on Stack Overflow

C# - Pl/SQL Problem with out parameter in stored procedure

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related