5 SDO_GEOR_ADMIN Package Reference

The MDSYS.SDO_GEOR_ADMIN package contains subprograms (functions and procedures) for administrative operations related to GeoRaster. This chapter presents reference information, with one or more examples, for each subprogram.

SDO_GEOR_ADMIN.checkSysdataEntries

Format

SDO_GEOR_ADMIN.checkSysdataEntries() RETURN SDO_STRING2_ARRAY;

Description

Checks the USER_SDO_GEOR_SYSDATA view for any invalid entries.

Parameters

None.

Usage Notes

This function returns an array of comma-delimited list of GeoRaster system data entries that are invalid. It checks for errors such as the following:

The RDT name is not unique.
The GeoRaster table does not exist.
The GeoRaster column does not exist.
The GeoRaster objects does not exist.
The GeoRaster object is non-empty or nonblank, but the RDT does not exist.
Duplicate GeoRaster objects exist (that is, one or more non-unique combinations of RDT and raster ID).

If you call this function while connected as the MDSYS user, the entries in the ALL_SDO_GEOR_SYSDATA view instead of the USER_SDO_GEOR_METADATA view are checked.

The USER_SDO_GEOR_DATA and ALL_SDO_GEOR_SYSDATA views are described in Section 2.4.

Examples

The following example checks the USER_SDO_GEOR_SYSDATA view for invalid entries.

SELECT * FROM THE (SELECT SDO_GEOR_ADMIN.checkSysdataEntries FROM DUAL);
 
COLUMN_VALUE
--------------------------------------------------------------------------------
The RDT name "RDT1" is not unique
The GeoRaster object GEOR_TEST1.TABLE1.GEOR: RDT=RDT2 RID=3 is associated with a
 non-existing RDT table!
The specification of GeoRaster column GEOR_TEST1.TABLE1.c1  is not correct.
The GeoRaster object GEOR_TEST1.TABLE1.geor: RDT=dt3 RID=2 doesn't exist!
The GeoRaster table GEOR_TEST1.t1  doesn't exist!

SDO_GEOR_ADMIN.isRDTNameUnique

Format

SDO_GEOR_ADMIN.isRDTNameUniquer(

rdtName VARCHAR2)

RETURN VARCHAR2;

Description

Checks if the specified raster data table (RDT) name is unique among RDT names in the database.

Parameters

rdtName: Name to be checked for uniqueness.

Usage Notes

You can use this function to check, before you create an RDT, if the RDT name that you plan to use is unique.

This function returns the string TRUE if the name is unique and the string FALSE if the name is not unique.

Examples

The following example checks if the name MY_RDT is unique.

SELECT SDO_GEOR_ADMIN.IsRDTNameUnique('MY_RDT')  FROM DUAL;
 
SDO_GEOR_ADMIN.ISRDTNAMEUNIQUE('MY_RDT')                                        
--------------------------------------------------------------------------------
TRUE

SDO_GEOR_ADMIN.isUpgradeNeeded

Format

SDO_GEOR_ADMIN.isUpgradeNeeded() RETURN SDO_STRING2_ARRAY;

Description

Checks the GeoRaster system data entries and GeoRaster data for the current schema.

Parameters

None.

Usage Notes

This function returns an array of comma-delimited list of GeoRaster system data entries and GeoRaster columns and objects that are invalid. It can report errors such as the following:

System data entry error, the RDT name is not unique.
System data entry error, the RDT/RID pair is not unique.
System data entry error, the GeoRaster table does not exist.
System data entry error, the GeoRaster column does not exist.
System data entry error, the GeoRaster object does not exist.
The GeoRaster object is non-empty or nonblank, but the RDT does not exist.
Duplicate GeoRaster objects exist (that is, one or more non-unique combinations of RDT and raster ID).
There is a non-registered pair of (GeoRaster column, GeoRaster object).

If you call this function while connected as the MDSYS user, the GeoRaster system data entries and GeoRaster data for the entire database are checked.

Examples

The following example checks the GeoRaster system data entries and GeoRaster data. It assumes that you are connected as the MDSYS user.

SELECT * FROM THE (SELECT SDO_GEOR_ADMIN.isUpgradeNeeded FROM DUAL);
 
COLUMN_VALUE
--------------------------------------------------------------------------------
The following GeoRaster columns aren't registered:
     SCHEMA:GEOR_TEST TABLE:TABLE1 COLUMN:GEOR
The following GeoRaster objects aren't registered:
     SCHEMA:GEOR_TEST TABLE:TABLE1 COLUMN:GEOR RDT:RDT RID:3
     SCHEMA:GEOR_TEST TABLE:TABLE1 COLUMN:GEOR RDT:RDT RID:4

SDO_GEOR_ADMIN.listGeoRasterColumns

Format

SDO_GEOR_ADMIN.listGeoRasterColumns() RETURN SDO_STRING2_ARRAYSET;

Description

Lists the GeoRaster columns defined in the current schema.

Parameters

None.

Usage Notes

This function returns an array of comma-delimited list of GeoRaster columns with their registration status. The list contains the following information:

Schema name (only if you are connected as the MDSYS user)
GeoRaster table name
GeoRaster column name
Status: registered (a DML trigger is created for the GeoRaster column) or unregistered (no DML trigger is created for the GeoRaster column)

If you call this function while connected as the MDSYS user, all GeoRaster columns defined in the database are listed.

Examples

The following example lists the GeoRaster columns defined in the current schema.

SELECT * FROM THE (SELECT SDO_GEOR_ADMIN.listGeoRasterColumns FROM DUAL);
 
COLUMN_VALUE
---------------------------------------------------------------------------------
SDO_STRING2_ARRAY('TEST_TABLE1', 'GEOR', 'registered')
SDO_STRING2_ARRAY('TEST_TABLE2', 'GEOR', 'registered')

SDO_GEOR_ADMIN.listGeoRasterObjects

Format

SDO_GEOR_ADMIN.listGeoRasterObjects() RETURN SDO_STRING2_ARRAYSET;

Description

Lists the GeoRaster objects defined in the current schema.

Parameters

None.

Usage Notes

This function returns an array of comma-delimited list of GeoRaster objects with their registration status. The list contains the following information:

Schema name (only if you are connected as the MDSYS user)
GeoRaster table name
GeoRaster column name
RDT name
Raster ID
Status: registered (the GeoRaster object has been registered is the SYSDATA table) or unregistered (the GeoRaster object has not been registered is the SYSDATA table)

If you call this function while connected as the MDSYS user, all GeoRaster objects defined in the database are listed.

Examples

The following example lists the GeoRaster objects defined in the current schema.

SELECT * FROM THE (SELECT SDO_GEOR_ADMIN.listGeoRasterObjects FROM DUAL);
 
COLUMN_VALUE
---------------------------------------------------------------------------------
SDO_STRING2_ARRAY('TEST_TABLE1', 'GEOR', 'RDT_REGULAR_01', '1', 'registered')
SDO_STRING2_ARRAY('TEST_TABLE2', 'GEOR', 'RDT_REGULAR_01', '2', 'registered')

SDO_GEOR_ADMIN.listGeoRasterTables

Format

SDO_GEOR_ADMIN.listGeoRasterTables() RETURN SDO_STRING2_ARRAYSET;

Description

Lists the GeoRaster tables defined in the current schema.

Parameters

None.

Usage Notes

This function returns an array of comma-delimited list of GeoRaster tables. The list contains the following information:

Schema name (only if you are connected as the MDSYS user)
GeoRaster table name

If you call this function while connected as the MDSYS user, all GeoRaster tables defined in the database are listed.

Examples

The following example lists the GeoRaster tables defined in the database. It assumes that you are connected as the MDSYS user.

SELECT * FROM THE (SELECT SDO_GEOR_ADMIN.listGeoRasterTables FROM DUAL);
 
COLUMN_VALUE
---------------------------------------------------------------------------------
SDO_STRING2_ARRAY('GEOR_TEST', 'TEST_TABLE1')
SDO_STRING2_ARRAY('GEOR_TEST', 'TEST_TABLE2')

SDO_GEOR_ADMIN.listDanglingRasterData

Format

SDO_GEOR_ADMIN.listDanglingRasterData() RETURN SDO_STRING2_ARRAYSET;

Description

Checks the GeoRaster system data entries and GeoRaster data, and lists all dangling raster data.

Parameters

None.

Usage Notes

Raster data table (RDT) rows might exist for nonexistent GeoRaster objects or GeoRaster objects that are not referred to in the SYSDATA table. The raster blocks associated with such rows are referred to dangling blocks. The dangling raster blocks cause wasted disk space in the RDT although otherwise they do not present a problem as long as the necessary primary key is defined on the RDT. To find these dangling blocks in the current schema or in all schemas, call the SDO_GEOR_ADMIN.listDanglingRasterData function.

Before you call this function, you should call SDO_GEOR_ADMIN.registerGeoRasterObjects to register all existing GeoRaster objects.

To remove the dangling raster block data from an RDT, delete the rows associated with the problems discovered by the SDO_GEOR_ADMIN.listDanglingRasterData function.

This function returns an array of comma-delimited list of dangling raster data. The list contains the following information:

Schema name (only if you are connected as the MDSYS user)
RDT name
Raster ID

If you call this function while connected as the MDSYS user, all dangling raster data in the database is listed.

Examples

The following example lists all dangling raster data in the current schema.

SELECT * FROM THE (SELECT SDO_GEOR_ADMIN.listDanglingRasterData FROM DUAL);
 
COLUMN_VALUE
--------------------------------------------------------------------------------
SDO_STRING2_ARRAY('RDT11', '3')

SDO_GEOR_ADMIN.listRDT

Format

SDO_GEOR_ADMIN.listRDT() RETURN SDO_STRING2_ARRAYSET;

Description

Lists the raster data tables (RDTs) defined in the current schema.

Parameters

None.

Usage Notes

This function returns an array of comma-delimited list of RDTs. The list contains the following information:

Schema name (only if you are connected as the MDSYS user)
RDT name

If you call this function while connected as the MDSYS user, all RDTs defined in the database are listed.

Examples

The following example lists the RDTs defined in the current schema.

SELECT * FROM THE (SELECT SDO_GEOR_ADMIN.listRDT FROM DUAL);
 
COLUMN_VALUE
---------------------------------------------------------------------------------
SDO_STRING2_ARRAY('RDT_REGULAR_01')
SDO_STRING2_ARRAY('RDT_REGULAR_02')

SDO_GEOR_ADMIN.listRegisteredRDT

Format

SDO_GEOR_ADMIN.listRegisteredRDT() RETURN SDO_STRING2_ARRAYSET;

Description

Lists the registered raster data tables (RDTs) defined in the current schema. An RDT is registered if at least one entry in the SYSDATA table refers to it.

Parameters

None.

Usage Notes

This function returns an array of comma-delimited list of RDTs. The list contains the following information:

Schema name (only if you are connected as the MDSYS user)
RDT name

If you call this function while connected as the MDSYS user, all registered RDTs defined in the database are listed.

Examples

The following example lists the registered RDTs defined in the current schema.

SELECT * FROM THE (SELECT SDO_GEOR_ADMIN.listRegisteredRDT FROM DUAL);
 
COLUMN_VALUE
---------------------------------------------------------------------------------
SDO_STRING2_ARRAY('RDT1_REGULAR_01')

SDO_GEOR_ADMIN.listUnregisteredRDT

Format

SDO_GEOR_ADMIN.listUnregusteredRDT() RETURN SDO_STRING2_ARRAYSET;

Description

Lists the unregistered raster data tables (RDTs) defined in the current schema. An RDT is unregistered if no entries in the SYSDATA table refer to it.

Parameters

None.

Usage Notes

This function returns an array of comma-delimited list of RDTs. The list contains the following information:

Schema name (only if you are connected as the MDSYS user)
RDT name

If you call this function while connected as the MDSYS user, all unregistered RDTs defined in the database are listed.

Examples

The following example lists the unregistered RDTs defined in the current schema.

SELECT * FROM THE (SELECT SDO_GEOR_ADMIN.listUnregisteredRDT FROM DUAL);
 
COLUMN_VALUE
---------------------------------------------------------------------------------
SDO_STRING2_ARRAY('RDT_REGULAR_02')

SDO_GEOR_ADMIN.maintainSysdataEntries

Format

SDO_GEOR_ADMIN.maintainSysdataEntries() RETURN SDO_STRING2_ARRAY;

Description

Checks the USER_SDO_GEOR_SYSDATA view for any invalid entries, and takes corrective action as appropriate.

Parameters

None.

Usage Notes

This function performs the same checks as the SDO_GEOR_ADMIN.checkSysdataEntries function, and it takes the corrective action that is appropriate (if any). For each of the following errors, the function does the following:

The RDT name is not unique. If you are connected as a user other then MDSYS, no action is taken; if you are connected as user MDSYS, duplicate RDTs are renamed so that their names are unique.
The GeoRaster table does not exist. The entry is deleted.
The GeoRaster column does not exist. The entry is deleted.
The GeoRaster objects does not exist. The entry is deleted.
The GeoRaster object is non-empty or nonblank, but the RDT does not exist. The entry is deleted.
Duplicate GeoRaster objects exist (that is, one or more non-unique combinations of RDT and raster ID). The entry is deleted.

If you call this function while connected as the MDSYS user, the entries in the ALL_SDO_GEOR_SYSDATA view instead of the USER_SDO_GEOR_METADATA view are checked.

The USER_SDO_GEOR_DATA and ALL_SDO_GEOR_SYSDATA views are described in Section 2.4.

Examples

The following example checks the USER_SDO_GEOR_SYSDATA view for invalid entries, and performs corrective action as appropriate.

DECLARE
  ret SDO_STRING2_ARRAY;
BEGIN
  ret:=sdo_geor_admin.MAINTAINSYSDATAENTRIES;
  for i in 1..ret.count loop
    dbms_output.put_line(ret(i));
  end loop;
END;
/
The RDT name GEOR_TEST1.RDT2 is renamed to GEOR_TEST1.RDT1!
The sysdata entry (SCHEMA=GEOR_TEST1 RDT=dt1 RID=1) is deleted!
 
PL/SQL procedure successfully completed.

SDO_GEOR_ADMIN.registerGeoRasterColumns

Format

SDO_GEOR_ADMIN.registerGeoRasterColumns;

Description

Creates DML triggers for all GeoRaster columns defined in the current schema.

Parameters

None.

Usage Notes

You should not normally need to execute this procedure. You should execute it only if some error or other condition has resulted in GeoRaster columns without associated DML triggers.

If you execute this procedure while connected as the MDSYS user, DML triggers are created for all GeoRaster columns defined in all schemas.

Examples

The following example creates DML triggers for all GeoRaster columns defined in the current schema.

EXECUTE sdo_geor_admin.registerGeoRasterColumns;

SDO_GEOR_ADMIN.registerGeoRasterObjects

Format

SDO_GEOR_ADMIN.registerGeoRasterObjects;

Description

Registers all GeoRaster objects defined in the current schema.

Parameters

None.

Usage Notes

If you execute this procedure while connected as the MDSYS user, all GeoRaster objects defined in all schemas are registered.

Examples

The following example registers all GeoRaster objects defined in the current schema.

EXECUTE sdo_geor_admin.registerGeoRasterObjects;

SDO_GEOR_ADMIN.upgradeGeoRaster

Format

SDO_GEOR_ADMIN.upgradeGeoRaster() RETURN SDO_STRING2_ARRAY;

Description

Checks the GeoRaster system data entries and GeoRaster data for the current schema, and performs any corrective action as appropriate.

Parameters

None.

Usage Notes

This function performs the same checks as the SDO_GEOR_ADMIN.isUpgradeNeeded function, and it takes the corrective action that is appropriate (if any) for the following errors:

System data entry error, the RDT name is not unique.
System data entry error, the RDT/RID pair is not unique.
System data entry error, the GeoRaster table does not exist.
System data entry error, the GeoRaster column does not exist.
System data entry error, the GeoRaster object does not exist.
The GeoRaster object is non-empty or nonblank, but the RDT does not exist.
Duplicate GeoRaster objects exist (that is, one or more non-unique combinations of RDT and raster ID).
There is a non-registered pair of (GeoRaster column, GeoRaster object).

If you call this function while connected as the MDSYS user, the GeoRaster system data entries and GeoRaster data for the entire database are checked, and any appropriate corrective actions are taken.

Examples

The following example checks the GeoRaster system data entries and GeoRaster data for the current schema, and performs any corrective action as appropriate.

SELECT SDO_GEOR_ADMIN.upgradeGeoRaster FROM DUAL;