Home » Server Options » Text & interMedia » ORACLE SQL POSSIBE BUG (11G)
ORACLE SQL POSSIBE BUG [message #679253] Wed, 19 February 2020 02:53 Go to next message
andreas18121989
Messages: 2
Registered: February 2020
Junior Member
when query my database table with word "NOR" with CONTAINS command i didn't get result(example:contains(tt_transvalue,'{'||'NOR'||'}') >0 ), the NOR is not a keyword and the escape character not work in my case.
Re: ORACLE SQL POSSIBE BUG [message #679254 is a reply to message #679253] Wed, 19 February 2020 03:19 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
It would help if you posted a test case - create table and insert statements for the data so we can replicate it, along with the exact select statement you are running.

Your complete oracle version (11g has a lot of sub-versions) would also be useful.
Re: ORACLE SQL POSSIBE BUG [message #679255 is a reply to message #679253] Wed, 19 February 2020 03:40 Go to previous messageGo to next message
John Watson
Messages: 8274
Registered: January 2010
Location: Global Village
Senior Member
No errors for me:
orclz> create index ename_ctx on emp(ename) indextype is ctxsys.context;

Index created.

orclz>
orclz> select * from emp where contains(ename,'{'||'NOR'||'}') >0 ;

no rows selected

orclz>
--edit:
And, to do the Moderator bit:
Welcome to the forum. Please read the OraFAQ Forum Guide and How to use code tags and make your code easier to read

[Updated on: Wed, 19 February 2020 03:43]

Report message to a moderator

Re: ORACLE SQL POSSIBE BUG [message #679256 is a reply to message #679255] Wed, 19 February 2020 03:43 Go to previous messageGo to next message
andreas18121989
Messages: 2
Registered: February 2020
Junior Member
The problem is that i don't get any results instead that i have at least one record with NOR value.
Re: ORACLE SQL POSSIBE BUG [message #679257 is a reply to message #679256] Wed, 19 February 2020 03:45 Go to previous messageGo to next message
John Watson
Messages: 8274
Registered: January 2010
Location: Global Village
Senior Member
Quote:
It would help if you posted a test case - create table and insert statements for the data so we can replicate it, along with the exact select statement you are running.

Your complete oracle version (11g has a lot of sub-versions) would also be useful.
Re: ORACLE SQL POSSIBE BUG [message #680529 is a reply to message #679253] Tue, 19 May 2020 12:58 Go to previous message
Barbara Boehmer
Messages: 8922
Registered: November 2002
Location: California, USA
Senior Member
NOR is a default stopword that is ignored and not searched for, unless you specify empty_stoplist in your index creation.

With no stoplist specified using default stoplist:

SCOTT@orcl_12.1.0.2.0> create table test_tab (tt_transvalue  varchar2(30))
  2  /

Table created.

SCOTT@orcl_12.1.0.2.0> insert into test_tab values ('NOR')
  2  /

1 row created.

SCOTT@orcl_12.1.0.2.0> create index test_idx on test_tab (tt_transvalue) indextype is ctxsys.context
  2  /

Index created.

SCOTT@orcl_12.1.0.2.0> select * from test_tab where contains(tt_transvalue,'{'||'NOR'||'}') >0
  2  /

no rows selected

With empty_stoplist:

SCOTT@orcl_12.1.0.2.0> drop index test_idx
  2  /

Index dropped.

SCOTT@orcl_12.1.0.2.0> create index test_idx on test_tab (tt_transvalue) indextype is ctxsys.context
  2    parameters ('stoplist ctxsys.empty_stoplist')
  3  /

Index created.

SCOTT@orcl_12.1.0.2.0> select * from test_tab where contains(tt_transvalue,'{'||'NOR'||'}') >0
  2  /

TT_TRANSVALUE
------------------------------
NOR

1 row selected.
Stoplists are lists of common words that are ignored during indexing. You can create your own or specify empty_stoplist or it uses the default stoplist. To see a list of words in the default stoplist:
SCOTT@orcl_12.1.0.2.0> select spw_word from ctx_stopwords where spw_stoplist = 'DEFAULT_STOPLIST' order by spw_word
  2  /

SPW_WORD
--------------------------------------------------------------------------------
Mr
Mrs
Ms
a
all
almost
also
although
an
and
any
are
as
at
be
because
been
both
but
by
can
could
d
did
do
does
either
for
from
had
has
have
having
he
her
here
hers
him
his
how
however
i
if
in
into
is
it
its
just
ll
me
might
my
no
non
nor
not
of
on
one
only
onto
or
our
ours
s
shall
she
should
since
so
some
still
such
t
than
that
the
their
them
then
there
therefore
these
they
this
those
though
through
thus
to
too
until
ve
very
was
we
were
what
when
where
whether
which
while
who
whose
why
will
with
would
yet
you
your
yours

114 rows selected.

[Updated on: Tue, 19 May 2020 13:04]

Report message to a moderator

Previous Topic: ctxhx process and Oracle Text indexes
Next Topic: Oracle Text provides easy Google-like searching using new query parser
Goto Forum:
  


Current Time: Thu Jun 04 22:44:57 CDT 2020