Calling Oracle stored procedures from SQLDataSource

For some simple pages in my application I prefer to use declarative manner of programming. To display some data on the page you can use this lines of code.

<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ProviderName="System.Data.OracleClient"
    SelectCommand="pac_dwh.Revew_item_info.GetAppPermissionInfoExt"
    SelectCommandType="StoredProcedure">
    <SelectParameters>
        <asp:QueryStringParameter Name="p_line_id"
                  QueryStringField="lineId" />
        <asp:Parameter Name="p_res" Direction="Output" />
    </SelectParameters>
</asp:SqlDataSource>
<asp:FormView ID="FormViewContent" runat="server"
          DataSourceID="SqlDataSource1" >
        <ItemTemplate>
            <%# Eval("profile") %>
            <%# Eval("application_description") %>
        </ItemTemplate>
</asp:FormView>

But if you are using Oracle database you will get the error

System.Exception: Parameter 'p_res': No size set for variable length data type: String.

It’s because you can’t define type of parameter in DataSource, because it’s a Cursor, and Direction has no this type of data.

The solution is very simple, you just need to set datatype for the output parameter in OnSelecting event, like this:

<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ProviderName="System.Data.OracleClient"
SelectCommand="pac_dwh.Revew_item_info.GetAppPermissionInfoExt"
SelectCommandType="StoredProcedure"
OnSelecting="FixOracleRefCursorProblem_SqlDataSource1_Selecting">
    <SelectParameters>
        <asp:QueryStringParameter Name="p_line_id"
                  QueryStringField="lineId" />
        <asp:Parameter Name="p_res" Direction="Output" />
    </SelectParameters>
</asp:SqlDataSource>

and in cs file:

protected void FixOracleRefCursorProblem_SqlDataSource1_Selecting(object sender,
   SqlDataSourceSelectingEventArgs e)
{
    ((System.Data.OracleClient.OracleParameter)
    e.Command.Parameters[1]).OracleType = OracleType.Cursor;
}

And now it works :)