OraFAQ Forum: SQL & PL/SQL » help with 54 page pl/sql package

Home » SQL & PL/SQL » SQL & PL/SQL » help with 54 page pl/sql package (Oracle 11g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

help with 54 page pl/sql package [message #434769]

Thu, 10 December 2009 11:29

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

Report message to a moderator

Re: help with 54 page pl/sql package [message #434770 is a reply to message #434769]

Thu, 10 December 2009 11:32

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>Where do I start???
Line #1

Report message to a moderator

Re: help with 54 page pl/sql package [message #434772 is a reply to message #434769]

Thu, 10 December 2009 11:40

cookiemonster
Messages: 13960
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.

Report message to a moderator

Re: help with 54 page pl/sql package [message #434774 is a reply to message #434769]

Thu, 10 December 2009 11:43

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

Report message to a moderator

Re: help with 54 page pl/sql package [message #434775 is a reply to message #434772]

Thu, 10 December 2009 11:44

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..

Report message to a moderator