pl/sql project password change

Question

this is my first pl/sql database project, so I am not experienced but need to start somewhere, I am writing a procedure for a update statement of customer table cpw which the user need to enter the username, if that match the cusername column the they will enter the old password, if that match with the cpw of the same row, then the system will update the new password which is prompt and accepted. here's what i have so far. any help will be appreciated.

CREATE OR replace PROCEDURE Changepassword (input_cusername IN VARCHAR, 
                                            old_cpw         IN VARCHAR, 
                                            new_cpw OUT VARCHAR) 
IS 
  DECLARE 
    c_username customer.cusername%TYPE; 
    c_cpw customer.cpw%TYPE; 
  BEGIN 
    ACCEPT input_cusername VARCHAR(40) prompt 'Username: ' 
    ACCEPT old_cpw         VARCHAR(20) prompt 'Enter Your Old Password:' 
    ACCEPT new_cpw         VARCHAR(20) prompt 'Enter Your New Password:' 
    UPDATE customer 
    SET    cpw = new_cpw 
    WHERE  cusername = input_cusername; 

  EXCEPTION 
  WHEN input_cusername <> c_username customer.cusername%TYPE THEN 
    dbms_output.put_line('no such user exist'); 
  WHEN old_cpw <> cpw FROM customer WHERE cusername = input_cusername THEN 
    dbms_output.put_line('Password Incorrect'); 
  END;

Answer 1

There are many things wrong with your procedure.

1. new_cpw should be an IN parameter.
2. Better use VARCHAR2 data type.

create or replace procedure changePassword (input_cusername IN VARCHAR2, 
                                            old_cpw         IN VARCHAR2, 
                                            new_cpw         IN VARCHAR2)

3. DECLARE your own EXCEPTION and user-defined error message using raise_application_error.

wrong_username EXCEPTION;
wrong_password EXCEPTION;

4. You MUST first validate the USERNAME entered.
5. If username is incorrect, then RAISE an EXCEPTION for wrong_username EXCEPTION.
6. If cusername entered is valid, i.e. if it exists in the table, then verify theold password` entered is correct.

SELECT cpw 
  INTO var_password 
FROM customer 
 WHERE cusername = input_cusername;

IF UPPER(old_cpw) = UPPER(var_password)
THEN
   UPDATE customer SET cpw = new_cpw WHERE cusername = input_cusername;
ELSE 
   RAISE wrong_password;
END IF;

7. Handle the exceptions gracefully.

EXCEPTION 
   WHEN wrong_username THEN
      raise_application_error (-20001,'You have entered an incorrect username');
   WHEN wrong_password THEN
      raise_application_error (-20001,'You have entered an incorrect password');

8. You MUST always have validations on the password security and levy some password rules. For example, LENGTH of password etc.

Answer 2

Although there are a few minor issues with your code (such as those pointed out by Lalit), the main thing you need to address first is what you actually want this code to do.

You have mixed some ACCEPT commands in your PL/SQL, but these are SQL*Plus commands that are only intended for interactive scripts; they cannot be included within PL/SQL. PL/SQL is not an interactive language; it is designed to be called from some other process (whether from within an interactive SQL*Plus script, or from a front-end interface such as Apex).