Search code examples
oraclestored-proceduressqldatasource

Login Stored Procedure in Oracle | Error: PLS-00306

I'm calling a stored procedure in a package from an ASP.NET SqlDataSource however I keep getting the following error. 

ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'VALIDATE_USER_PRC'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

My Stored procedure looks like:

PROCEDURE VALIDATE_USER_PRC(P_APPLICATIONNAME IN TBL_USERS.APPLICATIONNAME%TYPE,
P_USERNAME IN TBL_USERS.USERNAME%TYPE,
P_PASSWORD IN TBL_USERS.USERPASSWORD%TYPE,
P_RESULTS OUT NUMBER)
IS
BEGIN
SELECT COUNT(*) INTO P_RESULTS FROM TBL_USERS WHERE USERNAME=P_USERNAME
AND (USERPASSWORD=P_PASSWORD)
AND APPLICATIONNAME=P_APPLICATIONNAME;
END VALIDATE_USER_PRC;

It's declared in my package PARENT_USER_API.

  • TBL_USERS.APPLICATIONNAME%TYPE = VARCHAR2(255)
  • TBL_USERS.USERNAME%TYPE = VARCHAR2(255)
  • TBL_USERS.USERPASSWORD%TYPE = VARCHAR2(128)

Finally my ASP.NET code looks as such:

<asp:SqlDataSource ID="dsLogin" runat="server" CancelSelectOnNullParameter="false"
   ConnectionString="<%$ ConnectionStrings:ConnectionString %>" 
   ProviderName="<%$ ConnectionStrings:ConnectionString.ProviderName %>" 
   SelectCommand="PARENT_USER_API.VALIDATE_USER_PRC" SelectCommandType="StoredProcedure">
   <SelectParameters>     
      <asp:Parameter Name="P_APPLICATIONNAME" DefaultValue="/PortailParent" Type="String" />
      <asp:ControlParameter ControlID="txtUser" Name="P_USERNAME" PropertyName="Text"  Type="String" />
      <asp:ControlParameter ControlID="txtPass" Name="P_USERPASSWORD" PropertyName="Text"  Type="String" />
      <asp:Parameter Name="P_RESULTS" Direction="Output" Type="Int32" />
   </SelectParameters>
</asp:SqlDataSource>

My reasoning for using R_RESULTS is to see if any rows are returned with the given username and password in order to validate the user who's trying to login.

Can anyone tell me what I'm doing wrong? I've searched for hours and tried several things with no luck as of yet. Thanks a lot!

Solution

  • Solved!

    Embarrassingly the answer to my issue was in the SelectParameters of my SqlDataSource.

    <asp:ControlParameter ControlID="txtPass" Name="P_USERPASSWORD" 
     PropertyName="Text" Type="String" />

    The name I provided was P_USERPASSWORD instead of simply P_PASSWORD.

    Evidently I forgot to change it after switching my SqlDataSource which was iniatally programmed with my select statement in the SelectCommand to it's stored procedure counterpart.