A
Querying Stored Annotations

After the media data and the annotation are uploaded into an Oracle database, you can use Oracle9i Text to perform a query on the annotation (which is saved in the database in XML form).

The following PL/SQL code excerpt is an example of how to build an Oracle9i Text index on the VideoStorage table. This code excerpt generates an Oracle9i Text index on the comments field of the vsrc column of the VideoStorage table, with the list preference, as well as the XML tags, defined in the section group:

-- create a preference
execute ctx_ddl.create_preference('ANNOT_WORDLIST', 'BASIC_WORDLIST');
execute ctx_ddl.set_attribute('ANNOT_WORDLIST', 'stemmer', 'ENGLISH');
execute ctx_ddl.set_attribute('ANNOT_WORDLIST', 'fuzzy_match', 'ENGLISH');
...

-- section group
execute ctx_ddl.create_section_group('MOVIEANN_TAGS',
                                     'xml_section_group');
execute ctx_ddl.add_zone_section('MOVIEANN_TAGS', 'MOVIECASTTAG',
                                 'MOVIE_CAST');
execute ctx_ddl.add_zone_section('MOVIEANN_TAGS',
                                 'MEDIACOPYRIGHTTAG',
                                 'MEDIA_COPYRIGHT');
execute ctx_ddl.add_zone_section('MOVIEANN_TAGS', 
                                 'MEDIASOURCEFILEFORMATTAG',
                                 'MEDIA_SOURCE_FILE_FORMAT');
...
CREATE INDEX videoIdx ON VideoStorage(vsrc.comments) INDEXTYPE IS 
     CTXSYS.CONTEXT PARAMETERS('stoplist CTXSYS.EMPTY_STOPLIST wordlist 
     ANN_WORDLIST filter CTXSYS.NULL_FILTER section group MOVIEANN_TAGS');

See Section 2.2.2 for more information on creating the VideoStorage table.

The following PL/SQL code excerpt is an example of how to query the VideoStorage table:

-- Perform a query
select id, score(99)
from VideoStorage V
where
     CONTAINS(V.vsrc.comments, '(John Doe) WITHIN MOVIECASTTAG',
     99) > 0;

The preceding query returns the clip identification number and the relevancy score (generated by Oracle9i Text) of the video clips that contain John Doe in the MOVIE_CAST attribute of the associated annotation.

A copy of the preceding PL/SQL statements is available in:

<ORACLE_HOME>\ord\Annotator\demo\examples\SampleCode.sql

For more information, see the Oracle9i Text documentation.