Oracle function return rows into c#

Question

I want to create a procedure or function in oracle that returns more than one row of different types to my application. I found some examples on the internet but i can't get it to work...

Example: I created a custom type in oracle:

create type proc_selectall is object 
    (Mjera varchar2(50),
    Status number(10,0),
    Naziv varchar2(50),
    Stat number(10,0));

Then:

create type ret_selectall is table of proc_selectall;

Then created function:

create or replace function fsp_SelectAll return ret_selectall
is
l_ret_selectall ret_selectall := ret_selectall();
n integer := 0;
BEGIN 
    for r in(SELECT JEDINICAMJERE.Mjera,
    JEDINICAMJERE.Status, 
    KATEGORIJE.Naziv, 
    KATEGORIJE.Status as Stat
FROM JEDINICAMJERE, KATEGORIJE)
loop
  l_ret_selectall.extend;
  n := n + 1;
  l_ret_selectall(n) := proc_selectall(r.Mjera, r.Status, r.Naziv, r.Stat);
end loop;
return l_ret_selectall;
END;

When I type this line:

select * from table (fsp_SelectAll)

I get a nice and expected output:

MJERA | STATUS | NAZIV | STAT
tr      0        name1   1
fd      1        name2   1
ds      1        name3   0
.....

Later i tried to get those values in my app. I tried something like this:

//cmd.Parameters.Add("proc_selectall ", OracleDbType.Object);
//cmd.Parameters.Add("ret_selectall ", OracleDbType.Array);
//cmd.Parameters["ret_selectall "].Direction = ParameterDirection.ReturnValue;
//if (connection.State == ConnectionState.Closed)
//    connection.Open();
//cmd.ExecuteNonQuery();

Later this...

//OracleCommand objCmd = new OracleCommand("select * from table fsp_SelectAll", connection);
//objCmd.CommandType = CommandType.StoredProcedure;
//objCmd.ExecuteNonQuery();
//int size=objCmd.ExecuteReader().FieldCount;

I also tried with

//adapter = new OracleDataAdapter(cmd);...

But there are always some errors about type, parameters... (Connection to oracle work. I executed Insert, Update, Delete procedures and they work fine)

Also i tried to create this procedure: (this was my first try)

CREATE OR REPLACE PROCEDURE osp_SelectAll (Mjera OUT VARCHAR2,
Status OUT NUMBER, Naziv OUT VARCHAR2,Stat OUT NUMBER) AS 
BEGIN 
    SELECT JEDINICAMJERE.Mjera,
    JEDINICAMJERE.Status, 
    KATEGORIJE.Naziv, 
    KATEGORIJE.Status as Stat INTO Mjera, Status, Naziv, Stat
FROM JEDINICAMJERE, KATEGORIJE;
END osp_SelectAll;

But it doesn't return any row or rows... and i give up.

Please help me, i need to solve this.

You want to execute a query, so ExecuteNonQuery() is the wrong method. You need ExecuteReader() which returns a reader, and you want to loop while(Reader.Read()). Inside that loop you want to parse the fieds like var someField = (string)Reader["SomeFieldName"] — oerkelens
– oerkelens, Commented Nov 7, 2017 at 22:29
Thanks for replay... I created that what you suggest and in line: rdr=objCmd.ExecuteReader(); I got error: {"ORA-06550: line 1, column 27:\nPL/SQL: ORA-00906: missing left parenthesis\nORA-06550: line 1, column 7:\nPL/SQL: SQL Statement ignored"} — mrKva
– mrKva, Commented Nov 7, 2017 at 22:39

Wernfried Domscheit · Accepted Answer · 2017-11-08 07:42:29Z

2

Returning a user defined object is not so easy, it requires a lot of additional effort.

Try this one:

create or replace function fsp_SelectAll return SYS_REFCURSOR is
   res SYS_REFCURSOR;
BEGIN 
    OPEN res FOR
    SELECT JEDINICAMJERE.Mjera,
      JEDINICAMJERE.Status, 
      KATEGORIJE.Naziv, 
      KATEGORIJE.Status as Stat
    FROM JEDINICAMJERE, KATEGORIJE;
    return res;
END;

and then

OracleCommand objCmd = new OracleCommand("fsp_SelectAll", connection);
objCmd.CommandType = CommandType.StoredProcedure;
objCmd.Parameters.Add("res", OracleDbType.RefCursor, ParameterDirection.ReturnValue);

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

Or you can use ExecuteReader(), if you prefer:

OracleDataReader dr = objCmd.ExecuteReader();
while ( dr.Read() ) {
   ...
}
dr.Close();

Note, I never worked with .CommandType = CommandType.StoredProcedure;. If it fails use this syntax:

OracleCommand objCmd = new OracleCommand("BEGIN :res := fsp_SelectAll; END;", connection);
objCmd.CommandType = CommandType.Text;

answered Nov 8, 2017 at 7:42

Wernfried Domscheit

60.4k10 gold badges93 silver badges132 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

mrKva Over a year ago

Than you very much. This simple way is what i need.

Collectives™ on Stack Overflow

Oracle function return rows into c#

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related