1 Reply Latest reply: May 17, 2012 4:42 PM by Barbara Boehmer RSS

    CONTAINS with null or nvl

    716769
      I'm triyg to use the CONTAINS resource on a query with this syntax:

      where CONTAINS(ENAME, &P_SEARCH, 1) > 0;

      It works perfectlly always when &P_SEARCH has a value.

      When i try to use nvl CONTAINS(ENAME, NVL(&P_SEARCH,ENAME), 1) > 0 or CONTAINS(ENAME, {&P_SEARCH | ENAME}, 1) > 0 it gives errors.

      What's the correct syntax to prevent that &P_SEARCH has no value?

      Regards.

      Luis.
        • 1. Re: CONTAINS with null or nvl
          Barbara Boehmer
          SCOTT@orcl_11gR2> CREATE INDEX ename_idx ON emp (ename)
            2  INDEXTYPE IS CTXSYS.CONTEXT
            3  /
          
          Index created.
          
          SCOTT@orcl_11gR2> VARIABLE p_search VARCHAR2(100)
          SCOTT@orcl_11gR2> EXEC :p_search := 'FORD'
          
          PL/SQL procedure successfully completed.
          
          SCOTT@orcl_11gR2> SELECT empno, ename FROM emp
            2  WHERE  :p_search IS NOT NULL
            3  AND    CONTAINS (ename, :p_search, 1)>0
            4  /
          
               EMPNO ENAME
          ---------- ----------
                7902 FORD
          
          1 row selected.
          
          SCOTT@orcl_11gR2> EXEC :p_search := NULL
          
          PL/SQL procedure successfully completed.
          
          SCOTT@orcl_11gR2> SELECT empno, ename FROM emp
            2  WHERE  :p_search IS NOT NULL
            3  AND    CONTAINS (ename, :p_search, 1)>0
            4  /
          
          no rows selected