CREATE OR REPLACE PROCEDURE CHANGE_PWD
        (P_USERNAME IN USERS.USERNAME %TYPE,
        P_OLD_PW  IN USERS.PASSWORD %TYPE,
        P_NEW_PW IN USERS.PASSWORD %TYPE,
        P_SUCCES OUT BOOLEAN) IS


    BEGIN
        SELECT USERS.PASSWORD INTO P_OLD_PW FROM USERS
        WHERE USERS.USERNAME = p_username;

    IF p_old_pw = USERS.PASSWORD


    THEN
        UPDATE USERS
        SET PASSWORD = p_new_pw
        WHERE USERS.USERNAME = p_username;



    ELSE RETURN <error message>;

    END IF;

    END CHANGE_PWD;

SQL> create table users
  2    (username varchar2(30),
  3     password varchar2(20));

Table created.

SQL> insert into users values ('LITTLEFOOT', '2cellos');

1 row created.

SQL> create or replace procedure change_pwd
  2    (p_username   in users.username%type,
  3     p_old_pw     in users.password%type,
  4     p_new_pw     in users.password%type,
  5     p_success   out varchar2
  6    )
  7  is
  8    l_old_pw  users.password%type;
  9  begin
 10    select password
 11      into l_old_pw
 12      from users
 13      where username = p_username;
 14
 15    if l_old_pw = p_old_pw then
 16       -- OK, old password matches
 17       update users set
 18         password = p_new_pw
 19         where username = p_username;
 20       p_success := 'Password changed';
 21    else
 22       p_success := 'Invalid old password';
 23    end if;
 24  exception
 25    when no_data_found then
 26      p_success := 'Invalid username';
 27  end;
 28  /

Procedure created.

SQL> set serveroutput on;
SQL> declare
  2    l_succ varchar2(100);
  3  begin
  4    change_pwd('LITTLEFOOT', 'xyz', 'newpwd', l_succ);
  5    dbms_output.put_line(l_succ);
  6
  7    change_pwd('bigFOOT', 'abc', 'newpwd', l_succ);
  8    dbms_output.put_line(l_succ);
  9
 10    change_pwd('LITTLEFOOT', '2cellos', 'newpwd', l_succ);
 11    dbms_output.put_line(l_succ);
 12  end;
 13  /
Invalid old password
Invalid username
Password changed

PL/SQL procedure successfully completed.

SQL>