Home » Open Source » Programming Interfaces » How do I Pass Params and Call A Simple PL/SQL Query from C#.NET 2.0? (Oracle 10g, Microsoft Windows XP Professional)
How do I Pass Params and Call A Simple PL/SQL Query from C#.NET 2.0? [message #293022] Thu, 10 January 2008 11:23
mftepera
Messages: 1
Registered: January 2008
Junior Member
I have searched for documentation on the Oracle site, .NET help, etc., and have not been able to find out:

How do I call this PL/SQL from .NET? Below is the PL/SQL:

column disp_name format a30 trunc
compute sum of net_acres on report
break on report

select disp_name, wi_type, net_acres, wi_pct, lease_status, exp_date
from report_output
where user_timestamp = '&user_timestamp'
order by wi_type desc, wi_name, nvl(add_name,' ')
/

Here is the C# that is failing:

_conn.OpenConnection();

string cmdText1 = "column disp_name format a30 trunc";
string cmdText2 = "compute sum of net_acres on report";
string cmdText3 = "break on report";

string select = "disp_name, wi_type, net_acres, wi_pct, lease_status, exp_date";
string table = "REPORT_OUTPUT";
string whereClause = "user_timestamp = '" + _timestamp + "'";
string orderBy = "wi_type desc, wi_name, nvl(add_name,' ')";

string sql = "SELECT " + select + " FROM " + table;
if (whereClause != string.Empty)
{
sql += " WHERE " + whereClause;
}
if (orderBy != string.Empty)
{
sql += " ORDER BY " + orderBy;
}

OracleCommand cmd = _conn.Connection.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = cmdText1 + ";" + cmdText2 + ";" + cmdText3 + ";" + sql;
OracleDataAdapter adapter = new OracleDataAdapter(cmd.CommandText, _conn.Connection);
DataTable dataTable = new DataTable(_reportType);
adapter.Fill(dataTable);

Any suggestions would be greatly appreciated.

[Updated on: Thu, 10 January 2008 12:43]

Report message to a moderator

Previous Topic: Connect to oracle
Next Topic: Problem Connection to 10g from .Net Service
Goto Forum:
  


Current Time: Fri Mar 29 07:11:26 CDT 2024