using ORACLE stored procedure in ASP.NET 2.0

G

george

Hi world!

Anyone experienced building gridview in Oracle environment using stored
procedures, please help!

Right now, I have issues using Oracle SP, I spent a whole day on this
and still cannot get the results back, so shamelessly turn to you for
help!
OK, the web page part of SqlDataSource is defined as:
<asp:SqlDataSource ID="SqlDataSource4" runat="server"
ConnectionString="<%$ ConnectionStrings:ConnectionString1 %>"
ProviderName="<%$
ConnectionStrings:ConnectionString1.ProviderName %>"
SelectCommand='CORE_SERVER.SP_Get_Vols'
SelectCommandType="StoredProcedure">
<SelectParameters>
<asp:parameter Name="xID" DefaultValue="2"
Type="String" />
<asp:parameter Name="xFromDate"
DefaultValue="1-Jun-2006" Type="Datetime" />
<asp:parameter Name="xToDate"
DefaultValue="2-Jun-2006" Type="Datetime" />
<asp:parameter Name="xTenorDate"
DefaultValue="3M" Type="String" />
<asp:parameter Name="resultSet"
Direction="Output" Type="???" />
</SelectParameters>
</asp:SqlDataSource>

Part of SP in ORACLE is defined as below (and I have another .NET
webservice application using the same sp, and it is working, so I
assume that the sp should be ok).

CORE_SERVER PACKAGE 3 TYPE ssATMVolType IS RECORD
(ric eod_atm_vol.ric%TYPE, market_date eod_atm_vol.ric%TYPE, vol
eod_atm_vol.vol_1m%TYPE);


CORE_SERVER PACKAGE 4 TYPE ssATMVolCursor IS REF
CURSOR RETURN ssATMVolType;



CORE_SERVER PACKAGE 7 PROCEDURE SP_Get_Vols(xID IN
VARCHAR2, xFromDate IN DATE, xToDate IN DATE, xTenorDate in varchar2,
resultSet OUT SSAtmVolCursor);
....
....


Since the resultSet in SP is a REF CURSOR, I am not sure what Type I
should put there in .aspx file. there is no "Cursor" type in ASP.NET
2.0.. If nothing about Type is put there, it will complain:

[Exception: Parameter 'resultSet': No size set for variable length data
type: String.]

System.Data.OracleClient.OracleParameterBinding.PrepareForBind(OracleConnection
connection, Int32& offset) +956
System.Data.OracleClient.OracleCommand.Execute(OciStatementHandle
statementHandle, CommandBehavior behavior, Boolean needRowid,
OciRowidDescriptor& rowidDescriptor, ArrayList&
resultParameterOrdinals) +1264
System.Data.OracleClient.OracleCommand.Execute(OciStatementHandle
statementHandle, CommandBehavior behavior, ArrayList&
resultParameterOrdinals) +50
System.Data.OracleClient.OracleCommand.ExecuteReader(CommandBehavior
behavior) +174

System.Data.OracleClient.OracleCommand.ExecuteDbDataReader(CommandBehavior
behavior) +29

System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior
behavior) +32
System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset,
DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String
srcTable, IDbCommand command, CommandBehavior behavior) +183
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32
startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
CommandBehavior behavior) +307
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String
srcTable) +152

System.Web.UI.WebControls.SqlDataSourceView.ExecuteSelect(DataSourceSelectArguments
arguments) +2868
System.Web.UI.DataSourceView.Select(DataSourceSelectArguments
arguments, DataSourceViewSelectCallback callback) +84
System.Web.UI.WebControls.DataBoundControl.PerformSelect() +154
System.Web.UI.WebControls.BaseDataBoundControl.DataBind() +99
System.Web.UI.WebControls.GridView.DataBind() +24
System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound() +92

System.Web.UI.WebControls.CompositeDataBoundControl.CreateChildControls()
+100
System.Web.UI.Control.EnsureChildControls() +134
System.Web.UI.Control.PreRenderRecursiveInternal() +109
System.Web.UI.Control.PreRenderRecursiveInternal() +233
System.Web.UI.Control.PreRenderRecursiveInternal() +233
System.Web.UI.Control.PreRenderRecursiveInternal() +233
System.Web.UI.Control.PreRenderRecursiveInternal() +233
System.Web.UI.Control.PreRenderRecursiveInternal() +233
System.Web.UI.Control.PreRenderRecursiveInternal() +233
System.Web.UI.Page.ProcessRequestMain(Boolean
includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
+4437


and I then tried Direction="InputOutput" for the resultSet, there is no
error but there is nothing coming back (but the sp should bring me some
data).

Any ideas? I would appreciate if some one give me pointers with some
sample examples using ORACLE stored procedures in ASP.NET 2.0 web page
(in asp:SqlDataSoure)?

thanks,
George
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

Forum statistics

Threads
473,744
Messages
2,569,484
Members
44,905
Latest member
Kristy_Poole

Latest Threads

Top