Home » SQL & PL/SQL » SQL & PL/SQL » Modularisation of Plsql packages (Oracle 10g)
Modularisation of Plsql packages [message #596616] Wed, 25 September 2013 03:26 Go to next message
njnbat
Messages: 19
Registered: September 2013
Junior Member
Currently i am doing a restructuring project mainly on the oracle plsql packages in our company . It involves working on many of the core packages of our company . we never had documentations for the back end work done so far and the intention of this project is to create new set of api's based on the current logics in a structured way along with avoiding all unwanted logics that currently exist in the system. We are also making a new module currently for the main business of the organisation that would work based on these newly created backend API's . As i started of this project, i found out that most of the wrapper api's had around more than 8000 lines of code. I managed to covert this code into many single api's and invoked them from the wrapper api. This activity in itself has been a time taking process but i was able to cut down the lines of code to just 900 in wrapper api by calling independent api's for each business functionality . i would like to know from you experts if this mode of modularizing the code is good and worth the time invested in it as i am not sure if it would have many performance benefits. But from the point of view of readability of code , this is definitely helping and now i am able to understand the 8000 lines of code in a much better way after restructuring and i am sure the other developers in my organisation too will understand. Requesting you to let me know if i am doing the right thing and if its having its advantages apart from readability please do mention them. sorry for the long explanation. And is it ok having more than 1000 lines of code in a wrapper api.
Re: Modularisation of Plsql packages [message #596626 is a reply to message #596616] Wed, 25 September 2013 04:16 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2346
Registered: May 2013
Location: World Wide on the Web
Senior Member
1. It is good to have all the subprograms to be wrapped in a package based on the functional modules. For one particular module in API, you should have one package to wrap all the objects used for that module.

2. 1000 lines of code should not be an issue. What issue do you face with more number of lines? Also, not every line of the code will be executed. Since it is procedural programmming, it depends on the logic you use in the code. If you are having too many procedural calls, loops and calculations, then you can make use of the PL/SQL Native Compilation feature. Though it has limited improvements.

3. The biggest advantage of wrapping the objects inside a package is that the first time you invoke a package subprogram, Oracle Database loads the whole package into memory. Subsequent invocations of other subprograms in same the package require no disk I/O.

Since all the explanation is basics and can be found in documentation. Is there any specific issue you are facing? What is the benefit in terms of performance that will be drawn out of your restructuring project?

Regards,
Lalit
Re: Modularisation of Plsql packages [message #596636 is a reply to message #596626] Wed, 25 September 2013 05:49 Go to previous messageGo to next message
rleishman
Messages: 3700
Registered: October 2005
Location: Melbourne, Australia
Senior Member
The type of benefit you are looking for is called "Total Cost of Ownership" or TCO. http://en.wikipedia.org/wiki/Total_cost_of_ownership

Assuming there are no performance or scalability benefits to your modifications, the payback for your effort will come primarily through reduced cost of future maintenance. Do these packages undergo frequent changes? Do the rules change a lot? Or are there frequent bugs? When someone does make a change, is it hard to test? Or do they often introduce other bugs accidentally? These things lead to increased operational expenses and ultimately reduced profit for your organisation.

If the packages are very stable, reliable and not subject to change, then you may find there is no justifiable improvement to Total Cost of Ownership. That's pretty hard to hear because we all enjoy making code neater and better.

Ross Leishman
Re: Modularisation of Plsql packages [message #596637 is a reply to message #596626] Wed, 25 September 2013 05:51 Go to previous messageGo to next message
njnbat
Messages: 19
Registered: September 2013
Junior Member
First of all thanks for replying @lalit. The userdefined objects used in many of the packages are not properly structured and they have so many un identifiable attributes making it difficult for developers to make changes and debug the code.
Due to a wrong pattern of development process , these mistakes accumulated and i can see almost 90% packages with huge dependency that are very difficult to maintain now .
So the main intention of the project is to reduce impacts while making changes in different modules .
Hence we decided to start changes by making the backend procedures properly structured and reusable.
As a result we have decided in changing theexisting objects with newly designed objects .
Initially i was finding it difficult to understand the logics due to lengthy lines of code that exceeded around 8000 loc with so many comments in between. But i have made it a point to change these big procedures into individual api's based on the functionality of each logics and invoke them from the main api .
Now it is helping me identify many of the unused objects/local variables as well.
I am not sure but if this way of modularization will help performance much but surely might help in debugging and maintenance.


Re: Modularisation of Plsql packages [message #596638 is a reply to message #596636] Wed, 25 September 2013 05:58 Go to previous messageGo to next message
njnbat
Messages: 19
Registered: September 2013
Junior Member
@Ross , thanks for the reply. As you said , the intention of making the code neat and better is definitely there. Also i realize the importance to have documentations for each api's. Hence i am making it a point to write down every changes which i have been making.
Talking about bugs, they are in plenty and that has lead the management to take a decision to initiate the restructuring project.
But after 1 and half months i am under a slight dilemma if these hard works done in structuring around 8000 lines of code in different api's gonna help in the long run or not. Also m confused if it really is gonna make any performance improvements too.
whenever i see more than 1000 lines of code written together i am thinking about consolidating it in a different api, but i not sure if its worth the time invested.Only thing which i feel confident is that future maintenance will be easy and the other developers who will work in these api's in future might thank me for this work.
Re: Modularisation of Plsql packages [message #596645 is a reply to message #596637] Wed, 25 September 2013 07:18 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2346
Registered: May 2013
Location: World Wide on the Web
Senior Member
Ok. There is another dimension from process perspective of your organisation. If modularization and restructuring saves the time in development cycle of the SDLC for new development projects, then operational excellence team will be happy by the save in efforts ultimately made. Good luck.

Regards,
Lalit
Re: Modularisation of Plsql packages [message #596751 is a reply to message #596645] Thu, 26 September 2013 02:39 Go to previous message
njnbat
Messages: 19
Registered: September 2013
Junior Member
@lalit. thank you for the valuable feedbacks.
Previous Topic: How to check procedure compiled warnings & errors
Next Topic: splitting 1 output row into 3 output rows
Goto Forum:
  


Current Time: Sat Sep 20 23:19:17 CDT 2014

Total time taken to generate the page: 0.07332 seconds