PL/SQL Operator

Oracle8i interMedia Text 8.1 no longer supports the execute operator which allows you to call a PL/SQL function in a query.

As a result of interMedia Text's integration with Oracle8, you can use standard SQL, which allows you to call functions in a SELECT statement as long as the function satisfies the requirements for being named in a SQL statement.

Pre-8.1 Method

Calling a PL/SQL function within a query is useful for converting words to alternate forms. For example, assuming the function french returns the French equivalent of English words, you as ctxuser can search on the French word for cat by issuing:

'@ctxuser.french(cat)'

8.1 Method

You can call user functions directly in the CONTAINS clause as long as the function satisfies the requirements for being named in a SQL statement. The caller must also have EXECUTE privilege on the function.

For example, assuming the function french returns the French equivalent of English words, you can search on the French word for cat by writing:

SELECT SCORE(1), title from news 
   WHERE CONTAINS(text, french('cat'), 1) > 0
   ORDER BY SCORE(1);

You can improve performance by passing the function to CONTAINS as a bind variable as follows:

variable qry varchar2(80);
exec :qry := french('cat');

SELECT SCORE(1), title from news 
   WHERE CONTAINS(text, :qry, 1) > 0
   ORDER BY SCORE(1);

See Also: For more information about creating user functions and calling user functions from SQL, see Oracle8i SQL Reference.