Home » SQL & PL/SQL » SQL & PL/SQL » Multi packages or single package? Which is good? (oracle 9)
Multi packages or single package? Which is good? [message #279298] Wed, 07 November 2007 11:31 Go to next message
sharks
Messages: 1
Registered: November 2007
Junior Member
we have a database , 20-30 tables, we need create package, one suggestion is for each object create a package , each package have their own sp/function like new,delete,update... , thus will have 10-20 packages , another suggestion is create one package and include all sp/function in it, so the sp name will like object1_new,object1_delete,object2_delete,object2_update...

Which is more practise? what's the pro and cons between those 2?

Thanks!
Re: Multi packages or single package? Which is good? [message #279307 is a reply to message #279298] Wed, 07 November 2007 13:15 Go to previous messageGo to next message
Soumen Kamilya
Messages: 128
Registered: August 2007
Location: Kolkata
Senior Member

It depends on your requirment.
If you need to call the data services for all tables then it will be good practice to write them into a single package because it will load only once into the memory.
If you do not required all tables data services frequently then just create them separately.
Practices depends on the requirment crieteria.

Cheers
Soumen
Re: Multi packages or single package? Which is good? [message #279319 is a reply to message #279298] Wed, 07 November 2007 15:06 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
check this link. http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:3306109930452

Regards

Raj

Re: Multi packages or single package? Which is good? [message #279419 is a reply to message #279319] Thu, 08 November 2007 04:13 Go to previous messageGo to next message
amcghie
Messages: 35
Registered: March 2005
Location: Sunny Dubai
Member
Hi

I'm sure out of the 20-30 tables that you have in your database around 25% of them are static lookup/ref_data tables which would be sensible to group together in one package (say pkg_reg_data). Other tables in your schema may well be logically grouped for example you may have a CUSTOMER table and a CUSTOMER_ADDRESS table. It would make sense to group the maintenance of these together as you can't have a customer address without a customer!

Another question you should ask is..."How many developers will be working on the project?". If there is more than one grouping all your code in one HUGE package may lead to contention - i.e. 2 developers needing to write code for the same package at the same time.

Hope this helps a little.

Cheers
Re: Multi packages or single package? Which is good? [message #279471 is a reply to message #279419] Thu, 08 November 2007 11:51 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Having a package per table simplifies code generation for new tables.
I think it boils down to what you plan to with the packages. If, as one extreme, you will only create simple, straight DML functions, I would go for a package for each table.
If you will create functions that handle logical units, as in amcghie's example the customer plus address, then I would create functional packages; maybe on top of the simple (generated!) table-packages..
Previous Topic: select count of nulls thru EXECUTE IMMEDIATE
Next Topic: Need help with Date format
Goto Forum:
  


Current Time: Thu Dec 08 12:32:41 CST 2016

Total time taken to generate the page: 0.13055 seconds