Ask Question
Help Others
Home › SQL & PL/SQL

Get Total Number of Lines in Functions and Procedures

Tue, 07/08/2008 - 7:52pm — Lepa
Your rating: None Average: 5 (1 vote)

To get the total number of lines inside a function, trigger, procedure, ect. We need to run our query against the
ALL_SOURCE table (describes the text source of the stored objects accessible to the current user). Or against the
USER_SOURCE table (describes the text source of the stored objects owned by the current user. Its columns (except for OWNER) are the same as those in ALL_SOURCE.

Counting the total lines is a no brainier, we just use oracle's max function and group by name and type. To get the total number of lines without the empty or "space" lines, I've used the DECODE function along with REPLACE and chr(10), the New Line character.

 SELECT name                 ,
  type                       ,
  MAX(line) AS LINE_COUNT,
  COUNT(
  DECODE (REPLACE(text, chr(10), ''), NULL, NULL, '1')) AS NO_EMPLY_LINE_COUNT
   FROM all_source
--if you run this SQL against USER_SOURCE you can omit the "where owner = :1"
WHERE owner = :1
GROUP BY name,
  type;

NAME                         TYPE         LINE_COUNT  NO_EMPLY_LINE_COUNT
---------------------------- ------------ ----------- -------------------
REPCAT$_OBJECT_NULL_VECTOR   TYPE         10          10                  
ORA$_SYS_REP_AUTH            PROCEDURE    15          15                  
REPCATLOGTRIG                TRIGGER      5           5                  
DEF$_PROPAGATOR_TRIG         TRIGGER      13          12                  
DBMS_REPCAT_AUTH             PACKAGE BODY 1           1                  
DBMS_REPCAT_AUTH             PACKAGE      1           1                  

6 rows selected

Bookmark/Search this post with
  • Buzz Up!
  • del.icio.us
  • Digg
  • Facebook
  • Google Buzz
  • Reddit
  • StumbleUpon
  • Technorati
  • Twitter
  • Yahoo
Please try to help out with unanswered topics on the forum. Chances are you have had the same issue/question some time in your IT career!

Comments

Have a question? Please ask it on the forum instead.
Wed, 08/12/2009 - 9:14pm
#1
Guest's picture
Guest (not verified)
Re: Get Total Number of Lines in Functions and Procedures

Hi Lepa,
'Total Number of Lines',What does this mean?
please teach me via other explain,thanks.

Top
  • reply
Wed, 08/12/2009 - 9:38pm
#2
Guest's picture
Guest (not verified)
Re: Get Total Number of Lines in Functions and Procedures

Hi Lepa,
I see the mean via inquiry the table,dont need to tell me now.

Top
  • reply

Post new comment

The content of this field is kept private and will not be shown publicly.
CAPTCHA
The question below is to prevent automated spam submissions.
Image CAPTCHA
Enter the characters shown in the image.