Search from database from multiple tables using mvc and oracle

Question

I have a requirement, where I am not able to trace how to make this functionality. Because here what I want is.

I will have a search Icon, on click of which a textbox will be opened. Now user can insert any text into the textbox and on search click, it will search from the tables of the databases which consist of 12-15 tables in oracle

So my issue here is, how to proceed this with and is it logically correct to do like this. Or please suggest any other way to implement this.

Please suggest

UPDATE

I have done this for One table, but I want this to work for as many tables I have in future.

PROCEDURE GET_SEARCH_DATA
 (
  P_INPUTTEXT IN NVARCHAR2,
  P_RETURN OUT SYS_REFCURSOR 
 )

AS

BEGIN
   OPEN P_RETURN FOR

 SELECT DISTINCT APP_MST_ID, APPLICATIONNAME, PROJECTNO, VSS_FOLDER_LOC 
   FROM APPLICATION_MASTER 
  WHERE APPLICATIONNAME LIKE  '%'|| P_INPUTTEXT || '%'
     OR PROJECTNO LIKE '%' ||  P_INPUTTEXT || '%'
     OR VSS_FOLDER_LOC LIKE '%' ||  P_INPUTTEXT || '%';

END;

Which columns in all tables are you wanting to search? all of them ? ids, dates,numbers, or is there some segmented set ? — Kris Rice
– Kris Rice, Commented Feb 7, 2018 at 14:30
@KrisRice: Almost all coumns, except Id's and dates wont be searched. can u help me with this ? — HEEN
– HEEN, Commented Feb 8, 2018 at 5:03
So, some questions: 1. Is it necessary to have a procedure with SYS_REFCURSOR out parameter? 2. Do you need to do this search in all 15 tables with only one request to DB? — Dmitriy
– Dmitriy, Commented Feb 8, 2018 at 12:10
@Dmitry: No, its not necessary, I just added so that I can return some value and show in front end. and for your second question, yes because there might be data in any table so how should I do for that. ? — HEEN
– HEEN, Commented Feb 8, 2018 at 12:15
I've read the question once again, it seems I didn't notice before: if in the future a new table will appear, the query should work with it too, right? In this case, how do you define, in which table you have to search? In all tables of a certain schema or any another way? — Dmitriy
– Dmitriy, Commented Feb 8, 2018 at 12:38

Littlefoot · Accepted Answer · 2018-01-30 06:23:47Z

2

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;

answered Jan 30, 2018 at 6:23

Littlefoot

144k15 gold badges41 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

HEEN Over a year ago

thanks for your reply and answer, but is this the accurate way to do this or do you have any other way for implementing this ? any suggestion would be welcomed

Littlefoot Over a year ago

Well, that's how I do it. Someone else might suggest another approach, so - be patient.

eifla001 · Accepted Answer · 2018-02-09 09:31:42Z

0

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.

answered Feb 9, 2018 at 9:31

eifla001

1,1578 silver badges9 bronze badges

Comments

Ko Min · Accepted Answer · 2018-02-14 04:59:41Z

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.

Collectives™ on Stack Overflow

Search from database from multiple tables using mvc and oracle

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related