I'm having trouble writing a simple procedure.
I'm trying to simply select against a table in a different schema.
I first wrote a simple procedure that selected against a table in my
schema, which worked.
CREATE OR REPLACE PROCEDURE test
IS
CURSOR cur_test
IS
select *
from tablename;
BEGIN
FOR itemrec IN cur_test
LOOP
DBMS_OUTPUT.PUT_LINE('record!');
END LOOP;
END test;
A simple change to this is to change my select line to:
select *
from otherTablespace.tablename;
This gives me an error saying "otherTablespace.tablename must be
declared."
My first thought was to create a public synonym, so I did.
CREATE PUBLIC SYNONYM mySyn FOR otherTablespace.tablename
I also change my select line to read:
select *
from mySyn
PLS-00201: identifier mySyn' must be declared.
What am I missing? Why can't I "see" the other schemas table in a
procedure even when explicitly referenced using the
tablespace.tablename?
