CODEWITHSUNDEEP
oracle-databaseoracle12c
Mahesh
Mahesh

Reputation: 198

Referring to objects(tables) in other schema or user in Oracle procedure

Am accessing table data from other schema and inserting into current schema table in oracle procedure, but am unable to compile the procedure getting error "Error(5,27): PL/SQL: ORA-00942: table or view does not exist".

But where as when I select data from table(in other schema) directly am able to fetch the data but not in procedure. I have all required permissions as well.

Below is the procedure.

CREATE OR REPLACE PROCEDURE TEST_SCHEMA
IS
BEGIN
  INSERT INTO table1
    SELECT * FROM Other_User_Schema.Table2

  COMMIT;
END;

Upvotes: 0

Views: 937

Answers (1)

Littlefoot
Littlefoot

Reputation: 142713

It seems that you acquired privileges to select data from other_user_schema.table2 via role. If that's so, grant that privilege directly to your user because that won't work in named PL/SQL procedures.

Upvotes: 1

Related Questions