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;

Select allOpen in new window