Akdora’s Blog

Programming, Oracle, Life, Fun

Search In Oracle (All_Source, All_Views, All_Triggers) February 4, 2009

Filed under: Oracle,PL/SQL — Akdora @ 8:54 pm
Tags: , , , , , , , , ,

If you want to search some words in the oracle database that you wrote before. You have two options to check out.
Firstly, we checks the ALL_SOURCES table.

SQL> Desc ALL_SOURCE
Name  Type                            Nullable Default Comments                                                                                                     
—– ————–                   ——– ——- ————————————————————————————————————-
OWNER VARCHAR2(30)      Y                Owner of the object                                                                                          
NAME   VARCHAR2(30)       Y                Name of the object                                                                                           
TYPE     VARCHAR2(12)        Y                Type of the object: “TYPE”, “TYPE BODY”, “PROCEDURE”, “FUNCTION”,  “PACKAGE”, “PACKAGE BODY” or “JAVA SOURCE”
LINE      NUMBER                      Y                Line number of this line of source                                                                           
TEXT     VARCHAR2(4000)  Y                Source text   

 

This table includes following types;

FUNCTION
JAVA SOURCE
PACKAGE
PACKAGE BODY
PROCEDURE
TRIGGER
TYPE
TYPE BODY

But, As you see “VIEWS..” are not in the list.!!! So we also check the ALL_VIEWS table.

We should check also ALL_TRIGGERS table🙂

SQL> desc all_views
Name             Type           Nullable Default Comments                                                   
—————- ————– ——– ——- ———————————————————–
OWNER                                 VARCHAR2(30)                    Owner of the view                                          
VIEW_NAME                     VARCHAR2(30)                    Name of the view                                           
TEXT_LENGTH                  NUMBER         Y                Length of the view text                                    
TEXT                                     LONG           Y                View text                                                  
TYPE_TEXT_LENGTH    NUMBER         Y                Length of the type clause of the object view               
TYPE_TEXT                       VARCHAR2(4000) Y                Type clause of the object view                             
OID_TEXT_LENGTH      NUMBER         Y                Length of the WITH OBJECT OID clause of the object view    
OID_TEXT                         VARCHAR2(4000) Y                WITH OBJECT OID clause of the object view                  
VIEW_TYPE_OWNER  VARCHAR2(30)   Y                Owner of the type of the view if the view is an object view
VIEW_TYPE                    VARCHAR2(30)   Y                Type of the view if the view is an object view             
SUPERVIEW_NAME    VARCHAR2(30)   Y                Name of the superview, if view is a subview                
 
SQL>

For a complete search solution, I wrote a procedure:

CREATE OR REPLACE PROCEDURE prc_search_in_oracle(isearch IN VARCHAR2) IS
--------------------------------------------------------------------------
--                                                                      --       
--     Author       : Ozay AKDORA                                       --
--     Create Date  : 04.02.2009                                        -- 
--                                                                      --
--------------------------------------------------------------------------
     lncount  NUMBER;
     lvsearch VARCHAR2(100) := lower(isearch);
BEGIN
     -----------SEARCH IN ALL_SOURCE-------------------------------
     DECLARE
          CURSOR cur_src(p_search IN VARCHAR2) IS
               SELECT *
                 FROM all_source t
                WHERE lower(t.text) LIKE '%' || p_search || '%';
    
          lrow_src cur_src%ROWTYPE;
     BEGIN
          OPEN cur_src(lvsearch);
          LOOP
               FETCH cur_src
                    INTO lrow_src;
               EXIT WHEN cur_src%NOTFOUND;
               --satiri yazdiriyoruz
               dbms_output.put('Owner:' || lrow_src.owner);
               dbms_output.put(' Name:' || lrow_src.NAME);
               dbms_output.put(' Line:' || lrow_src.line);
               dbms_output.put(' Text:' || lrow_src.text);
               dbms_output.put_line('');
         
          END LOOP;
          lncount := cur_src%ROWCOUNT;
          dbms_output.put_line('');
          dbms_output.put_line(lncount ||
                               ' record(s) was found in ALL_SOURCE table.');
    
          CLOSE cur_src;
     END;
     dbms_output.put_line('');
     dbms_output.put_line('-----------------------------------------------------');
     dbms_output.put_line('');
    
     ------------SEARCH IN ALL_VIEWS----------------------------
     DECLARE
          CURSOR cur_views IS
               SELECT * FROM all_views t;
    
          lrow_views cur_views%ROWTYPE;
          v_line     VARCHAR2(4000);
     BEGIN
          lncount := 0;
          OPEN cur_views;
          LOOP
               FETCH cur_views
                    INTO lrow_views;
               EXIT WHEN cur_views%NOTFOUND;
               --Convert LONG to VARCHAR2
               v_line := substr(lrow_views.text, 1, 4000);
               IF v_line LIKE '%' || lvsearch || '%'
               THEN
                    dbms_output.put('Owner:' || lrow_views.owner);
                    dbms_output.put(' ViewName:' || lrow_views.view_name);
                    dbms_output.put(' Text:' || v_line);
                    dbms_output.put_line('');
                    lncount := lncount + 1;
               END IF;
         
          END LOOP;
          dbms_output.put_line('');
          dbms_output.put_line(lncount ||
                               ' record(s) was found in ALL_VIEWS table.');
    
          CLOSE cur_views;
     END;
     --————SEARCH IN ALL_TRIGGERS—————————-
    DECLARE
         CURSOR cur_trg IS
              SELECT * FROM all_triggers t;
         lrow_trg cur_trg%ROWTYPE;
         v_line   VARCHAR2(4000);
     
    BEGIN
         lncount := 0;
         OPEN cur_trg;
         LOOP
              FETCH cur_trg
                   INTO lrow_trg;
              EXIT WHEN cur_trg%NOTFOUND;
              v_line := substr(lrow_trg.description,
                                                         1,
                                                         4000);
              IF v_line LIKE '%' || lvsearch || '%'
              THEN
                   dbms_output.put('Owner :' || lrow_trg.owner);
                   dbms_output.put(' TriggerName :' || lrow_trg.trigger_name);
                   dbms_output.put(' text :' || v_line);
                   dbms_output.put_line('');
                   lncount := lncount + 1;
              END IF;
        
         END LOOP;
         dbms_output.put_line('');
         dbms_output.put_line(lncount || ' RECORD(s) was FOUND IN all_triggers TABLE.');
         CLOSE cur_trg;
    END;
END prc_search_in_oracle;

 

Let’s try it.

SQL> set serveroutput on
SQL>
SQL> BEGIN
  2       searchoraclesource(isearch => 'state_province');
  3  END;
  4  /
 
 
0 record(s) was found in ALL_SOURCE table.
 
-----------------------------------------------------
 
Owner:HR ViewName:EMP_DETAILS_VIEW Text:SELECT
  e.employee_id,
  e.job_id,
  e.manager_id,
  e.department_id,
  d.location_id,
  l.country_id,
  e.first_name,
  e.last_name,
  e.salary,
  e.commission_pct,
  d.department_name,
  j.job_title,
  l.city,
  l.state_province,
  c.country_name,
  r.region_name
FROM
  employees e,
  departments d,
  jobs j,
  locations l,
  countries c,
  regions r
WHERE e.department_id = d.department_id
  AND d.location_id = l.location_id
  AND l.country_id = c.country_id
  AND c.region_id = r.region_id
  AND j.job_id = e.job_id
WITH READ ONLY
 
1 record(s) was found in ALL_VIEWS table.
 
PL/SQL procedure successfully completed
 
SQL>
SQL>
SQL> BEGIN
  2       searchoraclesource(isearch => 'lvsearch');
  3  END;
  4  /
Owner:HR Name: PRC_SEARCH_IN_ORACLE Line:9 Text:     lvsearch VARCHAR2(100) := lower(isearch);
Owner:HR Name: PRC_SEARCH_IN_ORACLE Line:20 Text:          OPEN cur_src(lvsearch);
Owner:HR Name: PRC_SEARCH_IN_ORACLE Line:60 Text:               IF v_line LIKE '%' || lvsearch || '%'
 
6 record(s) was found in ALL_SOURCE table.
 
-----------------------------------------------------
 
 
0 record(s) was found in ALL_VIEWS table.
 
PL/SQL procedure successfully completed
 
SQL>
 

13 Responses to “Search In Oracle (All_Source, All_Views, All_Triggers)”

  1. hey what about triggers? all_triggers.trigger_body🙂

  2. akdora Says:

    ooh yeah, I forgot it :))
    Then, lets add this to the procedure;

    –————SEARCH IN ALL_TRIGGERS—————————-
    DECLARE
    CURSOR cur_trg IS
    SELECT * FROM all_triggers t;

    lrow_trg cur_trg%ROWTYPE;
    v_line VARCHAR2(4000);
    BEGIN
    lncount := 0;
    OPEN cur_trg;
    LOOP
    FETCH cur_trg
    INTO lrow_trg;
    EXIT WHEN cur_trg%NOTFOUND;
    v_line := substr(lrow_trg.description,
    1,
    4000);
    IF v_line LIKE ‘%’ || lvsearch || ‘%’
    THEN
    dbms_output.put(‘Owner :’ || lrow_trg.owner);
    dbms_output.put(‘ TriggerName :’ || lrow_trg.trigger_name);
    dbms_output.put(‘ text :’ || v_line);
    dbms_output.put_line(”);
    lncount := lncount + 1;
    END IF;

    END LOOP;
    dbms_output.put_line(”);
    dbms_output.put_line(lncount || ‘ RECORD(s) was FOUND IN all_views TABLE.’);

    CLOSE cur_trg;
    END;

  3. akdora Says:

    I also updated the post
    Thanks Tonguc

  4. You might wanna check this one too; Extracting SQL statements from PL/SQL – http://www.l2is.com/apex/f?p=999:3:0::::P3_NAME:ARTICLE65

  5. dwijadas Says:

    Can I do the same with all table ? very new to ORACLE.

  6. Peter Says:

    your search in all_triggers.description may be a mistake. The source can be found in all_triggers.trigger_body. Also you should use upper() or lower() in a consistent manner for the search.

  7. sql_newbees Says:

    your proc compil correctly, however, how will u do to execute it ? and what is ” searchoraclesource ” A TABLE ???
    i ‘m a beginner so i need some help thx !

  8. Khan Says:

    interestin but ti’s doesn’t work anymore,
    I try to compil ur proc : it compils perfectly but how to do to have results., we can do a search it’s not a “searchbot”. what i d like to do is a searchbot in all_source . do u have any idea guy ?
    I hope u can help me.

    Best Regards

  9. parisa Says:

    Wonderfuuuuuuuuuuul

  10. chiya Says:

    how to delete all triggers in a oracle10g database ?
    SQL>trunc all_triggers;
    will work ???

    – i am a student so have little basic knowledge only

  11. take care with upper case

    IF lower(v_line) LIKE ‘%’ || lvsearch || ‘%’

  12. where it is used packages,procedures,triggers,cursors


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s