StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POCan't get out parameter of Oracle in .net
text
Body
copied!<p>As i said in title, problem is - out parameter of oracle of procedure is null in .net ,although this parameter is assigned in pl/sql testing. Out parameter datatype is nvarchar2. I tried to change this value to int, and it worked well. But i need to return text from procedure!!! What is the issue? BTW i use using Oracle.DataAccess.Client; Here is the code:</p> <pre><code>private void OKButton_Click(object sender, EventArgs e) { float cur_rate; decimal value; if (!String.IsNullOrEmpty(RateTxtBox.Text) && decimal.TryParse(RateTxtBox.Text, out value)) { cur_rate = float.Parse(RateTxtBox.Text); con = new OracleConnection(conStr); con.Open(); cmd = con.CreateCommand(); cmd.BindByName = true; cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = schema_name + ".CACCRUEMENT_INS"; cmd.Parameters.Add("p_check_info_id", OracleDbType.Int32, header_id, ParameterDirection.Input); cmd.Parameters.Add("p_calculate_on", OracleDbType.Date, DateTime.Parse(dateTimePicker1.Text), ParameterDirection.Input); cmd.Parameters.Add("p_curr_id", OracleDbType.Decimal, CurrencyCombobox.SelectedValue, ParameterDirection.Input); cmd.Parameters.Add("p_cur_rate", OracleDbType.Decimal, Decimal.Parse(RateTxtBox.Text), ParameterDirection.Input); cmd.Parameters.Add("p_result", OracleDbType.NVarchar2).Direction = ParameterDirection.Output; //cmd.Parameters.Add("p_result", OracleDbType.NVarchar2,ParameterDirection.Output); cmd.ExecuteNonQuery(); string result = cmd.Parameters["p_result"].Value.ToString(); MessageBox.Show(result); Thread.Sleep(2); this.Close(); } else { MessageBox.Show("Invalid data format!"); } } </code></pre> <hr> <p>Pl/sql precedure:</p> <pre><code>create or replace procedure CACCRUEMENT_INS( p_check_info_id in number, p_calculate_on in date, p_curr_id in number, p_cur_rate in number, p_result out varchar2 ) is v_Accruemtnt number; v_rate number; v_cur_rate_in_check_info number; Last_accruemet_date date; monthes_bwn_last_adj_n_chck number; next_check date; check_info_rate number; begin select c.next_check into next_check from check_info c where c.id_check_info = p_check_info_id; begin select max(c.calculate_on) into Last_accruemet_date from Caccruement c where c.fk_check_info = p_check_info_id group by c.fk_check_info; exception when no_data_found then begin select max(c.calculate_on_date) into Last_accruemet_date from check_info_lines c where c.fk_check_info = p_check_info_id group by c.fk_check_info; exception when no_data_found then p_result:='Adjustment is not calculated!'; goto l_EndOfLoop; end; end; select ci.curency_rate into check_info_rate from check_info ci where ci.id_check_info = p_check_info_id; select c.rate into v_rate from Check_Info c where c.id_check_info = p_check_info_id; monthes_bwn_last_adj_n_chck:=MONTHS_BETWEEN(next_check,Last_accruemet_date); IF(monthes_bwn_last_adj_n_chck>round(MONTHS_BETWEEN(p_calculate_on,Last_accruemet_date),3)) THEN select c.curency_rate into v_cur_rate_in_check_info from check_info c where c.id_check_info = p_check_info_id; --v_rate:=round((v_rate/v_cur_rate_in_check_info),3); v_Accruemtnt := round(v_rate/check_info_rate * MONTHS_BETWEEN(p_calculate_on, Last_accruemet_date)*p_cur_rate,3) ; insert into CAccruement(Id_Accruemtnt,Calculate_On,Accruemtnt,Fk_Check_Info,Cu_Id,Cu_Rate,Calculation_Date,Accruemtnt_Kzt) values (accruement_seq.nextval,p_calculate_on,round(v_Accruemtnt/p_cur_rate,3),p_check_info_id,p_curr_id,p_cur_rate,Sysdate,v_Accruemtnt); update check_info c set c.total_accumulated = c.total_accumulated+v_Accruemtnt where c.id_check_info = p_check_info_id; END IF; COMMIT; p_result:='Operation complited successfully'; update_total_accumulated(p_check_info_id); <<l_EndOfLoop>> ROLLBACK; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(SQLERRM); ROLLBACK; end CACCRUEMENT_INS; </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload