Search from database from multiple tables using mvc and oracle

15 tables * 10 columns each = 150 columns (for example). Which ones of them do you want to search? All of them? Only some of them?

If ALL, you'd loop through all tables and columns (USER_TABLES joined with USER_TAB_COLUMNS) and search for that string. If SOME of the columns, you'd include those columns into the WHERE clause of the cursor FOR loop's SELECT statement. Any option you choose, it smells like a dynamic SQL.

Here's an example of how I'm doing it, searching all tables that have a column named TELEPHONE (telephone number); search string is "654" with the LIKE operator, so that it returns all tables that contain the TELEPHONE column and telephone number contains 654. The result is displayed with the DBMS_OUTPUT.PUT_LINE (as I'm running it from SQL*Plus). Your output will, probably, be something else.

Have a look, adjust it if necessary.

DECLARE  l_str VARCHAR2(500);  l_cnt NUMBER := 0;BEGIN  FOR cur_r IN (SELECT u.table_name, u.column_name                FROM user_tab_columns u, user_tables t                WHERE u.table_name = t.table_name                  AND u.column_name = 'TELEPHONE'                                                            )  LOOP    l_str := 'SELECT COUNT(*) FROM ' || cur_r.table_name ||             ' WHERE ' || cur_r.column_name || ' like (''%654%'')';    EXECUTE IMMEDIATE (l_str) INTO l_cnt;    IF l_cnt > 0 THEN       dbms_output.put_line(l_cnt ||' : ' || cur_r.table_name);    END IF;                           END LOOP;END;

You can also create a table that has two columns,

CREATE TABLE table1(value_col VARCHAR2(4000), query_col VARCHAR2(4000));

Use the PL/SQL Littlefoot did, modify it a little bit so that it will insert data to the table

So, if you perform a search you will just have to use query like below

SELECT query_col  FROM table1 WHERE value_col LIKE '%'||INPUT_TEXT||'%';

And for the query_col value, you can use it to get the REF CURSOR records and display it in your front end.

According your searching on one table, you should use union all like this below.

select c1, c2, c3, c4 from (    select distinct column1 as c1, column2 as c2, column3 as c3, column4 as c4    from table1 where column1 like '%'|| para ||'%' or column2 like '%'|| para ||'%' or column3 like '%'|| para ||'%' or column4 like '%'|| para ||'%'    union all    select distinct  column1 as c1, column2 as c2, column3 as c3, '' as c4    from table2 where column1 like '%'|| para ||'%' or column2 like '%'|| para ||'%' or column3 like '%'|| para ||'%'    union all    select distinct  column1 as c1, column2 as c2, column3 as c3, '' as c4    from table3 where column1 like '%'|| para ||'%' or column2 like '%'|| para ||'%' or column3 like '%'|| para ||'%')tbl.

Hope this will help.