I have 2 users in OracleXE db: ALICE
and BOB
. Following sequence of steps is successful at my local machine:
ALICE
create or replace type testtype as object(x number(16));
(EDIT: correction from wrong name foo which misleaded Matthew McPeak)BOB
BOB
sees the ALICE
's type:
select * from all_types
returns rowdeclare x ALICE.testtype; begin null; end;
compilesSame steps applied to our development database yield in situation where type is invisible to BOB
, i.e. select doesn't return row for the type, PLSQL block compilation fails with PLS-00201: identifier 'ALICE.TESTTYPE' must be declared
error.
I need to make type visible, so I ask: What may cause this difference? (I guessed for some need of grants on types or public synonyms, however this minimalistic example proves the opposite.) Some property of user/session? I did my best in googling and am stuck now though I hope the solution will be trivial. Both databases are Oracle 11g (11.2.0.1.0 dev, 11.2.0.2.0 local).
Thanks!
I'm assuming it's more than just the fact that you named your type foo
but tried to use it as testtype
.
If foo
is the correct name, then, as ALICE
:
grant execute on foo to bob;
Bob probably has EXECUTE ANY TYPE
or similar system privilege in the 1st database.