Databases
StackOverflow2013
Postsdbo
POCan't get out parameter of oracle in.net
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POCan't get out parameter of oracle in.net
    primarykey
    data
    text
    <p>I has been 2 day since i'm trying to get out parameter out of pl/sql procedure in .net. I user ODAC(ORACLE DATA ACESS CLIENT). Here is my prevuouse question.<a href="https://stackoverflow.com/questions/12607923/cant-get-out-parameter-of-oracle-in-net/12628179#12628179">Can&#39;t get out parameter of Oracle in .net</a></p> <p>Now, i changed procedure to function and try to return varchar 2 value.</p> <p>C# CODE</p> <pre><code>float cur_rate; decimal value; if (!String.IsNullOrEmpty(RateTxtBox.Text) &amp;&amp; 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_FUNC"; 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.Decimal,ParameterDirection.Output); cmd.Parameters.Add("RETURN_VAL", OracleDbType.Varchar2, ParameterDirection.ReturnValue); cmd.ExecuteNonQuery(); int result = Int16.Parse(cmd.Parameters["RETURN_VAL"].Value.ToString()); </code></pre> <p>It throws an <code>{"ORA-06502: PL/SQL: numeric or value error: character string buffer too small\nORA-06512: at line 1"}</code></p> <p>pl/sql function</p> <pre><code>create or replace function CACCRUEMENT_INS_FUNC( p_check_info_id in number, p_calculate_on in date, p_curr_id in number, p_cur_rate in number, p_result out number)return 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_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; if Last_accruemet_date &gt; p_calculate_on then --p_result :=2;--'Accruement date must be between date of last adjustment and next C-Check date!'; return 'Accruement date must be between date of last adjustment and next C-Check date!'; goto l_EndOfLoop; end if; exception when no_data_found then --p_result:=0;--'Adjustment is not calculated!'; return 'Adjustment is not calculated!'; goto l_EndOfLoop; 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 null; 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&gt;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_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:=1;--'Operation complited successfully'; return 'Operation complited successfully'; update_total_accumulated(p_check_info_id); &lt;&lt;l_EndOfLoop&gt;&gt; ROLLBACK; EXCEPTION WHEN OTHERS THEN --DBMS_OUTPUT.PUT_LINE(SQLERRM); p_result:=3;--'Oracle not handled exception!'; ROLLBACK; end CACCRUEMENT_INS_FUNC; </code></pre>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USCommunity
    1. USNate
    plurals
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. COI have no experience with .net, but with the oracle side. I assume ,that during runtime your function will be called in some anonymous pl/sql block, assembled with the info provided about the parameters and return value. If that is the case, then the varchar2 variable used in that statement needs a length. Is there any way for you to define/configure/set a size for "OracleDbType.Varchar2"?
      singulars
      1. POCan't get out parameter of oracle in.net
      1. USJuergen Hartelt
    2. COYes, i have already solved it. I should have chooce other overloaded method of OracleComomand.Cmd.Parameters.Add() methed ,that hold the length of return value. Thanks!!
      singulars
      1. POCan't get out parameter of oracle in.net
      1. USNate
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

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