Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Yogesh.

Asked: February 26, 2002 - 2:56 pm UTC

Last updated: September 04, 2007 - 11:48 am UTC

Version: 8.1.7

Viewed 1000+ times

You Asked

Hi Tom,

I was trying following query for intermedia based search and results were absolutely shocking.

SELECT
ID,
NAME
FROM
TEST
WHERE
ID IS NOT NULL AND (( (contains(BODY,' OREGON ' )>0) OR (contains(BODY,' OR ')>0)) )
/

Above query ends the current DB session after returning end of file communication channel error.

Another query

SELECT
ID,
TITLE
FROM
TEST
WHERE
ID IS NOT NULL AND (contains(BODY,' OR ')>0)) )
/

Returns following error

SELECT
*
ERROR at line 1:
ORA-29902: error in executing ODCIIndexStart() routine
ORA-20000: interMedia Text error:
DRG-50901: text query parser syntax error on line 1, column 2

Plz explain.

Regards
Yogesh



and Tom said...

Well, for the EOF on communication channel, I'll have to refer you to support. It seems to be a bug dealing with your invalid query you used. There may be a patch for it.


For the second query with just 'OR' -- you need to read about the query language behind interMedia. OR is a keyword in the search string itself. You need to escape it.

So the search would look like this:

ops$tkyte@ORA817DEV.US.ORACLE.COM> create table t ( id number, name varchar2(80), body varchar2(4000) );
Table created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> insert into t values ( 1, 'a', 'oregon' );
1 row created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> insert into t values ( 1, 'b', 'or' );
1 row created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> insert into t values ( 1, 'c', 'hello' );
1 row created.

ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> create index search_idx
2 on t(body)
3 indextype is ctxsys.context
4 /

Index created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> select * from t
2 where ID IS NOT NULL AND (( (contains(BODY,' OREGON ' )>0) OR (contains(BODY,' {OR} ')>0)) )
3 /

ID NAME BODY
---------- ----- ------------------------------
1 a oregon



Now, why didn't OR come back? It is one of the words in the default stoplist. If you want to index common words like that, you'll need to set up your own stoplist using interMedia as well.

As an aside, it would be better to use interMedia to OR:

ops$tkyte@ORA817DEV.US.ORACLE.COM> select * from t
2 where ID IS NOT NULL AND contains( body, 'oregon or {or} or hello' ) > 0
3 /

ID NAME BODY
---------- ----- ------------------------------
1 a oregon
1 c hello



Rating

  (6 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Great Explaination

Yogesh Bhandarkar, February 28, 2002 - 4:31 pm UTC

Got New direction for working.

thesaurus error

LC, June 10, 2004 - 1:47 pm UTC 

Good day.  I am running 8.1.7 on windows.  I am unable to determine why I can't get the thesaursus feature to work.

Any insight would be helpful.

Thanks for the great site.
____________________________________________
SQL> create table prsn ( first_name varchar2(50));

Table created.

SQL> insert into prsn values ( 'Mike');

1 row created.

SQL> insert into prsn values ( 'Michael');

1 row created.

SQL>  insert into prsn values ( 'Bob');

1 row created.

SQL> CREATE INDEX idx_docs ON PRSN(first_name) INDEXTYPE IS CTXSYS.CONTEXT ;

Index created.

SQL>  exec CTX_THES.CREATE_THESAURUS('names',false);

PL/SQL procedure successfully completed.

SQL> exec CTX_THES.CREATE_RELATION('names','mike','SYN','michael');

PL/SQL procedure successfully completed.

SQL> SELECT SCORE(1), first_name  FROM PRSN  where CONTAINS(first_name,'syn(mike)', 1) > 0;
SELECT SCORE(1), first_name  FROM PRSN  where CONTAINS(first_name,'syn(mike)', 1) > 0
*
ERROR at line 1:
ORA-29902: error in executing ODCIIndexStart() routine
ORA-20000: interMedia Text error:
DRG-50942: errors:
ORA-01455: converting column overflows integer datatype


SQL> SELECT SCORE(1), first_name  FROM PRSN  where CONTAINS(first_name,'fuzzy(mike,       70, 6, wei
ght)', 1) > 0;

  SCORE(1) FIRST_NAME
---------- --------------------------------------------------
         4 Mike

SQL> 
SQL>

Tom Kyte
June 10, 2004 - 8:06 pm UTC

contact support, reference bug <Bug:2305507>

DRG-50901: text query parser syntax error on line 1, column 1

Dee, June 25, 2004 - 10:23 pm UTC

Of some use, I will go back and review my code, however there has to be other reasons why this error occurs. I am not sure this solves my original problem... Can I ask a related question here, or do I have to post a new question on DRG-50901: text query parser syntax error on line 1, column 1?

Tom Kyte
June 26, 2004 - 6:45 pm UTC

lets see your text query, it would be "malformed"

DRG-50901

Rich, March 04, 2005 - 9:56 am UTC

Hi Tom,

I created a context index on EMP.JOB as follows:

create index myindex on emp(job)
indextype is ctxsys.context;

Then created a procedure like:

CREATE OR REPLACE PROCEDURE get_emps IS
CURSOR c_emps IS
SELECT deptno, ename, empno, job, sal
FROM emp
ORDER BY deptno, ename;
v_count NUMBER;
BEGIN
FOR v_rec IN c_emps LOOP
select Count(*) cnt INTO v_count
from emp
WHERE deptno = v_rec.deptno
AND empno <> v_rec.empno
AND contains (job, ''''||'fuzzy(('||v_rec.job||'),,,w)'||'''', 1) > 0;
Dbms_Output.put_line(v_rec.deptno||' '||v_rec.ename||' '||v_count);
END LOOP;
END;

Procedure created successfully.

Then tried to execute the procedure but got the
following error:

ORA-29902: error in executing ODCIIndexStart() routine
ORA-20000: Oracle Text error:
DRG-50901: text query parser syntax error on line 1, column 18
ORA-06512: at "SCOTT.GET_EMPS", line 9
ORA-06512: at line 2

Would appreciate your help. Thanks.

Tom Kyte
March 04, 2005 - 10:55 am UTC 

well, you are passing in a string


'fuzzy((MANAGER),,,w)'

when you meant perhaps to pass in

fuzzy((MANAGER),,,w)

(no quotes)


ops$tkyte@ORA9IR2> create table emp as select * from scott.emp;
 
Table created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create index myindex on emp(job) indextype is ctxsys.context;
 
Index created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> CREATE OR REPLACE PROCEDURE get_emps
  2  IS
  3    CURSOR c_emps IS
  4      SELECT deptno, ename, empno, job, sal
  5        FROM emp
  6        ORDER BY deptno, ename;
  7    v_count NUMBER;
  8  BEGIN
  9    FOR v_rec IN c_emps
 10    LOOP
 11      select Count(*) cnt INTO v_count
 12        from emp
 13        WHERE deptno = v_rec.deptno
 14        AND empno <> v_rec.empno
 15        AND contains (job, 'fuzzy(('||v_rec.job||'),,,w)', 1) > 0;
 16      Dbms_Output.put_line(v_rec.deptno||' '||v_rec.ename||' '||v_count);
 17    END LOOP;
 18  END;
 19  /
 
Procedure created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> exec get_emps
10 CLARK 0
10 KING 0
10 MILLER 0
20 ADAMS 1
20 FORD 1
20 JONES 0
20 SCOTT 1
20 Smith 1
30 Allen 3
30 BLAKE 0
30 JAMES 0
30 MARTIN 3
30 TURNER 3
30 WARD 3
 
PL/SQL procedure successfully completed.

<b>although I'd rather see you use a scalar subquery --as the database will cache scalar subquery results nicely -- that query will not be run for every record necessarily, just when  deptno/job changes....</b>


ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select deptno, ename, empno, job, sal,
  2        (select count(*)-1
  3           from emp  e2
  4          where deptno = e1.deptno
  5            and contains( job, 'fuzzy(('||e1.JOB||'),,,w)' ) > 0 ) cnt
  6    from emp e1
  7   order by deptno, ename;
 
    DEPTNO ENAME           EMPNO JOB              SAL        CNT
---------- ---------- ---------- --------- ---------- ----------
        10 CLARK            7782 MANAGER         2450          0
        10 KING             7839 PRESIDENT       5000          0
        10 MILLER           7934 CLERK           1300          0
        20 ADAMS            7876 CLERK           1100          1
        20 FORD             7902 ANALYST         3000          1
        20 JONES            7566 MANAGER         2975          0
        20 SCOTT            7788 ANALYST         3000          1
        20 Smith            7369 CLERK            800          1
        30 Allen            7499 SALESMAN        1600          3
        30 BLAKE            7698 MANAGER         2850          0
        30 JAMES            7900 CLERK            950          0
        30 MARTIN           7654 SALESMAN        1250          3
        30 TURNER           7844 SALESMAN        1500          3
        30 WARD             7521 SALESMAN        1250          3
 
14 rows selected.

Wonderful!

Rich, March 04, 2005 - 11:03 am UTC

Works great!

And the scalar subquery alternative is something I
will definitely consider.

Thanks a lot!

Drg-50901, Strange Error point

Jitender, August 27, 2007 - 7:12 am UTC

hi,
I have gone through with this discussion. In every discussion state that DRG-50901 has specific reason to occur. But we are getting this strangely from that where it should not be. Also, at one place it was working and at other part it is not. Means if run query through Sql-plus along with values it is working fine but when I ran it procedure using parameter then it gives error. Also, within same procedure we use same query to find out maximum record and it is working fine but data query doesn¿t return data but raising error.
Our query look like as follows:
SELECT col1, col2
FROM
WHERE
(strSearchStr IS NULL
Or length(trim(strSearchStr)) > 0
Or contains(col, strSearchStr) > 0)
OR
(strSearchStr IS NULL
Or length(trim(strSearchStr)) > 0
Or contains(col, strSearchStr) > 0)
----
Initial observation we found that if put contains under decode then we are not getting this error.
e.g DECODE(COALESCE(strSearchStr,¿*¿),¿*¿,1, contains(col, strSearchStr)) > 0
I know it is silly solution to this error, but it was working.
Later on we also came to know cost of query was very high. Basically this is pagination query. So if we remove First_rows hints then it was working fine. But not very sure how long this kind solution work.
As oracle shows it an error of Oracle Text and solution come other part.
Our query was absolutely right and working fine other environment.
Error environment has been upgraded from oracle.9.2.0.5 to 9.2.0.7.
In that environment following object was invalid:
sys.odcicolinfodump,
sys.odcienvdump,
sys.odciindexalteroptiondump,
sys.odciindexcallpropertydump,
sys.odciindexinfodump,
sys.odciindexinfoflagsdump,
sys.odcipredinfodump,
sys.odciqueryinfodump,
sys.odcistatsoptionsdump,
sys.pstub,
sys.pstubt,
sys.subptxt

CATSEARCH
CTXCAT
CTXRULE
CTXXPATH
CTX_CATSEARCH
CTX_MATCHES
CTX_XPCONTAINS
MATCHES
PARALLELPOPUINDEX
XPCONTAINS
Can you tell me what are other possibilities of this error. Other part of our application working fine even we are using oracle text utility also in it.

Tom Kyte
September 04, 2007 - 11:48 am UTC

if you provided a full example, I would comment - but without one, I cannot.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.