User269602965 posted
get bird information from two tables in Oracle and put data in a gridview that can be displayed in a web page.
However, if Lucence.net is a HOSTED web site, it may not support calling a REMOTE Oracle database outside the hosted site.
Or your Oracle environment may not allow connections to a third party Hosted web site due to security concerns.
CS.NET code in ASP.NET application
// Get data from stored procedure
{
try {
string strGroup = "DUCK";
string connstr = ConfigurationManager.ConnectionStrings("{OracleConnectionStringNameFromWebConfig}").ConnectionString;
using (OracleConnection conn = new OracleConnection(connstr)) {
using (OracleCommand cmd = new OracleCommand("{SCHEMANAME}.getBirdInfo.rcSelectBirdNames", conn)) {
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Clear();
cmd.Parameters.Add("bv_BirdGroup", OracleDbType.Varchar2, strGroup, ParameterDirection.Input);
cmd.Parameters.Add("ListBirdNames", OracleDbType.RefCursor, ParameterDirection.Output);
conn.Open();
using (OracleDataAdapter oda = new OracleDataAdapter(cmd)) {
DataSet ds = new DataSet();
oda.Fill(ds);
this.RadGrid1.MasterTableView.DataSource = ds.Tables[0];
}
}
}
} catch (Exception ex) {
}
}
/**************************************/
/* Oracle PL/SQL code */
CREATE OR REPLACE PACKAGE {SCHEMANAME}.getBirdInfo AS TYPE refCursor IS REF CURSOR;
PROCEDURE rcSelectBirdNames(bv_BirdGroup IN Varchar2, ListBirdNames OUT refCursor);
END getBirdInfo;
/
CREATE OR REPLACE PACKAGE BODY getBirdInfo AS
PROCEDURE rcSelectBirdNames(bv_BirdGroup IN Varchar2, ListBirdNames OUT refCursor)
IS
BEGIN
OPEN ListBirdNames FOR
SELECT a.BIRD_GROUP, a.AOU_NUMBER, a.BIRD_NAME_KEY, b.BIRD_NAME
FROM {SCHEMANAME}.BIRDS a
INNER JOIN LKUP_BIRD_NAME b ON a.BIRD_NAME_KEY = b.BIRD_NAME_KEY
WHERE a.BIRD_GROUP = :bv_BirdGroup;
END;
END getBirdInfo;
/
