Home » Other » Training & Certification » Query to implement search functionality
Query to implement search functionality [message #282548] Thu, 22 November 2007 05:41 Go to next message
vamsikgummadi
Messages: 62
Registered: April 2006
Location: United States, MI
Member
Hello all,
Hope all are having a nice time.
This time I came up with one more problem to be sorted out and I need your extended help and cooperation as you all ever did.

The problem is as follows:

I am having a table like Emp table with various fields like Empno, Ename, Job, Sal, Deptno...Now I want to implement a serch functionality on this table. Just by giving EName or Sal or Empno or Job .. I should be able to retrive all the records having the matchingdetails I entered. Similar to searching of matching jobs in a job site or while googling for a particular topic in Google.

I Hope I am clearwith my problem. Please get back if you need some other clarifications from my side. Please also try to send sample code snippets if possible as they will be very helpful to me. Hope to get back from you at the earliest. Thanking you in anticipation.
Regards,
Vamsi K Gummadi.

[EDITED by LF: fixed message formatting]

[Updated on: Thu, 22 November 2007 10:25] by Moderator

Report message to a moderator

Re: Query to implement search functionality [message #282550 is a reply to message #282548] Thu, 22 November 2007 05:48 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member

may be you can try with dynamic sql .


regards,
Re: Query to implement search functionality [message #282563 is a reply to message #282548] Thu, 22 November 2007 06:38 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
Or you could checkout overloading. Or you could ask your teacher/lecturer to give you a hint. (Oops let the cat out of the bag that this is homework! Smile )
Re: Query to implement search functionality [message #282593 is a reply to message #282548] Thu, 22 November 2007 07:55 Go to previous messageGo to next message
vamsikgummadi
Messages: 62
Registered: April 2006
Location: United States, MI
Member
Hello friends,
Thank you for the response. I am now trying to solve the problem using overloading and Dynamic SQL query process suggested. I will get back to you immediately once i get to know the solution. It will also be very helpful to me if yoou can provide the code snippets of the solutions suggested.
Thanking you in anticipation,
Vamsi K Gummadi.
Re: Query to implement search functionality [message #282658 is a reply to message #282593] Thu, 22 November 2007 22:28 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
The multi_column_datastore feature of Oracle Text is ideal for this sort of thing, as shown below.

SCOTT@orcl_11g> ALTER TABLE emp ADD (any_column VARCHAR2 (1))
  2  /

Table altered.

SCOTT@orcl_11g> BEGIN
  2    CTX_DDL.CREATE_PREFERENCE ('your_multi', 'MULTI_COLUMN_DATASTORE');
  3    CTX_DDL.SET_ATTRIBUTE ('your_multi', 'columns', 'ename, sal, empno, job, mgr');
  4  END;
  5  /

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> CREATE INDEX your_index ON emp (any_column)
  2  INDEXTYPE IS CTXSYS.CONTEXT
  3  PARAMETERS ('DATASTORE your_multi')
  4  /

Index created.

SCOTT@orcl_11g> SELECT empno, ename, sal, job, mgr FROM emp WHERE  CONTAINS (any_column, 'JONES') > 0
  2  /

     EMPNO ENAME             SAL JOB              MGR
---------- ---------- ---------- --------- ----------
      7566 JONES            2975 MANAGER         7839

SCOTT@orcl_11g> SELECT empno, ename, sal, job, mgr FROM emp WHERE  CONTAINS (any_column, 'CLERK') > 0
  2  /

     EMPNO ENAME             SAL JOB              MGR
---------- ---------- ---------- --------- ----------
      7369 SMITH             800 CLERK           7902
      7876 ADAMS            1100 CLERK           7788
      7900 JAMES             950 CLERK           7698
      7934 MILLER           1300 CLERK           7782

SCOTT@orcl_11g> SELECT empno, ename, sal, job, mgr FROM emp WHERE  CONTAINS (any_column, '7902') > 0
  2  /

     EMPNO ENAME             SAL JOB              MGR
---------- ---------- ---------- --------- ----------
      7369 SMITH             800 CLERK           7902
      7902 FORD             3000 ANALYST         7566

SCOTT@orcl_11g> 


Re: Query to implement search functionality [message #282759 is a reply to message #282658] Fri, 23 November 2007 06:22 Go to previous messageGo to next message
vamsikgummadi
Messages: 62
Registered: April 2006
Location: United States, MI
Member
Thank you Barbara Boehmer shall work out with your suggestion and get back to you after execution. Is thereno othermethod other than this? I mean to ask about execution of a simple query (or complex) but without using an Index over the table. Is the above scenario possible?
I shall once again than you for your effort in trying to solve my problem.
Regards,
Vamsi K Gummadi.
Re: Query to implement search functionality [message #282806 is a reply to message #282759] Fri, 23 November 2007 11:32 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
There are always various ways to do things. You could do this with a single simple query with an OR condition for each column. Or, you could do it with an overloaded package, as another responder suggested. With a short table like the emp table, any of these will run quickly. However, in the real world, where you have many rows and many columns in the table, you want an index and the context index is the best way to go. That is what it was designed for. Otherwise, your searches could be very slow.

Re: Query to implement search functionality [message #282809 is a reply to message #282658] Fri, 23 November 2007 12:05 Go to previous message
vamsikgummadi
Messages: 62
Registered: April 2006
Location: United States, MI
Member
Yes Barbara Boehmer, you have made the point which needs to be taken care of. Thank you once again for guiding me in the right direction. The suggestions you have given shall be noted down for future reference also.
Regards,
Vamsi K Gummadi
Previous Topic: Problem in the group by
Next Topic: regular expressions
Goto Forum:
  


Current Time: Sat Dec 10 07:15:09 CST 2016

Total time taken to generate the page: 0.05540 seconds