Search In Oracle Tables :) February 5, 2009

After I write searching code in PL/SQL blocks. I also  want to add this post.

If you want to find a specific data in Tables, you may check this page:

http://www.adp-gmbh.ch/ora/misc/find_in_tables.html

SQL> set serveroutput on
SQL>
SQL> begin
  2    dbms_output.put_line(find_in_schema(val => ‘147 Spadina Ave’));
  3  end;
  4  /
 
  rowid: AAAC88AAEAAAAAnAAI in LOCATIONS
Success
 
PL/SQL procedure successfully completed
 
SQL>

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

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 🙂