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>
Akdora's Blog 
hey what about triggers? all_triggers.trigger_body 🙂
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;
I also updated the post
Thanks Tonguc
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
Can I do the same with all table ? very new to ORACLE.
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.
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 !
Interesstin but it doesn’t anymore.
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
Wonderfuuuuuuuuuuul
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
take care with upper case
IF lower(v_line) LIKE ‘%’ || lvsearch || ‘%’
where it is used packages,procedures,triggers,cursors