• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 628
  • Last Modified:

Oracle Text and Syntax PL/SQL

All,

I am just developing this application in APEX and using Oracle Text and for some reason my syntax seems off as I am receiving:

failed to parse SQL query:
ORA-00904: "V_SEARCH_STRING": invalid identifier

Below is the code and at the bottom where I reference v_search_string I thought I had the right syntax and just not sure what is wrong. Thanks for your help in advance.

 
DECLARE
   v_query   VARCHAR2 (4000);
   v_search_string VARCHAR2(4000);

BEGIN
   
   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>';
  

 v_query := 'SELECT   ';

   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

0
mjfigur
Asked:
mjfigur
  • 3
  • 2
1 Solution
 
slightwv (䄆 Netminder) Commented:
Variables aren't exposed in strings.

Either use a bind variable or concatenate it.

|| 'WHERE contains (document, ' || v_search_string || ', 1) > 0 ';
0
 
mjfigurAuthor Commented:
I am so sorry to be such a pain for some reason I am now getting:

ORA-00936: missing expression
0
 
slightwv (䄆 Netminder) Commented:
Not a big deal.  We all make mistakes when learning.  

Try this:
|| 'WHERE contains (document, ''' || v_search_string || ''', 1) > 0 ';

If that doesn't work, capture/display the string v_query and see what the syntax being generated is.  There has to me a problem in there somewhere.

0
 
mjfigurAuthor Commented:
You are the greatest forever in debt thank you again!
0
 
slightwv (䄆 Netminder) Commented:
Glad to help!
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now