Results 1 to 13 of 13
  1. #1
    Join Date
    Feb 2004
    Posts
    41

    Unanswered: how to find out size of a trigger

    Hi all,

    can i find the size of a trigger which is stored in database?.
    if we can how to do it?.

    Thanks in advance
    'A candle will loose nothing by lighting an another candle'

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Look at the column TRIGGER_BODY in view ALL_TRIGGERS.

  3. #3
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    try dba_source. As a side note remember to try and keep the trigger body as small as possible by calling a stored procedure as it is soft parsed every time it executes I think.

    Alan

  4. #4
    Join Date
    Feb 2004
    Posts
    41
    Thank you for the reply,
    actually from both the table all_triggers and dba_source i can
    find the trigger code, i want to know the size of the trigger in database.
    is there anyway to query for the size of trigger?.
    'A candle will loose nothing by lighting an another candle'

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    It's not clear to me what you mean by the "size" of a trigger. The only "size" a trigger has is the amount of code that defines it - measured in characters, or lines, etc. That you can get by inspecting the code in ALL_TRIGGERS. If that isn't what you want, what is?

  6. #6
    Join Date
    Feb 2004
    Posts
    41
    Hmmm well i am sorry if question is not conveyed properly.
    ok my question is i have a trigger which is in database (user_triggers/all_triggers), i want to know how many lines of code is stored in a database for teh given trigger.
    one way is i can query for the trigger_body and store it in some file and see,
    but i want to query for the number of lines of code, depending on the lines of code i want to get it into some file.

    i hope i am clear with question.
    'A candle will loose nothing by lighting an another candle'

  7. #7
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Number of lines is tricky, number of characters is easy just do a sum(length(trigger_body)) against all_triggers. If you want lines you could use the translate function to remove all characters except \n and then do a length on it.

    Alan

  8. #8
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    The only way to find out is to extract the text in ALL_TRIGGERS.TRIGGER_BODY and count the number of lines. As long as the trigger code doesn't exceed 32K you can so that easily with PL/SQL:
    Code:
    SQL> declare
      2    v_text varchar2(32767);
      3  begin
      4    select trigger_body
      5    into   v_text
      6    from   user_triggers
      7    where  trigger_name = 'EMP_BDS';
      8    dbms_output.put_Line ('Trigger code:');
      9    dbms_output.put_line (v_text);
     10    dbms_output.put_line ('number of lines = '
     11      || to_char(length(v_text) - length(translate(v_text,'x'||chr(10),'x'))));
     12  end;
     13  /
    Trigger code:
    begin
      emp_trg_pkg.emp_tab.delete;
    end;
    
    number of lines = 3
    
    PL/SQL procedure successfully completed.
    You could easily turn that code into a function that returns the line count.

  9. #9
    Join Date
    Feb 2004
    Posts
    41
    ok thank you.
    now i tried to get number of lines of trigger 'TRIG_AUD_CLASSOFSERVICE',
    which has around 2873 lines.

    when i execute the below code, i am getting error.

    declare
    v_text varchar2(32767);
    begin
    select trigger_body
    into v_text
    from user_triggers
    where trigger_name = 'TRIG_AUD_CLASSOFSERVICE';
    dbms_output.put_Line ('Trigger code:');
    dbms_output.put_line (v_text);
    dbms_output.put_line ('number of lines = '
    || to_char(length(v_text) - length(translate(v_text,'x'||chr(10),'x'))));
    end;
    /

    declare
    *
    ERROR at line 1:
    ORA-06502: PL/SQL: numeric or value error
    ORA-06512: at line 4
    'A candle will loose nothing by lighting an another candle'

  10. #10
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Yes, it looks like you have a trigger with more than 32767 characters in it. That requires a more complex approach. I think you can do it via the DBMS_SQL package, which has COLUMN_VALUE_LONG procedure:
    Code:
    PROCEDURE COLUMN_VALUE_LONG
     Argument Name                  Type                    In/Out Default?
     ------------------------------ ----------------------- ------ --------
     C                              NUMBER(38)              IN    
     POSITION                       NUMBER(38)              IN    
     LENGTH                         NUMBER(38)              IN    
     OFFSET                         NUMBER(38)              IN    
     VALUE                          VARCHAR2                OUT   
     VALUE_LENGTH                   NUMBER(38)              OUT
    ... so you can get the first 32K, then the next 32K, until there is no more.

  11. #11
    Join Date
    Feb 2004
    Posts
    41
    Thank you.
    yes i used column_value_long and able to do it.
    'A candle will loose nothing by lighting an another candle'

  12. #12
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    Why would you not just SELECT COUNT(*) FROM USER_SOURCE for the trigger name?

    Querying SOURCE_SIZE from USER_OBJECT_SIZE might give a more meaningful measure than the number of lines of unknown length.

  13. #13
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Quote Originally Posted by WilliamR
    Why would you not just SELECT COUNT(*) FROM USER_SOURCE for the trigger name?
    Mainly because I'm stuck in an 8i time warp. I didn't realise the code now appeared in USER_SOURCE!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •