Cheat sheet: Oracle DBMS

Abstract

This article contains informations about the Oracle DBMS in a compact format.

Problem & Solution

PL/SQL Script Drop Table If Exists

Regretfully a simple mechanism like DROP TABLE IF EXISTS known from PostgreSQL or MariaDB/MySQL still doesn’t exists in Oracle SQL. However you can use PL/SQL and Dynamic SQL to emulate its behaviour.

BEGIN
-- (...)
    SELECT count(*) INTO c from user_tables WHERE table_name = UPPER(val_table_name);
    IF c > 0 THEN
        EXECUTE IMMEDIATE 'DROP TABLE ' || val_table_name;
    END IF;
-- (...)
END;

PL/SQL Script to remove duplicate vertices in all registered spatial tables

Spatial tables may contain a lot of troubling geometries. In the GIS world you usually have to cleanup incorrect geometries before a spatial dbms can use them in an accurate and performant manner. However the spatial system of Oracle DBMS provides you a lot of tools to handle these corrupt geometries. A common problem are duplicate vertices in polygon rings. To solve these you can use the function:

SDO_UTIL.REMOVE_DUPLICATE_VERTICES

To remove duplicate vertices in all registered spatial tables you can use the following pl/sql script. But be warned that this can take a long time, depending on the size of the tables.

SET SERVEROUTPUT ON;
DECLARE
v_sql VARCHAR(2000);
c int;
val_table_name VARCHAR(2000);
BEGIN
DBMS_OUTPUT.ENABLE;
FOR spatial_table IN (SELECT * FROM USER_SDO_GEOM_METADATA)
LOOP    
    v_sql := 'UPDATE ' 
|| spatial_table.table_name 
||' c SET ' 
|| spatial_table.column_name 
|| '=SDO_UTIL.REMOVE_DUPLICATE_VERTICES(c.' 
|| spatial_table.column_name 
|| ', 0.005)';
    DBMS_OUTPUT.PUT_LINE( v_sql );
    EXECUTE IMMEDIATE v_sql;
    COMMIT;
END LOOP;
END;