Oracle Text and Syntax PL/SQL

Posted on 2011-04-25
Last Modified: 2012-05-11

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.

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

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

 v_query := 'SELECT   ';

   IF :p1_search_string IS NOT NULL
      v_query := v_query || 'score (1) relevance, ';
      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
      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 ';
      v_query := v_query || 'NULL snippet ';
   END IF;

   v_query := v_query || 'FROM docs ';

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

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

Open in new window

Question by:mjfigur
    LVL 76

    Expert Comment

    by:slightwv (䄆 Netminder)
    Variables aren't exposed in strings.

    Either use a bind variable or concatenate it.

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

    Author Comment

    I am so sorry to be such a pain for some reason I am now getting:

    ORA-00936: missing expression
    LVL 76

    Accepted Solution

    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.


    Author Closing Comment

    You are the greatest forever in debt thank you again!
    LVL 76

    Expert Comment

    by:slightwv (䄆 Netminder)
    Glad to help!

    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
    Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
    This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
    This video shows how to recover a database from a user managed backup

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now