I have a oracle procedure that i would like to use in my asp.net page. This procedure has one parameter and the parameter accepts more than one value. my problem is how can I embede this procdure in my asp.net page. I do not have a problme if this procedure
takes one value. but in this case it takes more than one value. if someone has an idea how this can be done i really appreciate that.
<div id=code>
CODE
<div>procedure:
CREATE OR REPLACE PROCEDURE planholderslist (
p_letting_date IN string_table,
p_results OUT sys_refcursor
)
AS
BEGIN
OPEN p_results FOR
SELECT DISTINCT DECODE (TRIM (MIN (j.route)),
NULL, 'N/A',
TRIM (MIN (j.route))
) routenumber,
l.lcontid contractid, (q.cdescr) jobdescription,
INITCAP (q.clocat1 || q.clocat2) LOCATION,
SUBSTR (q.cprojnum, 1, 10) projectnumber,
SUBSTR (l.letting, 3, 2)
|| '-'
|| SUBSTR (l.letting, 5, 2)
|| '-'
|| SUBSTR (l.letting, 1, 2) lettingdate
FROM vendor v,
vendaddr r,
letprop l,
planhold p,
proposal q,
project j,
propproj k,
bidlet bd
WHERE v.vendor = r.vendor
AND k.contid = q.contid
AND k.pcn = j.pcn
AND l.lcontid = k.contid
AND p.vendor = v.vendor
AND l.letting = p.letting
AND (TO_CHAR (bd.datelet, 'MM/DD/YYYY') IN (
SELECT COLUMN_VALUE
FROM TABLE
(p_letting_date))
)
AND l.CALL = p.CALL
AND r.addrnum = p.billto
AND bd.letting = l.letting
GROUP BY q.cdescr,
q.clocat1,
q.clocat2,
bd.letting,
l.letting,
l.lcontid,
q.cprojnum;
END planholderslist;</div></div>
<div id=code>
CODE
<div> Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
BindLetData()
End Sub
Sub BindLetData()
Dim connectionString As String = ConnectionStrings("costEstimating1").ConnectionString
Dim oOracleConn As OracleConnection = New OracleConnection(connectionString)
oOracleConn.Open()
Dim drLettingData As OracleDataReader
Dim cmdLetting As OracleCommand = New OracleCommand()
With cmdLetting
.Connection = oOracleConn
.CommandText = "planholderslist"
.CommandType = CommandType.StoredProcedure
.Parameters.Clear()
.Parameters.Add(New OracleParameter("p_letting_date", OracleType.VarChar)).Value = ("'07/28/2000','05/26/2000'")----this is were I am confuesed how to pass multiple values
.Parameters.Add(New OracleParameter("p_results", OracleType.Cursor)).Direction = ParameterDirection.Output
End With
drLettingData = cmdLetting.ExecuteReader()
gvPhllist.DataSource = drLettingData
gvPhllist.DataBind()
drLettingData.Close()
End Sub</div></div>
Member
10 Points
21 Posts
ASP.NET AND ORACLE PROCEDURE
May 12, 2010 02:13 PM|yosef2005|LINK
I have a oracle procedure that i would like to use in my asp.net page. This procedure has one parameter and the parameter accepts more than one value. my problem is how can I embede this procdure in my asp.net page. I do not have a problme if this procedure takes one value. but in this case it takes more than one value. if someone has an idea how this can be done i really appreciate that.
<div id=code>CODE
<div>procedure:CREATE OR REPLACE PROCEDURE planholderslist (
p_letting_date IN string_table,
p_results OUT sys_refcursor
)
AS
BEGIN
OPEN p_results FOR
SELECT DISTINCT DECODE (TRIM (MIN (j.route)),
NULL, 'N/A',
TRIM (MIN (j.route))
) routenumber,
l.lcontid contractid, (q.cdescr) jobdescription,
INITCAP (q.clocat1 || q.clocat2) LOCATION,
SUBSTR (q.cprojnum, 1, 10) projectnumber,
SUBSTR (l.letting, 3, 2)
|| '-'
|| SUBSTR (l.letting, 5, 2)
|| '-'
|| SUBSTR (l.letting, 1, 2) lettingdate
FROM vendor v,
vendaddr r,
letprop l,
planhold p,
proposal q,
project j,
propproj k,
bidlet bd
WHERE v.vendor = r.vendor
AND k.contid = q.contid
AND k.pcn = j.pcn
AND l.lcontid = k.contid
AND p.vendor = v.vendor
AND l.letting = p.letting
AND (TO_CHAR (bd.datelet, 'MM/DD/YYYY') IN (
SELECT COLUMN_VALUE
FROM TABLE
(p_letting_date))
)
AND l.CALL = p.CALL
AND r.addrnum = p.billto
AND bd.letting = l.letting
GROUP BY q.cdescr,
q.clocat1,
q.clocat2,
bd.letting,
l.letting,
l.lcontid,
q.cprojnum;
END planholderslist;</div></div>
<div id=code>CODE
<div> Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.LoadBindLetData()
End Sub
Sub BindLetData()
Dim connectionString As String = ConnectionStrings("costEstimating1").ConnectionString
Dim oOracleConn As OracleConnection = New OracleConnection(connectionString)
oOracleConn.Open()
Dim drLettingData As OracleDataReader
Dim cmdLetting As OracleCommand = New OracleCommand()
With cmdLetting
.Connection = oOracleConn
.CommandText = "planholderslist"
.CommandType = CommandType.StoredProcedure
.Parameters.Clear()
.Parameters.Add(New OracleParameter("p_letting_date", OracleType.VarChar)).Value = ("'07/28/2000','05/26/2000'")----this is were I am confuesed how to pass multiple values
.Parameters.Add(New OracleParameter("p_results", OracleType.Cursor)).Direction = ParameterDirection.Output
End With
drLettingData = cmdLetting.ExecuteReader()
gvPhllist.DataSource = drLettingData
gvPhllist.DataBind()
drLettingData.Close()
End Sub</div></div>
Contributor
2594 Points
578 Posts
Re: ASP.NET AND ORACLE PROCEDURE
May 13, 2010 02:36 AM|Jalpesh P. Vadgama|LINK
HI i think you can string variable in asp.net and pass comma seperated list.
Cheers,
Microsoft MVP(Visual C#),Computer Geek,Lifelong Learner
My Technology Blog : www.dotnetjalps.com
Mark as answer if my anwers helps you