CODEWITHSUNDEEP
sqlplsql
koda
koda

Reputation: 49

PL/SQL DBMS OUTPUT in Procedure Not Displaying

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;

Upvotes: 1

Views: 409

Answers (1)

Ashish Mishra
Ashish Mishra

Reputation: 724

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;

Upvotes: 1

Related Questions