Packages, Procedures & Functions

Tags:
Oracle
What are the best practices for design with regards to packages, procs and funcs. More specifically, which schema to place these objects? The database contains a couple of schemas with tables that support the system. This is a DW system and one schema contains stage tables and the other holds the dimension and fact tables. The procedures and functions reference both schemas. So, wondering where theses procedures, procs and funcs should be placed? There's lots of info on how to create these objects but I cannot find info on the design of the system. Thanks in advance!
1

Answer Wiki

Thanks. We'll let you know when a new response is added.

By putting the stored pkgs, procs and funcs in the tables schema, you can avoid a lot of grants. On the other hand, by putting them in there own schema, allows a third party create and maintain stored pkgs, procs and funcs without having DBA or schema owner passwords.

It depends on how your IT department is and will be structured. I prefer to put them in their own schma so that I am not responsible for their management and I can control what tables they access and the type of access they have. This is also useful from a capacity planning standpoint because you know when something is inserting or updating records in a table that was previously unused.

You just need to grant the permissions and recompile.

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • Shmath
    You can put where they are going to be used. If the functions, procedures etc are used for data scrubbing, during load operations to warehouse, then it may be a good idea to put them in staging schema. Also, you can create public synonyms for the packages, procedures, functions to make it transparent for users, to where they actually live.
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.

Following

Share this item with your network: