PL/SQL DBMS OUTPUT in Procedure Not Displaying

Question

I created this procedure to validate a login. The DBMS OUTPUT in the first IF statement outputs fine, but the DBMS OUTPUT in the ELSE statement doesn't display anything when I enter invalid info; I'm not sure why?

    CREATE OR REPLACE PROCEDURE member_ck_sp
    (p_username IN VARCHAR2,
     p_password IN VARCHAR2)
     IS
     p_check VARCHAR2(10):= 'INVALID';
     fullname VARCHAR2(20);
    CURSOR member_cur IS
    SELECT username, firstname, lastname, cookie, password
    FROM bb_shopper
    WHERE username = p_username
        AND password = p_password;
     BEGIN
      FOR rec_cur IN member_cur LOOP
          IF p_username = rec_cur.username AND p_password = rec_cur.password THEN
              fullname := rec_cur.firstname || ' ' || rec_cur.lastname;
              DBMS_OUTPUT.PUT_LINE('Name: ' || fullname || ' Cookie: ' || rec_cur.cookie);
              DBMS_OUTPUT.PUT_LINE('LOGGED IN');
            ELSE
                DBMS_OUTPUT.PUT_LINE('INVALID');
          END IF;
      END LOOP;
      END member_ck_sp;
      
      
      
    BEGIN
        member_ck_sp('rat55', 'kile');
    END;

Does this answer your question? Cursor For Loop how to print message when not found — Ashish Mishra
– Ashish Mishra, Commented Jul 11, 2021 at 3:13
The condition p_username = rec_cur.username AND p_password = rec_cur.password is always true. The cursor is only selecting rows from the table where the username and password match. Your IF statement then makes another check that they match (which will always be true), hence the ELSE block will never be executed. — Luke Woodward
– Luke Woodward, Commented Jul 11, 2021 at 6:49

Ashish Mishra · Accepted Answer · 2021-07-11 03:10:31Z

As you have used the below SQL query for the cursor and when you give invalid input so the for loop that iterates cursor values will be not found and it will skip iterating the loop and hence your program will end without informing that the Input was invalid. You can use a flag variable that can inform you whether the for loop ran or not.

SELECT username, firstname, lastname, cookie, password
FROM bb_shopper
WHERE username = p_username
    AND password = p_password;

CREATE OR REPLACE PROCEDURE member_ck_sp
(p_username IN VARCHAR2,
 p_password IN VARCHAR2)
 IS
 p_check VARCHAR2(10):= 'INVALID';
 fullname VARCHAR2(20);
 data_present BOOLEAN := FALSE;
CURSOR member_cur IS
SELECT username, firstname, lastname, cookie, password
FROM bb_shopper
WHERE username = p_username
    AND password = p_password;
 BEGIN
  FOR rec_cur IN member_cur LOOP
      IF p_username = rec_cur.username AND p_password = rec_cur.password THEN
          fullname := rec_cur.firstname || ' ' || rec_cur.lastname;
          DBMS_OUTPUT.PUT_LINE('Name: ' || fullname || ' Cookie: ' || rec_cur.cookie);
          DBMS_OUTPUT.PUT_LINE('LOGGED IN');
          data_present := TRUE;
      END IF;
  END LOOP;
  --Checking Invalid input 
  IF NOT data_present 
  THEN
  DBMS_OUTPUT.PUT_LINE('INVALID');
  END IF;
  END member_ck_sp;

Collectives™ on Stack Overflow

PL/SQL DBMS OUTPUT in Procedure Not Displaying

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related