User269602965 posted
Examples: Calling PL/SQL to get REFCURSOR back into a .NET data structure
/* VB.NET code in ASP.NET application */
' Get data from stored procedure '
Try
Dim connstr As String = ConfigurationManager.ConnectionStrings("{OracleConnectionStringNameFromWebConfig}").ConnectionString
Using conn As New OracleConnection(connstr)
Using cmd As New OracleCommand("{SCHEMANAME}.{PACKAGENAME}.rcGET_BIRD_DATA", conn)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Clear()
cmd.Parameters.Add("p_DATE_BAND", OracleDbType.Date, ParameterDirection.Input)
cmd.Parameters.Add("p_AOU_CODE", OracleDbType.Decimal, ParameterDirection.Input)
cmd.Parameters.Add("p_BIRD_CLASS", OracleDbType.Varchar2, ParameterDirection.Input)
cmd.Parameters.Add("ret_REFCUR", OracleDbType.RefCursor, ParameterDirection.Output)
conn.Open()
Using oda As New OracleDataAdapter(cmd)
Dim ds As New DataSet()
oda.Fill(ds)
Me.RadGrid1.MasterTableView.DataSource = ds.Tables(0)
End Using
End Using
End Using
Catch ex As Exception
End Try
/* CS.NET code in ASP.NET application */
' Get data from stored procedure '
try
{
string connstr = ConfigurationManager.ConnectionStrings("{OracleConnectionStringNameFromWebConfig}").ConnectionString;
using (OracleConnection conn = new OracleConnection(connstr))
{
using (OracleCommand cmd = new OracleCommand("{SCHEMANAME}.{PACKAGENAME}.rcGET_BIRD_DATA", conn))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Clear();
cmd.Parameters.Add("p_DATE_BAND", OracleDbType.Date, ParameterDirection.Input);
cmd.Parameters.Add("p_AOU_CODE", OracleDbType.Decimal, ParameterDirection.Input);
cmd.Parameters.Add("p_BIRD_CLASS", OracleDbType.Varchar2, ParameterDirection.Input);
cmd.Parameters.Add("ret_REFCUR", 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}.{PACKAGENAME}
AS
PROCEDURE rcGET_BIRD_DATA(p_DATE_BAND IN DATE
,p_AOU_CODE IN NUMBER
,p_BIRD_CLASS IN VARCHAR2
,ret_REFCUR OUT SYS_REFCURSOR);
END PKG_BIRDS;
/
CREATE OR REPLACE PACKAGE BODY {SCHEMANAME}.{PACKAGENAME}
AS
PROCEDURE rcGET_BIRD_DATA(p_DATE_BAND IN DATE
,p_AOU_CODE IN NUMBER
,p_BIRD_CLASS IN VARCHAR2
,ret_REFCUR OUT SYS_REFCURSOR)
BEGIN
OPEN ret_REFCUR FOR
SELECT
AOU_CODE,
BIRD_CLASS,
BIRD_NAME,
DATE_BAND,
WING_LENGTH,
TAIL_LENGTH,
EYE_COLOR,
PLUMAGE_CODE
FROM
{SCHEMANAME}BANDED_BIRDS
WHERE
DATE_BAND > p_DATE_BAND
AND
AOU_CODE = p_AOU_CODE
AND
BIRD_CLASS = p_BIRD_CLASS
END;
END {SCHEMANAME}.{PACKAGENAME};
/
Example 2: call the database with .NET side SQL
Imports System.Xml.Linq.XElement
Public Shared Sub updateUnitsActiveFlag(ByVal decQuantity As Decimal, ByVal strDescription As String, ByVal dateDateClosed As DateTime)
' Insert Quantity into new row Units table'
Dim OraConnStr As String = ConfigurationManager.ConnectionStrings("OraConnStr").ConnectionString
Try
Dim SQL =
<SQL>
INSERT INTO {YOURSCHEMANAME}.UNITS
(UNITS_SEQ, QUANTITY, DESCRIPTION, DATE_CLOSED, DATE_TODAY)
VALUES
(UNIT_SEQ.NextVal, :BindVarQuantity, :BindVarDescription, :BindVarDateClosed, SYSDATE)
</SQL>
Using conn As New OracleConnection(OraConnStr)
Using cmd As New OracleCommand(SQL.Value, conn)
cmd.Parameters.Clear()
cmd.Parameters.Add("BindVarQuantity", OracleDbType.Decimal, decQuantity, ParameterDirection.Input)
cmd.Parameters.Add("BindVarDescription", OracleDbType.CLOB, strDescription, ParameterDirection.Input)
cmd.Parameters.Add("BindVarDateClosed", OracleDbType.Date, dateDateClosed, ParameterDirection.Input)
conn.Open()
cmd.ExecuteNonQuery()
End Using
End Using
Catch ex As Exception
AppCalls.WriteToEventLog(ex, "Inserting UNITS.QUANTITY failed", "AppCalls.updateUnitsActiveFlag.vb")
End Try
End Sub
Imports System.Xml.Linq.XElement
Public Shared Sub InsertIntoTest(ByVal strID As String, ByVal strPerson As String, ByVal strLocation As String)
Dim OraConnStr As String = ConfigurationManager.ConnectionStrings("{YourOraConnStrName}").ConnectionString
Try
Dim SQL =
<SQL>
INSERT INTO {YourSchemaName}.TEST
(ID, Person, Location)
VALUES
(:BindVarstrID, :BindVarstrPerson, :BindVarstrLocation)
</SQL>
Using conn As New OracleConnection(OraConnStr)
Using cmd As New OracleCommand(SQL.Value, conn)
cmd.Parameters.Clear()
cmd.Parameters.Add("BindVarstrID", OracleDbType.Varchar2, strID, ParameterDirection.Input)
cmd.Parameters.Add("BindVarstrPerson", OracleDbType.Varchar2, strPerson, ParameterDirection.Input)
cmd.Parameters.Add("BindVarstrLocation", OracleDbType.Varchar2, strLocation, ParameterDirection.Input)
conn.Open()
cmd.ExecuteNonQuery()
End Using
End Using
Catch ex As Exception
End Try
End Sub
