drop table people;
drop table invalid_values;

create table invalid_values (
    id NUMBER GENERATED ALWAYS as IDENTITY(START with 1 INCREMENT by 1),
    message VARCHAR2(60)
    );

create table people (
    id      number,
    fname   varchar(60),
    lname   varchar(60),
    addr    varchar(120)
);

insert into people
values
(1,'Mary','Marison','5 Smith St')
/
insert into people
values
(2,'Jöhn','Johnson','23 Court St')
/
insert into people
values
(3,'James','Jameson','78 23rd St')
/
insert into people
values
(4,'Irene','Irenberg','74 Douglass St')
/
insert into people
values
(5,'Gary','Garriful','23 Buènker Ave')
/

create or replace function f_valid_chars (p_str in varchar2) 
return boolean
is

v_length  PLS_INTEGER := 0;
v_indx    PLS_INTEGER := 0;
v_letr    VARCHAR2(10) := NULL;
v_ascii   PLS_INTEGER := NULL;
v_str     VARCHAR2(120) := p_str;

v_retval boolean := true;

BEGIN 
  v_length := length(v_str);

  WHILE (v_indx < v_length 
         and v_retval = true)
  LOOP
    v_indx := v_indx + 1;
    v_letr := UPPER(SUBSTR(v_str, v_indx, 1) );
    v_ascii := ASCII(v_letr);

    IF NOT (v_ascii BETWEEN 65 AND 90
            OR v_ascii BETWEEN 44 AND 57
            OR v_ascii IN (32, 35, 39, 40, 41) )
    THEN
      v_retval := false;
    END IF;
END LOOP;

RETURN v_retval;


END f_valid_chars;
/

create or replace procedure p_log_invalid_chars
is

cursor c_people
is
select  id, 
        NVL(fname, 'Q')       FNAME,
        NVL(lname, 'Q')       LNAME,
        NVL(addr, 'Q')        ADDR
from people;

v_id        pls_integer;
v_fname     varchar2(60);
v_lname     varchar2(60);
v_addr      varchar2(120); 

BEGIN

  FOR rec IN c_people LOOP

    v_id          := rec.id;
    v_fname       := rec.FNAME;
    v_lname       := rec.LNAME;
    v_addr       := rec.ADDR;

    if not f_valid_chars(v_fname) then
        insert into invalid_values(message) values
        ('invalid chars in record: '||v_id||': fname: '||v_fname);
    end if;
    
    if not f_valid_chars(v_lname) then
        insert into invalid_values(message) values
        ('invalid chars in record: '||v_id||': lname: '||v_lname);
    end if;
    
   if not f_valid_chars(v_addr) then
        insert into invalid_values(message) values
        ('invalid chars in record: '||v_id||': addr: '||v_addr);
    end if;
    
  END LOOP;

END p_log_invalid_chars;
/


exec p_log_invalid_chars;

select * from invalid_values;