I am using oracle text index of type ctxcat, but I am new oracle text. I am able to query the table and getting the right results. But we want the results in below order.
If the search string contains two strings then order of the results should be,
In top position: Results that have the exact match to the input text
Then: results that have both words
And at the end: results that have at least one of the two words.

Hello Barbara,
Thanks for your quick reply. The solution worked for me. I tried with CATSEARCH.
I am able to sort the results using the order of sequence in progression. But I would like to know the reason that why we do we need decode in order by clause.

Without the DECODE, the progressive relaxation query returns the rows that match the first criteria, which is word1 immediately followed by word2 in any order, rows 1, 2, and 3 below, followed by the rows that match the second criteria, word1 and word2, in any order, row 4 below, followed by the rows that match the third criteria, either word1 or word2, rows 5, 6, 7, and 8 below.

If you want to have the exact match, row 3 above, in the first position, then you need to use DECODE to identify that match and assign a row number of 0 that will rank before the other row numbers. Note that the nested sub-query that I used before does not appear to be necessary.

Thanks for your explanation. I understood concept.
When I try to add <seq>word1 AND word2</seq> only without other seq's it is not giving any results. Please provide the solution, if we want to check whether the both words exists in any order what would be the condition.
I Tried as <seq>word1 AND word2 WITHIN name</seq>, here name is the node of xml, which is existing in test_col. I am adding the data in test_col in xml format.

One more thing is, After adding the sequences, I have some conditions which has to be applied,
For ex: if the search text present in first name, last name or middle name exists, then results has to be displayed, along with that in the criteria mentioned as country is USA, then data has to be selected only for USA, not for other countries. If I add one more seq then it is applying "or" condition and not the "And" condition. So please provide me the solution for this as well.

...When I try to add <seq>word1 AND word2</seq> only without other seq's it is not giving any results....

Then, you are doing something wrong. It works for me, as demonstrated below. You need to start providing a copy and paste of a run of a script for table creation, data insertion, index creation, and query, as I have provided below, so that we can see what you are doing wrong.

...I Tried as <seq>word1 AND word2 WITHIN name</seq>, here name is the node of xml, which is existing in test_col. I am adding the data in test_col in xml format.

Catsearch does not support WITHIN, even with query template. You will need to use a CONTEXT index with a section group and sections, in order to search WITHIN specific sections using CONTAINS, as demonstrated below.

...After adding the sequences, I have some conditions which has to be applied,
For ex: if the search text present in first name, last name or middle name exists, then results has to be displayed, along with that in the criteria mentioned as country is USA, then data has to be selected only for USA, not for other countries. If I add one more seq then it is applying "or" condition and not the "And" condition. So please provide me the solution for this as well....

The following uses the CONTEXT index with section group from the previous example. The first query lists only what you asked for. The second query uses that criteria as the only sequence in a progressive relaxation. The third query uses that sequence as the first of multiple sequences, so that those rows are returned on top. If you want the country restriction applied to the other sequences, then you will need to add it, as in the first sequence.