This content has been marked as final. Show 6 replies
1 person found this helpful
Is there a Query to find a String within the Wohle Database, then i dont know which Table holds this Information.No, but you could write Dynamic SQL to complete a brute force search.
Post Operating System (OS) name & version for DB server system.
Post results of
SELECT * from v$version
Edited by: sb92075 on Feb 2, 2010 7:43 AM
Hey checkout the below link, but be careful before using that procedure.
thanks this is what i was looking for.
Since I have a new website, please update the link [http://www.renetta.nl/libphp/search.php] to [http://www.renetta.nl/kennisbank/oracle/database-search?lang=en]
Edited by: user11995516 on 23-mrt-2010 2:03
I check the link and i found useful in one of our application. Can you post the another new script for searching in this case. Supose there are 1000 tables in a schema. I want to search a candidate name and father name, simantenously; i mean searching value will be in two different columns. Even till now i have'nt run your code, but if could possible; please post here/your site.
Sorry for my late reply. It seems I don't get notified with new posts...
My script defines if a search string is a varchar or a number. If it is a number, all numeric columns in that schema are searched for that value.
If it a (var)char, all columns with %CHAR% datatype (except 'VARCHAR2_TABLE') are searched for that value. LOBs are excluded from the script, but maybe I will build that in a while. Time is my biggest enemy ;-)
I can understand the need to search double values in one search, but the chance to find one value is bigger than finding two different values in one search.
I will try to explain here:
table tab1 in schema rene
col1 name surname
___ _____ _______
1 john doe
2 johnny depp
3 johnny crockett
4 rene zijlstra
Executing search.sql in schema rene for string 'john' will return: RENE.TAB1.NAME: 1 (schema, tablename, number of values)
searching for string "johnny" in schema RENE returns: RENE.TAB1.NAME: 2
This means is you search for a string in the database you manually have to query those tables.
It does not return the value in those columns or all columns of that table. You could let the script return rowid's if you want. Open the script, go to line 70 and 71 and replace
l_cnt_stmt varchar2(200) := 'select count(*) from "'||p_schema||'"."'||p_table||'" where lower('||p_column||') like :b3';
l_cnt_stmt varchar2(200) := 'select rowid from "'||p_schema||'"."'||p_table||'" where lower('||p_column||') like :b3';
You can make it tailor made to your own needs, just leave the header intact.
for the record: If you have a database schema with thousands of tables with milions of records, it will take a while. I advise you to do that in a quit moment or clone your database to another server and do the search there. That prevents you from being lynched ;-)
Edited by: user11995516 on 23-mrt-2010 1:57