[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 821
  • Last Modified:

Query Progression PL/SQL Syntax

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

0
mjfigur
Asked:
mjfigur
  • 6
  • 4
1 Solution
 
slightwv (䄆 Netminder) 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.
0
 
mjfigurAuthor 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
0
 
slightwv (䄆 Netminder) Commented:
But that doesn't show the normal 'contains' use of near where you can specify word proximity and preserve order.
0
Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

 
mjfigurAuthor 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.
0
 
slightwv (䄆 Netminder) Commented:
Well, like most Text things in Oracle, the documentation is pretty much non-existent.

I've been trying to locate the exact syntax on what is allowed in the textquery XML fragment.

Bad news:  Cannot find anything....yet.  I'll keep looking.

There is good news:
It appears your query gets even easier

Query Rewrite Template

Use this template to automatically write different versions of a query before you submit the query to Oracle Text. This is useful when you need to maximize the recall of a user query. For example, you can program your application to expand a single phrase query of 'cat dog' into the following queries:

There are options to add prefix and suffix to the terms:
prefix   Specifies the literal string to be prepended to all terms.
 
suffix   Specifies the literal string to be appended to all terms.
 
connector   Specifies the literal string to connect all terms after applying the prefix and suffix.
 




0
 
slightwv (䄆 Netminder) 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

0
 
mjfigurAuthor 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.
0
 
slightwv (䄆 Netminder) 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!
0
 
mjfigurAuthor 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.
0
 
slightwv (䄆 Netminder) Commented:
Glad to help!

Besides, from your previous question:  Progression is new to me so I'm learning as well!  
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now