Ok I am mildly interested in the fact that an anonymous block would behave differently than an pl/sql but they are indeed different.

any hooo...grant the approprite privs to the user (not throuhg a role) and your procedure will compile and even work!

SQL> create user stmontgo identified by stmontgo;

User created.

SQL> grant dba to stmontgo;

SQL> conn stmontgo/stmontgo@dallas
Connected.

SQL> select count(*) from scott.emp;

COUNT(*)
----------
14

SQL> set serveroutput on
SQL> declare
2 x number;
3 begin
4 select count(*) into x from scott.emp;
5 dbms_output.put_line(x);
6 end;
7 /
14



SQL> create or replace procedure my_proc as
2 x number;
3 begin
4 select count(*) into x from scott.emp;
5 dbms_output.put_line(x);
6 end;
7 /

Warning: Procedure created with compilation errors.

SQL> show errors
Errors for PROCEDURE MY_PROC:

LINE/COL ERROR
-------- -----------------------------------------------------------------
4/1 PL/SQL: SQL Statement ignored
4/35 PL/SQL: ORA-00942: table or view does not exist


SQL> conn scott/tiger@dallas
Connected.
SQL> grant select on emp to stmontgo;

Grant succeeded.



SQL> conn stmontgo/stmontgo@dallas
Connected.

SQL> set serveroutput on
SQL> exec my_proc;
14

PL/SQL procedure successfully completed.

SQL>