DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Public synonyms for Packages

  1. #1
    Join Date
    Dec 2001
    Location
    Keene, NH
    Posts
    510

    Public synonyms for Packages

    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.

  2. #2
    Join Date
    Mar 2001
    Location
    Ireland/Dublin
    Posts
    688

    Re: Public synonyms for Packages

    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 ?
    Best wishes!
    Dmitri

  3. #3
    Join Date
    Dec 2001
    Location
    Keene, NH
    Posts
    510
    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;

  4. #4
    Join Date
    Oct 2001
    Location
    Madrid, Spain
    Posts
    763
    If you have created the public synonym just:

    grant execute on package_name to username;

    Cheers

    Angel

  5. #5
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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:
    Code:
    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
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  6. #6
    Join Date
    Dec 2001
    Location
    Keene, NH
    Posts
    510
    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:
    Code:
    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!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width