is there a oracle function to search the tables for keywords?

[mokesql]

hi!

still the same problem. maybe i haven`t explained the problem clear enough. i`m searching for a oracle function to search the tables for keywords. i dont want to make a sql statement. i`m searching for a function which makes everything for me. so as example i want to get all recodsets which contains my name in the table users. and i want that this function searchs in all columns and to give me all recordsets as result which contains my name. i just dont know how this function is called, or if it exists. please please help me

thnx

moke

 

[THoey]

I am not saying that I have seen everything in Oracle, cause I know I haven't. But, I haven't seen a tool that will generate this for you.

You will most likely end up writing an SQL script that will in itself create an SQL script (SPOOL to a file). This first script will get all of your table names and the fields in those tables. The only way I could think of to do the rest (searching every field) would be to concatenate those fields together and then use the LIKE clause to see if your value is in the concatenated string.

Not going to be pretty or easy. But, maybe someone else like Sem, Carp, Karluk or Rcurva (the real Gods of this forum) know a better way... Terry M. Hoey

 

[carp]

Nope - not me! Unless your table is fairly simple (say, two or three columns), this is going to be ugly and you WILL have to build it yourself.

The function could take in the table owner and name as well as the target string as arguments and return whatever you want as a result. Internally, the function would have to go to all_tab_columns or dba_tab_columns, find all of the column names in the table, then build a query that concatenates the columns together and searches through the resultant string for LIKE %your_target%, and formats the results. An "execute immediate" will get the query executed.

Like I said, it's going to be ugly. Good luck!