CREATE_DB error due to disabled GeoRaster support in Oracle Spatial 12.1.0.2

[clausnagel]

Since Oracle 12c Release 1 (12.1.0.2) the GeoRaster feature is disabled after the Oracle Spatial and Graph is initially installed (see release changes).

When running the CREATE_DB.sql script with the Spatial option, creating the table GRID_COVERAGE consequently fails because the SDO_GEORASTER data type is not available (see lines 790ff in the TABLES.sql file). The screenshot below illustrates the error message.

To enable GeoRaster support on Oracle Spatial and Graph 12.1.0.2, two steps are required:

1. Connect to the database as SYS AS SYSDBA
2. Enter the following statement:
   EXECUTE MDSYS.enableGeoRaster;

After this, the GRID_COVERAGE table can be created and the CREATE_DB.sql script runs without error message.

The CREATE_DB.sql script should therefore be changed to automatically check whether GeoRaster support is enabled on Oracle Spatial and Graph 12.1.0.2. The raster relief tables may only be created if GeoRaster support is enabled. A warning message could be displayed to the user if GeoRaster is disabled.

[clausnagel]

Fixed in e6bdfe3 and 2e35383

[FxKu]

I still got compilation errors under Oracle 11g. I had to create a variable within the anynomous code blocks:
:GEORASTER_SUPPORT := :GEORASTER_SUPPORT;

The special treatment for GEORASTER is also missing in other files, like DROP_DB. Everything is already fixed in the dev branch by c0eb7ec.

Will close issue as soon as code is merged.

[clausnagel]

Just ran the setup on Oracle 11g without compilation errors (using latest master). What errors do you encounter? And GEORASTER_SUPPORT is considered in the DROP_DB script. Please provide more information.

[FxKu]

When dropping the schema the following messages appear in the log:

"SP2-0552: Bind-Variable "GEORASTER_SUPPORT" ist nicht deklariert"

I've included a declaration of GEORASTER_SUPPORT in DROP_DB.sql in order to use &GEORASTER_SUPPORT in DROP_DB2.sql. Then I do not need to define the variable in all the code blocks.

When creating the DB I spot several errors in the logs, but for some reasons it says "finished without errors" in the end. I fixed it by adding a declaration in most code blocks.

Fehler beim Start in Zeile : 777 Datei @ C:\Users\fkunde\workspace\citydb\Oracle\SQLScripts\SCHEMA\TABLES\TABLES.sql
In Befehl -
BEGIN
  IF (upper('&DBVERSION')='S' and :GEORASTER_SUPPORT <> 0) THEN
    EXECUTE IMMEDIATE 'CREATE TABLE RASTER_RELIEF 
	(
	  ID NUMBER NOT NULL 
	, URI VARCHAR2(4000) 
	, COVERAGE_ID NUMBER 
	, CONSTRAINT RASTER_RELIEF_PK PRIMARY KEY 
	  (
	    ID 
	  )
	  ENABLE 
	)';  
    EXECUTE IMMEDIATE 'CREATE TABLE GRID_COVERAGE
	(
	  ID NUMBER NOT NULL 
	, RASTERPROPERTY MDSYS.SDO_GEORASTER NOT NULL 
	, CONSTRAINT GRID_COVERAGE_PK PRIMARY KEY 
	  (
	    ID 
	  )
	  ENABLE 
	)';
	EXECUTE IMMEDIATE 'CREATE TABLE GRID_COVERAGE_RDT 
	(
	  RASTERID NUMBER 
	, PYRAMIDLEVEL NUMBER 
	, BANDBLOCKNUMBER NUMBER 
	, ROWBLOCKNUMBER NUMBER 
	, COLUMNBLOCKNUMBER NUMBER 
	, BLOCKMBR MDSYS.SDO_GEOMETRY 
	, RASTERBLOCK BLOB 
	, CONSTRAINT GRID_COVERAGE_RDT_PK PRIMARY KEY 
	  (
		RASTERID 
	  , PYRAMIDLEVEL 
	  , BANDBLOCKNUMBER 
	  , ROWBLOCKNUMBER 
	  , COLUMNBLOCKNUMBER 
	  )
	  ENABLE 
	) 
	LOB (RASTERBLOCK) STORE AS SECUREFILE 
	( 
	  ENABLE STORAGE IN ROW 
	  CACHE  
	)';

  END IF;
END;
Fehlerbericht -
Fehlende Defines

Fehler beim Start in Zeile : 42 Datei @ C:\Users\fkunde\workspace\citydb\Oracle\SQLScripts\SCHEMA\SEQUENCES\SEQUENCES.sql
In Befehl -
BEGIN
  IF (upper('&DBVERSION')='S' and :GEORASTER_SUPPORT <> 0) THEN
    EXECUTE IMMEDIATE 'CREATE SEQUENCE GRID_COVERAGE_SEQ INCREMENT BY 1 START WITH 1 MINVALUE 1 NOCACHE';  
    EXECUTE IMMEDIATE 'CREATE SEQUENCE GRID_COVERAGE_RDT_SEQ INCREMENT BY 1 START WITH 1 MINVALUE 1 NOCACHE';
  END IF;
END;
Fehlerbericht -
Fehlende Defines

Fehler beim Start in Zeile : 1.733 Datei @ C:\Users\fkunde\workspace\citydb\Oracle\SQLScripts\SCHEMA\CONSTRAINTS\CONSTRAINTS.sql
In Befehl -
BEGIN
  IF (upper('&DBVERSION')='S' and :GEORASTER_SUPPORT <> 0) THEN
    EXECUTE IMMEDIATE 'ALTER TABLE RASTER_RELIEF
	ADD CONSTRAINT RASTER_RELIEF_COMP_FK FOREIGN KEY
	(
	  ID
	)
	REFERENCES RELIEF_COMPONENT
	(
	  ID
	)
	ENABLE';  

	EXECUTE IMMEDIATE 'ALTER TABLE RASTER_RELIEF
	ADD CONSTRAINT RASTER_RELIEF_COVERAGE_FK FOREIGN KEY
	(
	  COVERAGE_ID 
	)
	REFERENCES GRID_COVERAGE
	(
	  ID 
	)
	ENABLE';
  END IF;
END;
Fehlerbericht -
Fehlende Defines

Fehler beim Start in Zeile : 314 Datei @ C:\Users\fkunde\workspace\citydb\Oracle\SQLScripts\SCHEMA\INDEXES\SIMPLE_INDEX.sql
In Befehl -
BEGIN
  IF (upper('&DBVERSION')='S' and :GEORASTER_SUPPORT <> 0) THEN
    EXECUTE IMMEDIATE 'CREATE INDEX RASTER_RELIEF_COVERAGE_FKX ON RASTER_RELIEF (COVERAGE_ID)';  
  END IF;
END;
Fehlerbericht -
Fehlende Defines

plus at the end I see comething, I haven't noticed before:

MC2                                                                            
--------------------------------------------------------------------------------
                                                                                

 1 Zeilen gewählt

SP2-0556: Ungültiger Dateiname.

[clausnagel]

Strange, no errors on my side

[FxKu]

Should be fixed now :)