Search code examples
oraclestored-procedurespackageora-00942

Oracle 10: Is it possible to access a public synonym to a table in a stored procedure or package?

I've been trying without success to add a reference to a public synonym in a package or stored procedure in oracle for a while, and I'm wondering if there are solutions to this problem short of accessing the tables directly. For instance:

CREATE OR REPLACE PROCEDURE test_func AS
  test_int INTEGER;<br>
BEGIN<br>
  select count(*) INTO test_int FROM test_synonym;
END;
/

I know this procedure does nothing, but it is only an example. Where the table 'test_synonym' is actually a public synonym to a table in another schema. I do have select permissions to that table, but trying to compile this repeatedly gives me the error:

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

Thanks for your time.

Solution

  • Does the schema in which you're creating the procedure have direct SELECT privs (ie not via a role?)

    When you create a procedure (function/package), permissions on tables need to be directly granted, not granted via a role.