1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

problem with authentication

Discussion in 'SQL PL/SQL' started by krizza69, Jun 21, 2010.

  1. krizza69

    krizza69 Guest


    i have a problem with the authentication of my page on "Oracle APEX".
    Every time I want to register a new registered user to me, the programm shows me an error.

    Here is my authentication:
    Code (SQL):

    CREATE OR REPLACE FUNCTION custom_auth (p_username IN VARCHAR2, p_password IN VARCHAR2)
      l_password varchar2(4000);
      l_stored_password varchar2(4000);
       l_count NUMBER;
    SELECT COUNT(*) INTO l_count FROM userdaten WHERE username = p_username;
    IF l_count > 0 THEN
      SELECT passwort INTO l_stored_password
       FROM userdaten WHERE username = p_username;
       l_password := p_password;
        IF l_stored_password = p_password THEN
          RETURN TRUE;
          RETURN FALSE;
        END IF;
    END IF;
    This error occurs when logging a user on: Invalid Login Credentials.
    But if I go through the commands in SQL, the continuous test works very well which I have listed above.
    What could be the problem?

    Is there anybody, who can help me to find a solution?
  2. zargon

    zargon Community Moderator Forum Guru

    Likes Received:
    Trophy Points:
    Aurora, CO
    You're expecting to find more than one user with a specific username? Your code fails when l_count >= 2 as the select ... into returns an error:

    Code (SQL):
    ERROR at line 1:
    ORA-01422: exact fetch RETURNS more than requested NUMBER OF ROWS
    ORA-06512: at line 4
    You should be looking for l_count = 1 and processing an exception for the ORA-01422. Next, how, exactly, is the value in passwort stored? Plain text? That's a security hole you could drive a truck through, in my opinion. You also did not set l_password to null and you should; variables in PL/SQL are not initialized to NULL automatically, which can present problems later on. What does l_password bring to this function? I see it assigned a value then never used again; why is that?

    What is the exact error number you receive when this occurs? Posting only part of the error message doesn't provide much information for those of us attempting to assist you. Please post the entire error message (or error stack if more than one message appears) so we can better understand your problem and possibly provide a workable solution.