call oracle view from asp.net?

Question

How can i call oracle 10g view from asp.net page?

View "DEPARTMENTS_VIEW" is

select Department_ID, DEPARTMEN_NAME , DISCOUNT    from DEPARTMENTS

c# code is

    OracleConnection conn = new OracleConnection();
    conn.ConnectionString = txtconnection.Text;
    conn.Open();
    OracleCommand objCmd = new OracleCommand();
    objCmd.Connection = conn;
    objCmd.CommandType = CommandType.StoredProcedure;
    objCmd.CommandText = "DEPARTMENTS_VIEW";
  
    DataSet ds = new DataSet();
    OracleDataAdapter oraDa = new OracleDataAdapter(objCmd);
    oraDa.Fill(ds, "department");
    gvGET_DEPARTMENTS.DataSource = ds.Tables["department"];
    gvGET_DEPARTMENTS.DataBind();
    conn.Close();

I'm getting this error code:

ORA-06550: line 1, column 7:

PLS-00221: 'DEPARTMENTS_VIEW' is not a procedure or is undefined

ORA-06550: line 1, column 7:

PL/SQL: Statement ignored

Stored procedure and View are different things. You can't call a view like that. Read: http://stackoverflow.com/questions/5194995/what-is-the-difference-between-a-stored-procedure-and-a-view and http://stackoverflow.com/questions/3773277/stored-procedures-vs-views — Soner Gönül, Feb 22 '15 at 11:53

score 1 · Accepted Answer · answered Feb 22 '15 at 12:22

1

View is meant to be used the same way a table is used:

OracleConnection conn = new OracleConnection();
conn.ConnectionString = txtconnection.Text;
conn.Open();
OracleCommand objCmd = new OracleCommand();
objCmd.Connection = conn;
objCmd.CommandType = CommandType.StoredProcedure;
objCmd.CommandText = "SELECT * FROM DEPARTMENTS_VIEW";

DataSet ds = new DataSet();
OracleDataAdapter oraDa = new OracleDataAdapter(objCmd);
oraDa.Fill(ds, "department");
gvGET_DEPARTMENTS.DataSource = ds.Tables["department"];
gvGET_DEPARTMENTS.DataBind();
conn.Close();

You select data from a view the same way you select data from a table.

answered Feb 22 '15 at 12:22

Dn24Z

149
5

Thank you for your replay, what about Synonym , i allways get this error message PLS-00428: an INTO clause is expected in this SELECT statement – user2374513 Feb 22 '15 at 15:53
@user2374513 - what synonym are you talking about? – Bob Jarvis - Слава Україні Feb 22 '15 at 20:13
@user2374513 Use of Synonyms depends on the type of object synonym is created. If it's a synonym function you call it as a function, if it's a synonym table you select the data the same way you select data from a table. – Dn24Z Feb 22 '15 at 21:49

score 0 · Answer 2 · answered Sep 26 '18 at 15:26

I dont have a enough reputation to comment on Dn24Z's answer but the answer should be:

OracleConnection conn = new OracleConnection();
conn.ConnectionString = txtconnection.Text;

conn.Open();
OracleCommand objCmd = new OracleCommand();
objCmd.Connection = conn;
objCmd.CommandType = CommandType.Text;
objCmd.CommandText = "SELECT * FROM DEPARTMENTS_VIEW";

DataSet ds = new DataSet();
OracleDataAdapter oraDa = new OracleDataAdapter(objCmd);
oraDa.Fill(ds, "department");
gvGET_DEPARTMENTS.DataSource = ds.Tables["department"];
gvGET_DEPARTMENTS.DataBind();
conn.Close();

The key difference is that the objCmd.CommandType SHOULD BE objCmd.CommandType = CommandType.Text;

call oracle view from asp.net?

2 Answers2