A
Querying Stored Annotations

After the media data and the annotation are uploaded into an Oracle database, you can use Oracle 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 Oracle Text index on a database table, VideoStorage. This code excerpt generates an Oracle 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');

The following example shows the SQL statement that creates the VideoStorage table:

CREATE TABLE VideoStorage OF VideoType (ID PRIMARY KEY)

             LOB(vsrc.source.localdata) STORE AS (NOCACHE NOLOGGING);

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 Oracle Text) of the video clips that contain John Doe in the MOVIE_CAST attribute of the associated annotation.

The preceding PL/SQL statements are available in the following file:

• On UNIX:

  $ORACLE_HOME/ord/Annotator/demo/examples/SampleCode.sql
  
  

• On Windows:

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

For more information, see the Oracle Text documentation, especially Oracle Text Application Developer's Guide.