CODEWITHSUNDEEP
oracle-databaseoracle11gdatabase-administrationdatabase-partitioning
sabya
sabya

Reputation: 119

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

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.

Upvotes: 0

Views: 2207

Answers (1)

Boneist
Boneist

Reputation: 23588

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!

Upvotes: 2

Related Questions