How to call a package which is belongs to a another user

Question

I have a package'PKG_PARTITION_MAINTENANCE in Schema : 'SAB_OWN' and i gave EXECUTE PERMISSION TO A USER LIKE BELOW :

GRANT EXECUTE ON  PKG_PARTITION_MAINTENANCE TO SAB_READ_USER;

but when the user is trying to call the package it getting error as below :

EXECUTE PKG_PARTITION_MAINTENANCE.ADD_TABLE_PARTITIONS('tbl_name');

ERROR :

[Error] Execution (3: 7): ORA-06550: line 1, column 7:
PLS-00201: identifier 'PKG_PARTITION_MAINTENANCE.ADD_TABLE_PARTITIONS' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

is anything i am missing, why user SAB_READ_USER not able to execute it ? This package is running well in SAB_OWN Schema.

Answer 1

You have to tell Oracle which schema the object belongs to, if it isn't in the schema you're logged into like so:

EXECUTE SAB_OWN.PKG_PARTITION_MAINTENANCE.ADD_TABLE_PARTITIONS('tbl_name');

You could also create a synonym instead:

create synonym PKG_PARTITION_MAINTENANCE for SAB_OWN.PKG_PARTITION_MAINTENANCE;

which acts like a pointer to tell Oracle where to look for the object being called.

Or, you could alter your session so that the schema you're "looking" at is a different one to the one you logged in as:

alter session set current_schema = SAB_OWN;

These last two options should allow you to run the package without explicitly stating the schema name, although personally, I wouldn't recommend them - I would go with explicitly stating the schema name, unless there was a jolly good reason why that wouldn't work!