Home » SQL & PL/SQL » SQL & PL/SQL » help with 54 page pl/sql package (Oracle 11g)
help with 54 page pl/sql package [message #434769] Thu, 10 December 2009 11:29 Go to next message
sjackson1972
Messages: 13
Registered: December 2009
Junior Member
I was just handed a document created in early 90's that contains a pl/sql package that is 54 pages long.
He wants me to condense this package. I have never worked with a package this long. Where do I start??? I have no ER diagram nor do I have access to the database. Just a print out of the package Confused
Re: help with 54 page pl/sql package [message #434770 is a reply to message #434769] Thu, 10 December 2009 11:32 Go to previous messageGo to next message
BlackSwan
Messages: 25035
Registered: January 2009
Location: SoCal
Senior Member
>Where do I start???
Line #1
Re: help with 54 page pl/sql package [message #434772 is a reply to message #434769] Thu, 10 December 2009 11:40 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
I'd be tempted to work out what it does and then rewrite it from scratch.

It could be worse - I've got a much longer one, would love to rewrite it but I never have the time.

Look for logical sub groups of functions/procedures and split then out into seperate packages.

Look for duplicate code and try and move it into a seperate procedure/function that can be called from all the places it's currently used.

Look for code that does multiple select statements and see if you can combine them into a single select statement.

Look for code that does insert/update/delete in a cursor loop and see if you can't combine them to get a single insert/update/delete statement.

Check to see if all the parameters/variables that are declared are actually being used.
icon12.gif  Re: help with 54 page pl/sql package [message #434774 is a reply to message #434769] Thu, 10 December 2009 11:43 Go to previous messageGo to next message
LKBrwn_DBA
Messages: 487
Registered: July 2003
Location: WPB, FL
Senior Member

1) Request a soft-copy of the package so you can manipulate using a text (or other) editor.
2) Avail yourself of a PL/SQL code analyzer:
http://www.brothersoft.com/code-visual-editor-66258.html
http://www.brothersoft.com/downloads/sql-analyzer.html
3) Good luck!
Razz
Re: help with 54 page pl/sql package [message #434775 is a reply to message #434772] Thu, 10 December 2009 11:44 Go to previous messageGo to next message
sjackson1972
Messages: 13
Registered: December 2009
Junior Member
Hey Thanks cookiemonster(I love the name). At least I have a starting point. It may take me sometime, but hopefully I can at least get it down to like maybe 20-30 pages. The good thing is the author did put some comments in the application..
Re: help with 54 page pl/sql package [message #434777 is a reply to message #434774] Thu, 10 December 2009 12:04 Go to previous messageGo to next message
sjackson1972
Messages: 13
Registered: December 2009
Junior Member
Thanks. This will get me started...
Re: help with 54 page pl/sql package [message #434789 is a reply to message #434777] Thu, 10 December 2009 14:06 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
Well worth a read...
http://www.joelonsoftware.com/articles/fog0000000069.html
Re: help with 54 page pl/sql package [message #434791 is a reply to message #434769] Thu, 10 December 2009 14:31 Go to previous message
sjackson1972
Messages: 13
Registered: December 2009
Junior Member
@andrew, interesting article. Thanks for posting
Previous Topic: exec dbms_mview.refresh('mviewname','c','atomic_refresh=>FALSE) is giving error
Next Topic: Avoiding huge rollback
Goto Forum:
  


Current Time: Sat Dec 03 19:53:39 CST 2016

Total time taken to generate the page: 0.05212 seconds