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

I want to write a report of all packages and their associated functions and procedures, to do a count of lines.

So

pkg_a         procedure_a        1,000
pkg_a         procedure_b           888
pkg_a         function_a               132
etc.
all packages

ALL_SOURCE gives me line counts but not parent relationships. ALL_OBJECTS

How can I get this parent child relationship? I can't see any relationships like through ALL_SOURCE or ALL_OBJECTS, just the objects themselves. Also looked at ALL_PROCEDURES and USER_IDENTIFIERS (no data).

I
Gadsden ConsultingIT SpecialistAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
>>to do a count of lines.

Careful of this.


create or replace function line_count return number
is
begin
/*Here are some comments

























*/
return 1;
end;
/

show errors


select count(*) from dba_source where name='LINE_COUNT';


Function created.

No errors.

  COUNT(*)
----------
        32

SQL>

Open in new window



>>How can I get this parent child relationship?

Check out DBA_DEPENDENCIES:
https://docs.oracle.com/en/database/oracle/oracle-database/12.2/refrn/DBA_DEPENDENCIES.html#GUID-D74F9AD4-387A-4773-8C40-337F4152645F

The problem you will run into is if any of the code uses dynamic SQL.  There is no way to find dependencies with dynamic SQL.  You might use string searches for code containing the same string as a procedure/??? name but that is also flawed because of comments.

For example:
begin
...
execute immediate ' select function_name from dual' into some_variable;
...
end;

Open in new window



function_name will not show up in DBA_DEPENDENCIES.

begin
--I based this code off of function_name
end;

Open in new window


If you search dba_source for function_name, it will return the above code but it is a false positive.
0
Gadsden ConsultingIT SpecialistAuthor Commented:
slightvw - thanks. short answer - it's not easy!

I'll dig into this tomorrow and post follow-up questions.
0
Geert GOracle dbaCommented:
some developers, like the ones from oracle, put a big piece of comment in the package header
that might by a very idea

you'd just have to read the header comment then

don't really see what the point is for a line count
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

johnsoneSenior Oracle DBACommented:
And, if the code is wrapped, then it is impossible to get anything from it.  Including line counts.
0
slightwv (䄆 Netminder) Commented:
Excellent point johnsone.  I forgot all about WRAP.

>>don't really see what the point is for a line count

It used to be used as an estimator for level of effort for costing and forecasting.  X Developers could write/maintain Y lines of code.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Gadsden ConsultingIT SpecialistAuthor Commented:
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).
0
awking00Information Technology SpecialistCommented:
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?
0
Gadsden ConsultingIT SpecialistAuthor Commented:
Thanks for your comments!
0
Geert GOracle dbaCommented:
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
0
slightwv (䄆 Netminder) Commented:
>>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.
0
Gadsden ConsultingIT SpecialistAuthor Commented:
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).
0
slightwv (䄆 Netminder) Commented:
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?
0
Gadsden ConsultingIT SpecialistAuthor Commented:
>>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.
0
slightwv (䄆 Netminder) Commented:
>>- 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.
0
Gadsden ConsultingIT SpecialistAuthor Commented:
ok, point well taken.
0
awking00Information Technology SpecialistCommented:
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 ...
0
Gadsden ConsultingIT SpecialistAuthor Commented:
>>For our most current release of the database, the entire process took 45 seconds!
- interesting, and 45s - suh-weet!

>>then created another Java utility that counted the lines of code from all of our Java written code.
- nice ...

>>Legacy systems
- Managers and consultants love legacy systems, it keeps them busy! Management - not so much ...
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.