select count(*) into counter from all_tables

CREATE OR REPLACE TRIGGER MY_LIST BEFORE
INSERT ON LISTS FOR EACH ROW 
BEGIN
Teste;
END;

create procedure teste authid current_user
as
counter number;
begin
select count(*) into counter from all_tables;
dbms_output.put_line('Counter -> '||counter);
end;

SQL> create table lists (col1 varchar2(10), col2 varchar2(10));

Table created.

SQL> create or replace procedure teste authid current_user
  2  as
  3  counter number;
  4  begin
  5  select count(*) into counter from all_tables;
  6  dbms_output.put_line('Counter -> '||counter);
  7  end;
  8  /

Procedure created.

SQL> CREATE OR REPLACE TRIGGER MY_LIST BEFORE
  2  INSERT ON LISTS FOR EACH ROW 
  3  BEGIN
  4  Teste;
  5  END;
  6  /

Trigger created.

SQL> select count(*) from all_tables;
  COUNT(*)
----------
      1506

1 row selected.

SQL> set role none;

Role set.

SQL> select count(*) from all_tables;
  COUNT(*)
----------
        86

1 row selected.

SQL> insert into lists('ce','Teste'); 
insert into lists('ce','Teste')
                  *
ERROR at line 1:
ORA-00928: missing SELECT keyword


SQL> insert into lists values('ce','Teste'); 
Counter -> 86

1 row created.