Development: Omnidex SQL



The $RETRIEVE_FILE functions opens an external textual file and returns the contents. This function is most commonly used in Omnidex Text applications to index and query a catalog of external textual files. This function can also be used to simply retrieve the contents of any textual file using an Omnidex SQL statement.

This function returns a buffer using the datatype and length specified in the parameters. If no parameters are specified, then the default datatype and length are returned.


$RETRIEVE_FILE(filename [,'datatype' [,length [,'options']]])


A string literal, a column, or an expression containing the filename to retrieve.


The datatype to be used for retrieving the file’s content. Typically a CLOB or STRING is used to retrieve ASCII data such as text.

Datatypes are specified in textual form, and may be used with or without lengths. If no lengths are specified, then they must be included in the length parameter. If no datatype is specified, then CLOB is presumed.


The length to be used for retrieving the file’s content. Lengths may also be specified in the datatype parameter using the standard Omnidex syntax. If no length is provided in either place, the length defaults to 64KB.


The options to be applied to retrieving this file.


Use the STOPWORDS list identified by this option.


Parse the keywords from the text and discard all whitespace and punctuation.


Example 1: Retrieving from a named file

This example retrieves the contents of a single, named file. Note that this example retrieves from the table $OMNIDEX. $OMNIDEX is a virtual table that is always available that contains a single virtual row. It is available for purposes such as these.

select        $retrieve_file('star.sql', 'c string(4KB)')
  from        $OMNIDEX;

Example 1: Retrieving from a named file

This example relies on the LIBRARY table containing a FILENAME column. This FILENAME column contains a different filename for each row. This statement will retrieve the contents of each file as each row is retrieved.

select        $retrieve_file(FILENAME, 'c string(4KB)')
  from        LIBRARY;
Back to top
dev/sql/functions/retrieve_file.txt · Last modified: 2016/06/28 22:38 (external edit)