using ORACLE stored procedure in ASP.NET 2.0

Discussion in 'ASP .Net' started by george, Oct 25, 2006.

  1. george

    george Guest

    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
     
    george, Oct 25, 2006
    #1
    1. Advertisements

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 (here). After that, you can post your question and our members will help you out.