Odtnhj

I'm trying to execute plsql procedure from Visual Studio. Basically what I'm trying to do is pass some parameters and using them I'm trying to insert values into a different table. The procedure uses a cursor and inserts values into a table and after that I'm deleting that table to make use of it for the next time. The table obtained using cursor needs to be finally displayed on gridbox. But the below program isn't displaying anything. Can someone help me with this?

DB_connect();

String x3 = "google";
String x1;
String x2;

String s1 = "delete from temp";

OracleCommand comm = new OracleCommand(s1, conn);
comm.ExecuteNonQuery();

System.Data.OracleClient.OracleCommand comm2 = new System.Data.OracleClient.OracleCommand();
String s2 = "exec cv";

comm2.CommandText = s2;
comm2.CommandType = CommandType.StoredProcedure;
comm2.Parameters.Add("x", System.Data.OracleClient.OracleType.Number).Value = comboBox1.Text;
comm2.Parameters.Add("y", System.Data.OracleClient.OracleType.Number).Value = comboBox2.Text;
comm2.ExecuteNonQuery();

String s3 = "select * from temp";

OracleCommand comm3 = new OracleCommand(s3, conn);
OracleDataAdapter MyAdapter3 = new OracleDataAdapter();//adapter acts as interface btw database and dataset(which is collectio of tables)
MyAdapter3.SelectCommand = comm;

DataTable dTable3 = new DataTable();//datatable represents a single table in database 
MyAdapter3.Fill(dTable3);

dataGridView1.DataSource = dTable3;
conn.Close();

This is my stored procedure:

create or replace procedure cv(x in int, y in int, z in varchar)
as
 cursor c
 is
 select email, collegename, cgpa, compname
 from student_cv
 where (cgpa >= x and yearsofexp >= y) and compname = z;

 tem c%rowtype;

 begin
 open c;
 loop
 fetch c into tem;
 exit when c%notfound;

 insert into temp 
 values(tem.email, tem.collegename, tem.cgpa, tem.compname);
 end loop;
 end;
/

Edit:

If you set the type to stored procedure, all you need is the name of the stored procedure. So commandtext should be cv rather than exec cv.

-edit-

While the thing above is an issue as well, the screenshot you posted in your update suggests that the connection is not opened yet when you call ExecuteNonQuery.

Or maybe it is opened, but not assigned to the command. Since an application could theoretically have multiple connections, you have to tell the command which one to use. There is no global reference to the connection that the command will use implicitly. The example given in Oracle's documentation shows how this could be done through a property, although apparently you can also set it in the constructor, which you do for connections 1 and 3 but not for connection 2. I guess there is the problem. :o)

Your approach is not really the "direct path", I would do it like this. Either select the table directly:

var s1 = "select email, collegename, cgpa, compname from student_cv 
 where (cgpa >= :x and yearsofexp >= :y) and compname = :z";
OracleCommand comm = new OracleCommand(s1, conn);
comm.Parameters.Add("x", OracleType.Number).Value = comboBox1.Text;
comm.Parameters.Add("y", OracleType.Number).Value = comboBox2.Text;
comm.Parameters.Add("z", OracleType.VarChar2).Value = "some text";

OracleDataAdapter da = new OracleDataAdapter(comm);
DataTable dt = new DataTable();
da.Fill(dt);
dataGridView1.DataSource = dt;

Instead OracleDataAdapter and OracleDataAdapter you can also use OracleDataReader:

OracleDataReader dr = comm.ExecuteReader();
while ( dr.Read() ) 
 // loop through the rows and process the rows one-by-one

dr.close();

The other way is to use a function or procedure like this:

create or replace FUNCTION cv (x in int, y in int, z in varchar2) RETURN SYS_REFCURSOR as
 res SYS_REFCURSOR;
BEGIN
 OPEN res FOR
 select email, collegename, cgpa, compname
 from student_cv
 where (cgpa >= x and yearsofexp >= y) and compname = z;

 RETURN res;
end;

And then call it in C# as this:

comm.CommandType = CommandType.Text;
comm.CommandText = "BEGIN :ret := cv(:x, :y, :z); END;";
comm.Parameters.Add("x", OracleType.Number).Value = comboBox1.Text;
comm.Parameters.Add("y", OracleType.Number).Value = comboBox2.Text;
comm.Parameters.Add("z", OracleType.VarChar2).Value = "some text";
comm.Parameters.Add("ret", OracleDbType.RefCursor, ParameterDirection.ReturnValue);

OracleDataAdapter da = new OracleDataAdapter(comm);
DataTable dt = new DataTable();
da.Fill(dt);
dataGridView1.DataSource = dt;

Of course, you can also use OracleDataReader like above.

Note, I don't have a computer in front of me for any testing, so please forgive me if I did one or two typos in the code examples.

Regarding your actual error message, ensure the correct order:

1. open the connection


2. execute the command (or several commands)


3. close the connection

As OracleConnection implements IDisposal you should enclose it by using like this

using (var conn = new OracleConnection() ) 
 conn.ConnectionString = "Data Source=...";
 conn.Open();
 // Code from above

 conn.Close();

OracleDataReader and OracleDataAdapter also implements IDisposal, so is should be also enclosed by using

You may need a commit; before end; ?