How to get line counts by package name, then procedure or function.

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

slightvw - thanks. short answer - it's not easy!

I'll dig into this tomorrow and post follow-up questions.

SOLUTION

Geert G

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

SOLUTION

johnsone

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

Geert Gruwez - thanks.

johnsone - good point too, and I just learned about wrapping ...

slightvw
>>It used to be used as an estimator for level of effort for costing and forecasting
- yes, exactly what I am looking for

=============
I really just need lines of code and # objects, it will give a measure of the size of the current system. So I'll use ALL_SOURCE and forget about the package relationships (although it would certainly be helpful, just too complicated for the moment).

awking00

Just to make sure I understand what you're looking for - using your example, are you referring to procedure_a and procedure_b and funcgtion_a as being children of the parent package pkg_a that has 2020 total lines?

ASKER

Thanks for your comments!

Geert G

well, yeah, we got a coder once, called Serge
I think he was paid per line of code.

it's faster to travel around the world than get results from his code...

so line count ...
i'd ask for efficient code, not a long essay of code

>>just too complicated for the moment

DBA_DEPENDENCIES isn't that complicated and it should get you "most" of what you need. It probably won't get you everything since I bet most of us, myself included, has used dynamic SQL somewhere in our code.

ASKER

slightwv, so I understand about the dynamic sql, but for my purpose this isn't really going to be a differentiator.

Note: ALL_SOURCE doesn't even have the name of the procedures / functions in a package. So you get just the package_name and the total lines of code. So what I wanted to get I'm actually not sure is possible. Same with DBA_Dependencies, the name of a procedure in a package is not in DBA_DEPENDENCIES.
- For example, I see do not see PROCEDURE or FUNCTION as a REFERENCED_TYPE in All_Dependencies.

Note 2: My count of ALL_SOURCE has 150,000 lines of custom code (a large portion of which was written in 1993. And over 1,000,000 lines of code where 80% is generated code (but still the functionality will need to be replicated in one manner or another).

Also - see my commentary regarding SQL coding standards, the lines of code counts include one line per column in a select statement (wich is way to much white space imo).

I'm not sure any line counter will break out individual functions inside modules/containers/packages but I'm far from an Expert in all languages.

I'm also not sure you need it. For your exercise: You probably wouldn't have two developers working on two different functions inside the same package. I'm not sure how that would even work. Two people shouldn't check out the same package because you cannot check back in 'sections'. You own it ALL or nothing.

Also: It is how many man hours to maintain X lines of code in total?

ASKER

>>I'm not sure any line counter will break out individual functions inside modules/containers/packages
- yeah, I see that, and really what I was trying to do was get line counts per package, as I thought they were tied to a procedure / function within a package. So I actually have what I needed!!!!

>>I'm also not sure you need it.
- agree!

>>how many man hours to maintain X lines of code in total?
- A LOT. and like all legacy systems, it's all hands on deck maintaining the system - - - basically no one doing new development.

>>- A LOT

Sorry. That wasn't supposed to be a question. I was trying to point out that two functions with 100 lines each and a package with 200 lines is the same thing. I was supporting the argument that you didn't need function lines inside a package. Poorly but trying anyway.

ASKER

ok, point well taken.

awking00

I've been following this thread with some personal interest. We have a project that maintains a database with some 250 tables. The tables are populated with data that comes in in xml format for which we have accompanying schema definitions (xsds). Each time a new schema is published, we have to analyze it to see if any changes to the database are required as a result while still maintaining backward compatibility with previous schemas. Such changes require the need to add or modify ddl code for any changes to the database and modifying or adding any needed functions, procedures, or packages to load the data. In the beginning of the project (about 13 years ago), this was all done manually with some 9 or 10 programmers performing the analysis and writing the code. In most years there have usually been three or four new schemas published (although there have been as many as a dozen) and it typically took 3-4 months for the programmers to complete the job. Some years ago, we developed Java applications that keep track of the "schema evolution" and maps the various xml xpaths and their elements to the database tables and columns. Over the past few years we have made enhancements to the applications to the point that it now not only analyzes the type of changes made, but also writes the newly required code for the database changes and loading of the data. For our most current release of the database, the entire process took 45 seconds! However, we were still required to cost out the new release process and as part of that had to supply the number of lines of code on a workbook estimate. To meet that requirement, we then created another Java utility that counted the lines of code from all of our Java written code. The irony of it all is that it takes the utility that counts lines 1 1/2 minutes or twice a long as it takes to write the code in the first place. Legacy systems are like that proverbial horse that can be led to water but ...