We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Query Progression PL/SQL Syntax

mjfigur
mjfigur asked
on
Medium Priority
875 Views
Last Modified: 2012-05-11
I cannot thank everyone enough for their help and I promise the last question I should have on this project. The issue is just that I am learning PL/SQL and my ablity to understand the syntax is still very undeveloped.



I have the following PL/SQL which is currently working well:
DECLARE
   v_query   VARCHAR2 (4000);
   v_search_string VARCHAR2(4000);
   
BEGIN
   v_query := 'SELECT   ';

  v_search_string := '<query>
   <textquery>
     <progression>
       <seq>'||:p1_search_string||'</seq>
       <seq>'||replace(:p1_search_string,' ',' $')||'</seq>
       <seq>'||replace(:p1_search_string,' ',' ?')||'</seq>
     </progression>
   </textquery>
</query>';


   IF :p1_search_string IS NOT NULL
   THEN
      v_query := v_query || 'score (1) relevance, ';
   ELSE
      v_query := v_query || '''100%'' relevance, ';
   END IF;

   v_query :=
      v_query || 'SUBSTR (website, INSTR (website, ''/'') + 1) '
              || 'document,' || 'ID, ';

   IF :p1_search_string IS NOT NULL
   THEN
      v_query :=
            v_query
         || 'NVL2 '
         || '(:p1_search_string, '
         || 'ctx_doc.snippet '
         || '(''docsx'', '
         || 'ROWID, '
         || 'NVL (:p1_search_string, ''%''), '
         || '''<span style="color:darkgreen;'
         || 'font-weight:bold;font-size:14px;">'', '
         || '''</span>'' '
         || '), '
         || 'NULL '
         || ') snippet ';
   ELSE
      v_query := v_query || 'NULL snippet ';
   END IF;

   v_query := v_query || 'FROM docs ';

   IF :p1_search_string IS NOT NULL
   THEN
      v_query :=
            v_query
         || 'WHERE contains (document, ''' || v_search_string || ''', 1) > 0';
   END IF;

   v_query := v_query || 'ORDER BY 1 DESC ';
RETURN (v_query);
END;

Open in new window



I would like to add:
<seq>(NEAR((:p1_search_string),15,TRUE)) </seq>
 however whenever I try and add that into the <query> portion of the code I recieve errors stating that NEAR is not defined. For some reason I just cannot get the syntax correct.

Below is an example of what the code looks like in SQL and works for the below example:

 
select SCORE(1),website, document from docs WHERE CONTAINS(document,
'<query>
<textquery>
<progression>
<seq>(borrower solicitation) </seq>
<seq>($borrower $solicitation) </seq>
<seq>(NEAR((borrower,solicitation),15,TRUE)) </seq>
<seq>(NEAR(($borrower,$solicitation),15,TRUE)) </seq>
<seq>(borrower% solicitation%) </seq>
<seq>(FUZZY(borrower,,,WEIGHT) FUZZY(solicitation,,,WEIGHT))</seq>
</progression>
</textquery>
 <score datatype="INTEGER" algorithm="COUNT"/>
</query>', 1)>0
Order by SCORE(1) desc

Open in new window

Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
Never a problem.  There isn't a lot of good Text info out there.

As you know by your previous question, I just learned about progressive regression but I doubt the NEAR function is compatible.

Text doesn't like to mix and match features.  Does the docs suggest you can use near?

You might want to ask Oracle Support on this one.

Author

Commented:
Thanks, yes here is the documentation from Oracle 10g that describes the use of near, however sadly they never show the syntax for using PL/SQL.

  Oracle Text Docmentation
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
But that doesn't show the normal 'contains' use of near where you can specify word proximity and preserve order.

Author

Commented:
Right good point did not catch that one. I might have to reach out to Oracle Support on that one or dig deeper into the documentation.
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
lol... should have just tried it.

NEAR syntax is allowed.  The error must be elsewhere.

I tested the below code in 10.2.0.3 and 11.2.0.2

drop table tab1 purge;

create table tab1(col1 varchar2(50));

insert into tab1 values('Fred Flintstone');
insert into tab1 values('Barney Rubble');
commit;
create index tab1_idx on tab1(col1) indextype is ctxsys.context;

select col1 from tab1 where contains(col1,'
<query>
   <textquery>
     <progression>
		<seq>(fred flintstone) </seq>
		<seq>($fred $flintstone) </seq>
		<seq>(NEAR((fred,flintstone),15,TRUE)) </seq>
		<seq>(NEAR(($fred,$flintstone),15,TRUE)) </seq>
		<seq>(fred% flintstone%) </seq>
		<seq>(FUZZY(fred,,,WEIGHT) FUZZY(flintstone,,,WEIGHT))</seq>
     </progression>
   </textquery>
</query>') > 0;

Open in new window

Author

Commented:
That is great to hear and should be a major help with search queries! What would the syntax of the NEAR statement be in PL/SQL? Would it be a situation  where I would need to find the variable or concatenate. Just always have an issue of getting my apostrophes in the right area.
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
>What would the syntax of the NEAR statement be in PL/SQL?

Should be the same.

>>Would it be a situation  where I would need to find the variable or concatenate

The answer to this is:  It depends.  You will likely need to dynamically build the string like you have started doing.  I had to...

With the template example I mentioned in http:#a35466692 it should get a lot simpler.

>>Just always have an issue of getting my apostrophes in the right area.

Just remember:  Use two to get one!

Author

Commented:
Thank you so much it appears that the transform is much easier to use than relaxation as you have to use explicit replace functions in the relaxation method. Thanks again.
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
Glad to help!

Besides, from your previous question:  Progression is new to me so I'm learning as well!  
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.