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; 2 Answers
Answers 1
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; Answers 2
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.
0 comments:
Post a Comment