Advertisement
Guest User

Untitled

a guest
Nov 21st, 2017
60
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
PL/SQL 1.09 KB | None | 0 0
  1. DECLARE
  2.  
  3.   l_query VARCHAR2(4000);
  4.  
  5. BEGIN
  6.   --Sync index to ensure up-to-date records
  7.   ctx_ddl.sync_index('agencies_desc_ctx_idx', '2M');
  8.  
  9.   IF v('P7_A_ID') IS NOT NULL THEN
  10.  
  11.       l_query:=
  12.        'select
  13.        A_ID,
  14.        AGENCY_NAME,
  15.        AGENCY_DESCRIPTION
  16.        from   AGENCIES
  17.        where A_ID = '|| v('P7_A_ID') ||'';
  18.        
  19.         IF v('P7_REPORT_SEARCH') IS NOT NULL THEN
  20.         l_query := l_query||' '||'
  21.        and
  22.         CONTAINS(agency_description, ''fuzzy('|| v('P7_REPORT_SEARCH') ||', 50, 100, weight), relevance'', 1) > 0;
  23.       ';
  24.   END IF;
  25.  
  26.   ELSE
  27.  
  28.       l_query:=
  29.        'select
  30.        A_ID,
  31.        AGENCY_NAME,
  32.        AGENCY_DESCRIPTION
  33.        from   AGENCIES ';
  34.  
  35.       IF v('P7_REPORT_SEARCH') IS NOT NULL THEN
  36.         l_query := l_query||' '||'
  37.        where
  38.         CONTAINS(agency_description, ''fuzzy('|| v('P7_REPORT_SEARCH') ||', 50, 100, weight), relevance'', 1) > 0;
  39.       ';
  40.       END IF;
  41.  
  42.   END IF;
  43.  
  44.   -- test and debug only
  45.     htp.p( l_query );
  46.     htp.p( '<br>' );
  47.  
  48.   RETURN l_query;
  49.  
  50. END;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement