CREATE OR replace PROCEDURE alert_password 
IS 
  var1 VARCHAR2(50); 
  var2 NUMBER; 
  var3 VARCHAR2(50); 
  var5 VARCHAR2(50); 
  var4 DATE; 
  var6 VARCHAR2(50); 
  var7 VARCHAR2(50); 
  var8 VARCHAR2(50); 
  CURSOR c1 IS 
    SELECT   a.username, 
             Trunc((((86400*(a.expiry_date-SYSDATE))/60)/60)/24) "Days", 
             a.expiry_date, 
             b.cc_email_id_1, 
             b.tl_email_id 
    FROM     dba_users a, 
             password_alert b 
    WHERE    a.username=b.schema_name 
    ORDER BY b.schema_name DESC; 

BEGIN 
  OPEN c1; 
  LOOP 
    FETCH c1 
    INTO  var1, 
          var2, 
          var4, 
          var8, 
          var7; 
     
    BEGIN 
      SELECT server 
      INTO   var6 
      FROM   password_alert 
      WHERE  schema_name=var1; 
     
    EXCEPTION 
    WHEN OTHERS THEN 
      var6:='XYZ'; 
    END; 
    IF (var7 IS NOT NULL 
      AND 
      var8 IS NULL) 
      AND 
      ( 
        var2 =20 
        OR 
        var2 = 15 
      ) 
      THEN 
      SELECT tl_email_id 
      INTO   var3 
      FROM   password_alert 
      WHERE  Upper(schema_name)=var1; 
       
      Password_alert_Proc_2(var3,'abc@yahoo.com','password change','Server of' 
      ||var6 
      || '(LIVE server)- s user ' 
      || var1 
      ||' gets ' 
      ||' expired on ' 
      ||var4); 
    ELSIF (var8 IS NOT NULL 
      AND 
      var7 IS NOT NULL) 
      AND 
      ( 
        var2 =20 
        OR 
        var2 = 15 
      ) 
      THEN 
      SELECT tl_email_id, 
             cc_email_id_1 
      INTO   var3, 
             var5 
      FROM   password_alert 
      WHERE  upper(schema_name)=var1; 
       
      Password_alert_Proc_3 (var3,xyz@gmail.com',var5, 'password change ', 'Server of ' 
      ||var6 
      || '(LIVE server)- s user ' 
      || var1 
      ||' gets ' 
      ||' expired on ' 
      ||var4); 
    ELSE 
      NULL; 
    END IF; 
    EXIT 
  WHEN c1 %NOTFOUND; 
  END LOOP; 
  CLOSE c1; 
END;

Trunc((((86400*(a.expiry_date-SYSDATE))/60)/60)/24)

SELECT   a.username, 
             Trunc((((86400*(a.expiry_date-SYSDATE))/60)/60)/24) "Days", 
             a.expiry_date, 
             b.cc_email_id_1, 
             b.tl_email_id 
    FROM     dba_users a, 
             password_alert b 
    WHERE    a.username=b.schema_name 
    ORDER BY b.schema_name DESC; 

SQL> select * from password_alert ;
select * from password_alert
              *
ERROR at line 1:
ORA-00942: table or view does not exist