Oracle7 Spatial Data Option User's Guide and Reference

Sample SQL Scripts

A.1 altpart.sql Script
A.2 crlayer.sql Script
A.3 droppart.sql Script
A.4 sdogrant.sql Script

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
crlayer.sql
droppart.sql
sdogrant.sql

A.1 altpart.sql Script

This SQL script file shows how to use dynamic SQL in a PL/SQL procedure to modify all partitions of a Spatial Data Option partitioned table.

The Spatial Data Option data dictionary view used in this SQL script requires that a registered Spatial Data Option 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 crlayer.sql Script

This file is a template for an SQL script used to create all the tables for a layer and populate the meta-data in the SDODIM and SDOLAYER tables.

A.3 droppart.sql Script

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

The Spatial Data Option data dictionary view used in this SQL script requires that a registered Spatial Data Option 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.4 sdogrant.sql Script

The file sdogrant.sql 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 callable only by the user who compiled it.

Copyright © 1996 Oracle Corporation.
All Rights Reserved.