A
Sample SQL Scripts

The following sample SQL script files are provided to show how to use dynamic SQL in a PL/SQL block to create layer tables for spatially indexed data or to administer and manipulate all the partitions of a partitioned spatial table. The scripts are available after installation in the ORACLE_HOME/md/admin directory.

The following SQL scripts are described in this appendix:

• altpart.sql
• cr_spatial_index.sql
• crlayer.sql
• droppart.sql
• sdogrant.sql

A.1 altpart.sql Script

The altpart.sql script file shows how to use dynamic SQL in a PL/SQL procedure to modify all partitions of a Spatial Cartridge partitioned table.

The Spatial Cartridge data dictionary view used in this SQL script requires that a registered Spatial Cartridge partitioned table is specified. If the table is not registered, you can use the USER_TABLES view to select all the partition tables from the user's schema. To use the USER_TABLES view, use the following syntax:

SQL> SELECT TABLENAME FROM user_tables WHERE TABLENAME LIKE 

2> '%tablename_P%';

A.2 cr_spatial_index.sql Script

The cr_spatial_index.sql script file shows an example of updating the spatial index for a geometry, and executing a commit after every 50 vertices have been entered.

The SDO_ADMIN.POPULATE_INDEX() and SDO_ADMIN.POPULATE_INDEX_FIXED() procedures operate as a single transaction. To reduce the amount of rollback required to execute these procedures, you can write a routine similar to that in cr_spatial_index.sql. This script loops and calls SDO_ADMIN.UPDATE_INDEX for a geometry, committing after every 50 entries.

A.3 crlayer.sql Script

The crlayer.sql script file is a template used to create all the tables for a layer and populate the metadata in the SDODIM and SDOLAYER tables.

A.4 droppart.sql Script

The droppart.sql script file shows how to use dynamic SQL in a PL/SQL procedure to drop all partitions of a Spatial Cartridge partitioned table. After running this procedure, you must run the SDO_ADMIN.DROP_PARTITION_INFO procedure.

The Spatial Cartridge data dictionary view used in this SQL script requires that a registered Spatial Cartridge partitioned table is specified. If the table is not registered, you can use the USER_TABLES view to select all the partition tables from the user's schema. To use the USER_TABLES view, use the following syntax:

SQL> SELECT TABLENAME FROM user_tables WHERE TABLENAME LIKE 

2> '%tablename_P%';

A.5 sdogrant.sql Script

The sdogrant.sql script file contains an administrative procedure, propagate_grants(), which is used after calling the sdo_admin.partition() or sdo_admin.repartition() procedures.

This procedure must first be compiled by running the sdogrant.sql file. The propagate_grants() procedure is only callable by the user who compiled it.

---