Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Rajeshwaran.

Asked: April 03, 2017 - 2:12 am UTC

Last updated: April 21, 2017 - 1:23 am UTC

Version: 12.2.0.1

Viewed 1000+ times

You Asked

Team,

Just started learning about Oracle Text from documentation.

http://docs.oracle.com/database/122/CCAPP/getting-started-with-oracle-text.htm#CCAPP9645

drop table text_demo purge;
create table text_demo (x int,docs varchar2(100));
INSERT INTO text_demo VALUES(1, '<HTML>California is a state in the US.</HTML>');
INSERT INTO text_demo VALUES(2, '<HTML>Paris is a city in France.</HTML>');
INSERT INTO text_demo VALUES(3, '<HTML>France is in Europe.</HTML>');
commit;

create index text_demo_idx on text_demo(docs)
 indextype is ctxsys.context 
 parameters( 'filter ctxsys.null_filter
  section group ctxsys.html_section_group' );

when tried to run the example from the above link got this error.

demo@ORA12C> declare
  2     x clob;
  3     y varchar2(40);
  4     l_len int := 40;
  5  begin
  6     ctx_doc.markup('text_demo_idx','3','France',x);
  7     dbms_lob.read(x,l_len,1,y);
  8     dbms_output.put_line( 'y = '||y);
  9     dbms_lob.freetemporary(x);
 10  end;
 11  /
declare
*
ERROR at line 1:
ORA-20000: Oracle Text error:
DRG-11445: rowid value is invalid: 3
ORA-06512: at "CTXSYS.DRUE", line 171
ORA-06512: at "CTXSYS.CTX_DOC", line 2259
ORA-06512: at line 6


demo@ORA12C>


with LiveSQL Test Case:

and Connor said...

Primary key :-)

SQL> drop table text_demo purge;

Table dropped.

SQL> create table text_demo (x int primary key,docs varchar2(100));
                                  ^^^^^^^^^^^^^

Table created.

SQL> INSERT INTO text_demo VALUES(1, '<HTML>California is a state in the US.</HTML>');

1 row created.

SQL> INSERT INTO text_demo VALUES(2, '<HTML>Paris is a city in France.</HTML>');

1 row created.

SQL> INSERT INTO text_demo VALUES(3, '<HTML>France is in Europe.</HTML>');

1 row created.

SQL> commit;

Commit complete.

SQL>
SQL> create index text_demo_idx on text_demo(docs)
  2   indextype is ctxsys.context
  3   parameters( 'filter ctxsys.null_filter section group ctxsys.html_section_group' );

Index created.

SQL>
SQL> declare
  2         x clob;
  3         y varchar2(80);
  4         l_len int := 40;
  5      begin
  6         ctx_doc.markup('text_demo_idx','3','France',x);
  7         dbms_lob.read(x,l_len,1,y);
  8         dbms_output.put_line( 'y = '||y);
  9         dbms_lob.freetemporary(x);
 10     end;
 11     /
y = <HTML><<<France>>> is in Europe.</HTML>

PL/SQL procedure successfully completed.



Rating

  (2 ratings)

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

Comments

Primary key

Rajeshwaran, April 03, 2017 - 6:40 am UTC

Could you help us to understand the need for the primary key here?
Connor McDonald
April 04, 2017 - 1:00 am UTC

Because that's we are using to locate your row. The key is not mandatory, but if you dont have it, you have to give us the rowid, eg

SQL> create table text_demo (x int,docs varchar2(100));

Table created.

SQL> INSERT INTO text_demo VALUES(1, '<HTML>California is a state in the US.</HTML>');

1 row created.

SQL> INSERT INTO text_demo VALUES(2, '<HTML>Paris is a city in France.</HTML>');

1 row created.

SQL> INSERT INTO text_demo VALUES(3, '<HTML>France is in Europe.</HTML>');

1 row created.

SQL> commit;

Commit complete.

SQL> select x, rowid from text_demo;

         X ROWID
---------- ------------------
         1 AAAUvEAAHAAAmhGAAA
         2 AAAUvEAAHAAAmhGAAB
         3 AAAUvEAAHAAAmhGAAC

3 rows selected.

SQL> create index text_demo_idx on text_demo(docs)
  2   indextype is ctxsys.context
  3   parameters( 'filter ctxsys.null_filter
  4    section group ctxsys.html_section_group' );

Index created.

SQL> declare
  2         x clob;
  3         y varchar2(40);
  4         l_len int := 40;
  5      begin
  6         ctx_doc.markup('text_demo_idx','AAAUvEAAHAAAmhGAAC','France',x);
  7         dbms_lob.read(x,l_len,1,y);
  8         dbms_output.put_line( 'y = '||y);
  9         dbms_lob.freetemporary(x);
 10     end;
 11     /

PL/SQL procedure successfully completed.



but obviously, a primary key is a more "accessible" piece of information for applications to have when they want to do a markup.

new to TEXT

A reader, April 20, 2017 - 1:57 pm UTC

I am new to Oracle TEXT, so which guide should i start first?

TEXT application developer guide - http://docs.oracle.com/database/122/CCAPP/toc.htm

or

TEXT reference -
http://docs.oracle.com/database/122/CCREF/toc.htm

kindly advice

More to Explore

CLOB/BLOB/etc

Complete documentation on Securefiles and Large Objects here