Oracle SQL parameter not bounding properly

Question

So my situation simply includes a data source and an Oracle SQL query in ASP.NET.

I have the following data source defined in the aspx.

<asp:SqlDataSource ID="DataSourcePFEP" runat="server"
    ConnectionString="<%$ ConnectionStrings:DALI %>" ProviderName="Oracle.ManagedDataAccess.Client" CancelSelectOnNullParameter="false">
    <SelectParameters>
        <asp:SessionParameter Name="PlantID" SessionField="PlantID" />
        <asp:QueryStringParameter Name="ProductionLine" QueryStringField="Line" ConvertEmptyStringToNull="true" />
    </SelectParameters>
</asp:SqlDataSource>

I also have a query, containing the following WHERE clause:

(not including the full query, because it's long and works perfectly without this clause or with :ProductionLine replaced with any string)

(:ProductionLine IS NULL OR PKHD.PRVBE = :ProductionLine)

My first issue is that this WHERE clause does not seem to work when there is no query string parameter present. It works if the query parameter is set like "?Line=Something", but with no query parameter I get no results at all. But isn't the "OR :ProductionLine IS NULL" part supposed to take care of that?

Another issue I have is that my full query includes another parameter, :PlantID. You can see it already defined in the SelectParameters section of my SqlDataSource. And if I use both parameters in my query, I get a ORA-01008 error saying I don't have all values bound. But if only one parameter is included, then both work one by one. So clearly both parameters bound properly, but if I use both at the same time, then it suddenly does not work?

Answer 1

Well, I got it to work, but in a quite weird manner.

If I use both a QueryStringParameter and a SessionParameter, then it works "properly".

<asp:SqlDataSource ID="DataSourcePFEP" runat="server"
ConnectionString="<%$ ConnectionStrings:DALI %>" ProviderName="Oracle.ManagedDataAccess.Client" CancelSelectOnNullParameter="false">
<SelectParameters>
    <asp:SessionParameter Name="PlantID" SessionField="PlantID" />
    <asp:QueryStringParameter Name="ProductionLine" QueryStringField="Line" ConvertEmptyStringToNull="true" />
    <asp:SessionParameter Name="ProductionLine" SessionField="ProductionLine" />
</SelectParameters>
</asp:SqlDataSource>

For this I need to set the Session["ProductionLine"] to be what the query string is on PageLoad. But if I only use SessionParameter, then it also won't work. For some reason if I use both, then it somehow works.

I'm not sure why, seems like a wacky workaround, but just leaving it here. Maybe someone can use it to figure out the real issue or at least use it to get around it like I do.