What I am doing wrong in this procedure

Question

I have created procedure to check and validate username and password, even when I provide correct password I will receive always exception error. I tried different thing inside the procedure but results would be the same.

create or replace 
PROCEDURE member_ck_sp
  (p_uname IN VARCHAR2,
   p_pass IN VARCHAR2,
   p_name OUT VARCHAR2,
   p_cookie OUT VARCHAR2)
IS
   CURSOR CUR_CHECK IS
   SELECT USERNAME, PASSWORD,FIRSTNAME||''||LASTNAME, COOKIE
   FROM bb_shopper;
   lv_check_txt VARCHAR2(100);
BEGIN
   FOR rec_check IN cur_check LOOP
   IF p_uname = rec_check.username 
      AND p_pass = rec_check.PASSWORD THEN
   lv_check_txt := 'Pass';
   ELSE lv_check_txt := 'Fail';
   END IF;
   END LOOP;

 IF lv_check_txt = 'Pass' THEN
    SELECT FIRSTNAME||''||LASTNAME, COOKIE
    INTO   p_name, p_cookie
    FROM   bb_shopper
    WHERE USERNAME = P_UNAME
    AND   password = p_pass;
    dbms_output.put_line(p_name||' '|| p_cookie);
 ELSE raise no_data_found;
 END IF;
--dbms_output.put_line(p_name||' '|| p_cookie);
EXCEPTION
  WHEN no_data_found THEN
  dbms_output.put_line('Please reneter credentials');
END;

And block to check code:

DECLARE
    lv_username_txt bb_shopper.username%TYPE := 'rat55';
    lv_password_txt bb_shopper.PASSWORD%TYPE := 'kile';
    lv_name_txt VARCHAR2(200);
    lv_cookie_txt bb_shopper.cookie%TYPE;
BEGIN
    member_ck_sp(lv_username_txt,lv_password_txt,lv_name_txt,lv_cookie_txt);
    --DBMS_OUTPUT.PUT_LINE('User name is '||lv_name_txt||' and      
    cookie '||lv_cookie_txt);
END;

Please provide more information about your exception. – mkuligowski May 13 '18 at 13:38 — mkuligowski, May 13 '18 at 13:38

APC · Accepted Answer · 2018-05-13T14:07:41.047

Your problem is the opening LOOP reads all the records in bb_shopper. One of those records presumably matches the entered credentials. However, unless the last record read is the matching one, you will exit the loop with lv_check_txt = 'Fail'. And that's why you always fail the test in the subsequent IF and get no_data_found.

The solution seems quite simple: ditch the loop and just validate the passed parameters.

create or replace 
PROCEDURE member_ck_sp
  (p_uname IN VARCHAR2,
   p_pass IN VARCHAR2,
   p_name OUT VARCHAR2,
   p_cookie OUT VARCHAR2)
IS
BEGIN
    SELECT FIRSTNAME||''||LASTNAME, COOKIE
    INTO   p_name, p_cookie
    FROM   bb_shopper
    WHERE USERNAME = P_UNAME
    AND   password = p_pass;

    --dbms_output.put_line(p_name||' '|| p_cookie);
EXCEPTION
  WHEN no_data_found THEN
     raise_application_error(-20000, 'Please re-enter credentials');
END;

chabzjo · Answer 2 · 2018-05-13T14:03:24.703

1

I haven't looked at PL\SQL in a long time. However, my first suggestion would be to look at your test data:

SELECT * FROM bb_shopper where username = 'rat55';

A few things to keep in mind:

The last line in the block to check code was probably meant to be commented out. It contains a quotation mark left open and a close bracket without an opening bracket. That can't help.

edited May 13 '18 at 14:03

answered May 13 '18 at 13:44

chabzjo

606
1
5
10

For comment in the UI is in one line so it is comment out, however when I paste the block it came out as 2 lines. – DKCroat May 13 '18 at 13:51
For 2nd I already have checked data for username = 'rat55' and has 1st and lastname, when I run the code as is it would print out only exception error message regardless if I provide correct credentials. – DKCroat May 13 '18 at 13:53
Are you sure the %TYPE needs to be there? See https://stackoverflow.com/questions/10886568/how-do-i-declare-and-use-variables-in-pl-sql-like-i-do-in-t-sql – chabzjo May 13 '18 at 13:57
Your second statement does not make any sense. – Wernfried Domscheit May 13 '18 at 14:00

score 1 · Answer 3 · answered May 14 '18 at 02:44

I'll take a different tack on this one. I see one potential error that overrides anything regarding the syntax and functionality, and that is:

I really really REALLY hope you are not planning on storing cleartext passwords in a database table.

Do not ever do this....ever. Please tell us that this routine already has the password salted/hashed before making it to this routine and table. Otherwise, this is the first thing you should looking at fixing before anything else.

This is just one of the exercise projects for preparing to take Oracle exam. Thank you for your suggestion. — DKCroat, May 15 '18 at 10:42

What I am doing wrong in this procedure

3 Answers3