Running Oracle Text PL/SQL APIs in a Sharded Database

Oracle Globally Distributed Database enables you to horizontally partition data across multiple, independent Oracle databases. Each physical database in such a configuration is called a shard. You can use the sharding-specific PL/SQL procedure SYS.EXEC_SHARD_PLSQL to propagate certain Oracle Text CTXSYS procedures across all shards.

You use the SYS.EXEC_SHARD_PLSQL wrapper to run a target procedure on all shards in the same way as DDL statements are run in a sharded database configuration. These procedures are propagated to all shards, tracked by the catalog, and run whenever a new shard is added to a configuration.

If you run a target procedure without using the SYS.EXEC_SHARD_PLSQL wrapper, then the procedure runs only on the shard catalog and is not propagated to all shards.

The SYS.EXEC_SHARD_PLSQL procedure takes a single CLOB argument, which is a character string specifying a fully qualified procedure name and its arguments.

For example, to run CTXSYS.CTX_DDL.CREATE_PREFERENCE on all shards:

exec sys.exec_shard_plsql('ctxsys.ctx_ddl.create_preference(
    preference_name => "mylexer",
    object_name => "BASIC_LEXER")');

Note the following:

• Certain PL/SQL procedures need a wrapper and others do not. For a complete list of allowed PL/SQL procedures, see Supported APIs in a Sharded Database.

• You must use double quotation marks (") inside a target procedure call specification, because the call specification itself is a string parameter to SYS.EXEC_SHARD_PLSQL.

• Running the SYS.EXEC_SHARD_PLSQL procedure without specifying a fully-qualified name (for example, CTXSYS.CTX_DDL.CREATE_PREFERENCE) results in an error.

Related Topics

• Oracle Globally Distributed Database Guide