Analyze Stored Procedure code?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Analyze Stored Procedure code?

  1. #1
    Join Date
    Feb 2006
    Location
    Iowa
    Posts
    7

    Analyze Stored Procedure code?

    I have been assigned to update a stored procedure. I am finding some variables that have been declared but are not used in the code. I am using Toad 8.6.0.38.

    Is there some program or procedure that can be run to analyze or review the code and tell me things that need to be cleaned up?

    Sure would make life easier!!

    Thanks!

  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    3,972
    You could comment out all of the variables that you think aren't being used, and then try to compile the code. Just be careful about variables declared in the package header, they could get used anywhere, by pl/sql or non-pl/sql code.
    this space intentionally left blank

  3. #3
    Join Date
    Feb 2006
    Location
    Iowa
    Posts
    7
    I was hoping for something that would do all the work for me...
    ;-) I've been doing the FIND to see if the variables are referenced anywhere else in code and that is painful...as in 'I don't want to do that anymore!'

    I'll try your method. Thanks!!

  4. #4
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    3,972
    You can try dba_dependencies.
    this space intentionally left blank

  5. #5
    Join Date
    Feb 2006
    Location
    Iowa
    Posts
    7
    tell me about dba_dependencies...

  6. #6
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684
    Tim...
    OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
    OCA PL/SQL Developer
    Oracle ACE Director
    My website: www.oracle-base.com
    My blog: www.oracle-base.com/blog

  7. #7
    Join Date
    Jun 2005
    Location
    London, UK
    Posts
    159
    Surely TOAD and similar tools report dependencies already? Being familiar with the data dictionary is no bad thing though.

    If there are multiple application schemas, cross-schema dependencies can get hidden due to permissions, so it can be an idea to check them from a DBA account.

Posting Permissions

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



Click Here to Expand Forum to Full Width