Trying to call procedure and return value in that procedure

Question

I'm trying to call a procedure and use the value which i get after executing in my code.

I have created a procedure Getdata and I'm calling it from ASP.NET MVC and try to use it.

Oracle procedure:

    CREATE OR REPLACE PROCEDURE Getdata(v_hr_stk_out out number,v_cr_stk_out out number) Is
    r_stk  number;
    vr_stk number;
    BEGIN
    select round(sum(a.batch_wt)) into r_stk from dbprod.sm_data a where a.iss_date is null and a.cw_coil_no is not null and a.prod_cd = '37' and a.from_plant != a.hsource;
    select round(sum(a.batch_wt)) into vr_stk  from dbprod.psm_data a where a.iss_date is null and a.cw_coil_no is not null and a.prod_cd = 'C9' and a.from_plant != a.hr_source;
v_hr_stk_out:=v_hr_stk;
v_cr_stk_out :=v_cr_stk;
    END;

ASP.NET MVC side:

 OracleConnection conn = new OracleConnection(ConfigurationManager.ConnectionStrings["Mycon"].ToString());
 conn.Open();

 OracleCommand command = new OracleCommand();
 command.Connection = conn;
 command.CommandText = "Getdata";
 command.CommandType = CommandType.StoredProcedure;

 OracleDataAdapter adapter = new OracleDataAdapter(command);

 DataSet dataSet = new DataSet();
 adapter.Fill(dataSet);

 conn.Close();
 return View("Home",dataSet);

Binding value in view

@using System.Data;
@model DataSet
    <table cellpadding="0" cellspacing="0">
        <tr>
            <th>A</th>
            <th>B/th>
            <th>C</th>        
        </tr>
        @foreach (DataRow row in Model.Tables[0].Rows)
        {
            <tr>
                <td>F</td>
                <td>Dataset.Tables[0]</td>
                <td>Dataset.Tables[1]</td>            
            </tr>
        }
    </table>

Value from procedure should come and bind in respective column in view.

Currently, no value is stored in the dataset after calling adapter.Fill(dataSet);.

I am new to ASP.NET MVC and Oracle. Any idea would be appreciated

Did you run the query directly in your database? And saw results? — Bosco, Jul 05 '19 at 04:43
I am not an expert in Oracle, but I can tell you are not returning anything from the procedure. Seems like, you are just assigning the variables inside procedure that has no reference in your c# code. Have a read at this https://www.oracletutorial.com/plsql-tutorial/plsql-procedure/ — Bharathi, Jul 05 '19 at 05:14
@AminSaadati giving error datatable is a type ,which is not valid in the given context — Mohan, Jul 05 '19 at 05:29
In fact I do not think the `DataTable` make error but look some other samples of calling `sp` by `oracle` in this page https://stackoverflow.com/questions/3940587/calling-oracle-stored-procedure-from-c — Amin Saadati, Jul 05 '19 at 05:34

Satish Patil · Answer 1 · 2019-07-08T06:02:30.493

1

As you are using Fill Dataset in ADO.Net, You need to use Ref Cursors to get data

Change Your procedure to

 CREATE OR REPLACE PROCEDURE Getdata(v_hr_stk_out OUT SYS_REFCURSOR) Is
r_stk  number;
vr_stk number;
BEGIN
select round(sum(a.batch_wt)) into r_stk from dbprod.sm_data a where a.iss_date is null and a.cw_coil_no is not null and a.prod_cd = '37' and a.from_plant != a.hsource;
select round(sum(a.batch_wt)) into vr_stk  from dbprod.psm_data a where a.iss_date is null and a.cw_coil_no is not null and a.prod_cd = 'C9' and a.from_plant != a.hr_source;

OPEN v_hr_stk_out For 
select r_stk, vr_stk from dual

END;

For C# follow this link

UPDATE

<table cellpadding="0" cellspacing="0">
    <tr>
        <th>A</th>
        <th>B/th>
        <th>C</th>        
    </tr>
    @foreach (DataRow row in Model.Tables[0].Rows)
    {
        <tr>
            <td>F</td>
            <td>@row["r_stk"]</td> 
            <td>@row["vr_stk"]</td>                        
        </tr>
    }
</table>

edited Jul 08 '19 at 06:02

answered Jul 05 '19 at 11:01

Satish Patil

438
5
15

and how can i use it in c# side to fetch individual value from cursor.Any idea – Mohan Jul 05 '19 at 12:01
@Mohan you already did that in MVC part, Just change the Proc – Satish Patil Jul 05 '19 at 12:19
@Mohan see update answer for ref link, use that for c# side code – Satish Patil Jul 05 '19 at 14:16
value has been return in table but how to bind that value in view any idea i am not able to bind value through above method i have tried – Mohan Jul 08 '19 at 04:27
@Mohan you got data in the dataset ? – Satish Patil Jul 08 '19 at 05:08
i have two different table in dataset and i want to bind data present in different table in different column of html table but the updated answer will only loop through table[0]. could you guide. – Mohan Jul 08 '19 at 05:23
@Mohan actually we have 2 datatable because you return two results, change the query to bring the result in a single table, then you get two columns and you can easily loop through – Satish Patil Jul 08 '19 at 05:23
i have did my coding in the way what i want geting two different table with single column in a dataset and now in view side i want to bind different column of HTML table.could you guide how to bring result in a single table beacause the way you explained earlier i have get the desired result only implementation in view is creating problem. – Mohan Jul 08 '19 at 05:38
@Mohan see the updated answer, change procedure as well as view, proc is written roughly see if there are an error in it – Satish Patil Jul 08 '19 at 06:03
the way proc you have written i tried but getting error even after modifying could help how to write this type proc correctly since i am new to oracle, error are 1.encountered symbole "round" expecting.... 2.encountered symbole "Into" expecting ....3.encountered symbole "A" expecting ... – Mohan Jul 08 '19 at 06:32

score 0 · Answer 2 · answered Jul 05 '19 at 06:02

0

Try adding this line before ending your procedure's body

 Select r_stk, vr_stk from dual;

answered Jul 05 '19 at 06:02

a.tolba

137
1
1
13

@a.toba it is giving error 'into statement is missing – Mohan Jul 05 '19 at 06:16

Trying to call procedure and return value in that procedure

2 Answers2