Running PL/SQL Procedures in a Distributed Database

In the same way that DDL statements can be run on all shards in an Oracle Globally Distributed Database configuration, so too can certain Oracle-provided PL/SQL procedures.

These specific procedure calls behave as if they were sharded DDL statements, in that they are propagated to all shards, tracked by the catalog, and run whenever a new shard is added to a configuration.

All of the following procedures can act as if they were a sharded DDL statement.

  • Oracle Text CTXSYS procedures listed in Oracle Text Application Developer's Guide, Supported APIs in a Sharded Database

  • Any procedure in the DBMS_FGA package

  • Any procedure in the DBMS_RLS package

  • Any procedure in the DBMS_REDACT package

  • The following procedures from the DBMS_STATS package:

    • GATHER_INDEX_STATS
    • GATHER_TABLE_STATS
    • GATHER_SCHEMA_STATS
    • GATHER_DATABASE_STATS
    • GATHER_SYSTEM_STATS

  • The following procedures from the DBMS_GOLDENGATE_ADM package:

    • ADD_AUTO_CDR
    • ADD_AUTO_CDR_COLUMN_GROUP
    • ADD_AUTO_CDR_DELTA_RES
    • ALTER_AUTO_CDR
    • ALTER_AUTO_CDR_COLUMN_GROUP
    • PURGE_TOMBSTONES
    • REMOVE_AUTO_CDR
    • REMOVE_AUTO_CDR_COLUMN_GROUP
    • REMOVE_AUTO_CDR_DELTA_RES

To run one of the procedures in the same way as sharded DDL statements, do the following steps.

  1. Connect to the shard catalog database using SQL*Plus as a database user with the gsm_pooladmin_role.

  2. Enable sharding DDL using ALTER SESSION ENABLE SHARD DDL.

  3. Run the target procedure using a sharding-specific PL/SQL procedure named SYS.EXEC_SHARD_PLSQL.

    This procedure takes a single CLOB argument, which is a character string specifying a fully qualified procedure name and its arguments. Note that running the target procedure without using EXEC_SHARD_PLSQL causes the procedure to only be run on the shard catalog, and it is not propagated to all of the shards. Running the procedure without specifying the fully qualified name (for example, SYS.DBMS_RLS.ADD_POLICY) will result in an error.

For example, to run DBMS_RLS.ADD_POLICY on all shards, do the following from SQL*Plus after enabling SHARD DLL. 

exec sys.exec_shard_plsql('sys.dbms_rls.add_policy(object_schema               =>
          ''testuser1'',                                    
          object_name     => ''DEPARTMENTS'',               
          policy_name     => ''dept_vpd_pol'',              
          function_schema => ''testuser1'',              
          policy_function => ''authorized_emps'',           
          statement_types => ''INSERT, UPDATE, DELETE, SELECT, INDEX'',
          update_check    => TRUE)' 
          ) ;

Take careful note of the need for double single-quotes inside the target procedure call specification, because the call specification itself is a string parameter to EXEC_SHARD_PLSQL.

If the target procedure runs correctly on the shard catalog database, it is queued for processing on all of the currently deployed shards. Any error in running the target procedure on the shard catalog is returned to the SQL*Plus session. Errors while running on the shards can be tracked in the same way they are for DDLs.