SELECT from SQL*Plus but not from PL/SQL

PL/SQL respect object privileges given directly to the user, but does not observe privileges given through roles. The consequence is that a SQL statement can work in SQL*Plus, but will give an error in PL/SQL. Choose one of the following solutions:

Grant direct access on the tables to your user. Do not use roles!

GRANT select ON scott.emp TO my_user;

Define your procedures with invoker rights (Oracle 8i and higher);

create or replace procedure proc1
authid current_user is
begin

Move all the tables to one user/schema