error for INSERT using SELECT in PROCEDURE (PL/SQL)

Question

I'm getting error with running this procedure in PL/SQL (Oracle) in my own schema:

CREATE OR REPLACE PROCEDURE my_procedure AS
BEGIN

  INSERT INTO myschema.mytable (col1, col2, col3, col4)
  SELECT COUNT(*), col2, col3, col4
  FROM AnotherSchema.anotherTable
  GROUP BY AnotherSchema.anotherTable.col2;

END my_procedure;

These are the errors I get:

Error(4,7): PL/SQL: SQL Statement ignored

Error(6,22): PL/SQL: ORA-00942: table or view does not exist

AnotherSchema.anotherTable does exist. and so does myschema.mytable

in fact, when I just run the INSERT command, it works fine and a row gets inserted into myschema.mytable :

  INSERT INTO myschema.mytable (col1, col2, col3, col4)
  SELECT COUNT(*), col2, col3, col4
  FROM AnotherSchema.anotherTable
  GROUP BY AnotherSchema.anotherTable.col2;

I don't understand why I get this error when i put the INSERT function inside the procedure.

---

EDIT:

I need to clarify that I am running the script in my own schema and inserting into a table that was created by myself in my own schema. and I can perform select (from my schema) on the table I'm reading from AnotherSchema.

Answer 1

You have not been granted the select privilege to the tables in the other schema directly but probably through a role, for your insert to work inside PLSQL, you will need the direct Grant to select on those tables.

Here is a link from Ask Tom explaining why it is like that:

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1065832643319