Public synonyms for Packages

[gopi]

executing the following using an explict owner, package name and package body name worked fine from user scott (privs are there):

exec stage_owner.RPT_CA_INVENTORY_AGING.GET_INVENTORY_AGING_SUMMARY

...it works!

Now
SCOTT> exec RPT_CA_INVENTORY_AGING.GET_INVENTORY_AGING_SUMMARY
does not work because of a lack of a synonym and lack of specifying the owner of the package.


How do I create a public synonym for a package.package_body ? The following public synonym was created as SYSTEM but SCOTT still cannot see the RPT_CA_INVENTORY_AGING.GET_INVENTORY_AGING_SUMMARY unless I specify the owner.

This worked syntactically but not during execution logged on as SCOTT:

create public synonym "RPT_CA_INVENTORY_AGING.GET_INVENTORY_AGING_SUMMARY" for
"stage_owner.RPT_CA_INVENTORY_AGING.GET_INVENTORY_AGING_SUMMARY"

Synonym created.

[kgb]

If you mean, 'package body' than, you can not create synonym only for package body.

Originally posted by gopi
How do I create a public synonym for a package.package_body ?

[gopi]

I tried creating a synonym for just the package body. Users still could not see it without every identifier owner and package and package body:

owner = stage_owner
Package = RPT_CA_INVENTORY_AGING
Package body = GET_INVENTORY_AGING_SUMMARY

I want any user in the dB (who has the privs) to run without the owner:

SCOTT> exec RPT_CA_INVENTORY_AGING.GET_INVENTORY_AGING_SUMMARY;

instead of:

SCOTT> exec stage_owner.RPT_CA_INVENTORY_AGING.GET_INVENTORY_AGING_SUMMARY;

[aarroyob]

If you have created the public synonym just:

grant execute on package_name to username;

Cheers

Angel

[jmodic]

Originally posted by gopi
owner = stage_owner
Package = RPT_CA_INVENTORY_AGING
Package body = GET_INVENTORY_AGING_SUMMARY

You have mixed up few things. Package and package body allways have the same name.

In your case:
Package = RPT_CA_INVENTORY_AGING
packaged procedure (not package body) = GET_INVENTORY_AGING_SUMMARY

your problem is that your public synonym was created totaly wrong:

create public synonym "RPT_CA_INVENTORY_AGING.GET_INVENTORY_AGING_SUMMARY" for
"stage_owner.RPT_CA_INVENTORY_AGING.GET_INVENTORY_AGING_SUMMARY"

You can't include packaged function/procedure in your synonym. Also, because you used double quotes the schema name STAGE_OWNER has being interpreted wrong, because you used lowercase inside the double quotes. Your create sysnonym statement should be:

create public synonym RPT_CA_INVENTORY_AGING for
stage_owner.RPT_CA_INVENTORY_AGING;

Now your users will be able to reference those packaged procedures and functions without specifying schema name, eg

SCOTT> exec RPT_CA_INVENTORY_AGING.GET_INVENTORY_AGING_SUMMARY

[gopi]

Originally posted by jmodic
You have mixed up few things. Package and package body always have the same name.

packaged procedure (not package body) = GET_INVENTORY_AGING_SUMMARY

Yes, Yes! Thank you Jurij!

Your create synonym statement should be:

create public synonym RPT_CA_INVENTORY_AGING for
stage_owner.RPT_CA_INVENTORY_AGING;

Now your users will be able to reference those packaged procedures and functions without specifying schema name, eg

SCOTT> exec RPT_CA_INVENTORY_AGING.GET_INVENTORY_AGING_SUMMARY

Thank you again Jurij...I'm now past my mental block!