Search through code in all triggers & store procedures: Oracle?

Posted on 2012-08-23
Last Modified: 2012-08-23
I am using Oracle 9 and10.  We have hundreds of store procedures and triggers.  Is there a way to search through all of the code?  I would hate to open each one just to look for a string.

Question by:jkit001
    LVL 36

    Expert Comment

    by:Geert Gruwez
    yes: Toad

    you can select what code you want to look in and what schemas you want to look
    LVL 73

    Accepted Solution

    select * from dba_source where text like '%your string goes here%'
    LVL 76

    Expert Comment

    by:slightwv (䄆 Netminder)
    Small correction to the post above:

    select * from dba_source where lower(text) like lower('%your string goes here%')
    LVL 73

    Expert Comment

    no need to correct anything.

    if you want an exact string search do not use LOWER

    if you do not want an exact string search, then you can

    also note, if you don't have access to dba_source, you could try all_source instead.
    but, despite the name "all", you might miss some objects

    Author Closing Comment

    This works for me.  Thanks.

    Featured Post

    PRTG Network Monitor: Intuitive Network Monitoring

    Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

    Join & Write a Comment

    Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
    This post first appeared at Oracleinaction  ( Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
    Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
    This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now